I am trying to get a complete list of applications per device. I have the device list but it is broken down into seperate lines so i would like some sort of formula (not fussed what type as long as it gets the desired results!) that will look in column A and if the text is the same, to concatenate the info in column b, separated by a comma. So for the first device (4d713006) i would get 1 cell that would have the list of all 52 applications in it separated by a formula. This is so i can vlookup the device name from another sheet and pull through all the list of applications.
I need a formula to be placed in cell Col A, Row 1, that concatenates any and all cells in Col A, with a ';' separating each item. I only want this concatenation performed ONLY IF an adjacent cell in Col B contains the letter 'X.
Additionally, if the cell in Col A is null, then I the formula to ignore it, and not perform the concatenation.
What can I add to this existing formula to look for duplicate values in the entire column (D) and then concatenate the text of column (K) in each of those rows together?
Essentially I want to string all text in column K together, for each duplicate row of column D with the results in column L.
I am attempting to concatenate the dept numbers for each user in a list. For example, the first user listed below is associated with 6 depts. I would like the resulting macro to concatenate the dept's into one cell next to the Name.
Below is an example with the solution I am attempting to produce.
Sample Table
Name Dept
Abbruzzese,James L 188100
[Code] ......
Output
Name Dept
Abbruzzese,James L 188100, 231100, 600377, 600656, 600663, 600708
I need to concatenate varying numbers of cells based on duplicates found in a separate column, but I'm not sure how to approach it. I have 41,000+ rows of data, so I have to find a formula.
I am looking for a way of creating the following conditioned concatenation.
I have two tables, let's call them "summary" and "detailed".
The "detailed" table is something like the following:
ID VOL
001 01
001 05
[code]....
The "summary" table below gets info from the "detailed" table. The 'ID'is now unique. I'm looking for a formula on the 'VOL (concatenated)' column cells it should get all rows from the "detailed" table with the same ID and then concatenate the 'VOL' column results, comma separated:
ID (unique) VOL (concatenated)
001 V01, V03, V05
002 V01, V04
003 V06
PS: I have people using this table with office 2003, so compatibility is necessary...
I am having a trouble in Excel sheet.My column A has a drop down list with text- possible, not possible, not required.Based on the text, i need to populate texts in columns B, C and D.
For example
Column A drop down selected is "possible" then B coulmn should automatically populate "1-3" C should populate with "3-5" D should be "5-7"
I am in need of restructuring a spreadsheet of addresses for mailing purposes. (I tried to find something similar answered previously, but nothing seemed to work for me.)
I have attached a small example spreadsheet below, but our spreadsheets can be hundreds or thousands of rows in length.
I need a macro that will look for the "PO Box" addresses under the column headers named "ADDRESS2" and "ADDRESS3".
The PO Box addresses will need to be moved under the column header "ADDRESS1" within the same row.
It will need to overwrite the text that is already under "ADDRESS1" and delete the text from the "ADDRESS2" and "ADDRESS3" columns - UNLESS the text in 'ADDRESS2" is a PO Box AND "ADDRESS1" begins with "c/o".
If the data in "ADDRESS2" or "ADDRESS3" is anything other than a PO Box it will remain the same. As will "ADDRESS 1".
Basically if there is a PO Box it needs to be in the column named "ADDRESS1" and overwrite anything else that was there. The exception will be for PO Boxes that are in c/o someone else, the PO Box will then need to be listed in the column directly after the column that has c/o.
If the c/o exception will be too difficult the code could just highlight those scenarios and we could fix them manually. We usually do not have a lot of them, but enough that we need to be mindful of them.
The different scenarios are listed in my sample spreadsheet.Also, the code will have to use the column header names in row 1 because those headers are not always in the same column.
My issue is that I have 1200+ addresses to make more readable - the first column is the street name, the second is the post code(s) relative to the street - what Ideally I'd like to achieve is one row per street, the street name followed by the post codes... i.e. take this...
Writing a UDF for VBA as I'm getting frustrated by the concatenate function.
I basically want to create my own function that will concatenate values in column B, if the value in column A is correct.
I've been looking at this link [URL] ......
With a very simple data set as an example, I want the following output in column C:
pet food conc
cat chicken chicken,fish,catnip
[Code] ........
I think I need to create a cell based function which takes a conditional range (col A) and concatenate range (col B) as inputs, stores this as a 5 by 3 array and then returns the concatenated output I wish (by referencing the stored array) based on what value is in column A. A "concatenate if", if you like.
I had a problem finding something then deleting the column, I tried to alter the code from that thread to solve a new problem and I am close (I think), but not quite there. I need to find the column "Group" and merge it with the column "Sex" which is offset by 1 column. This code will go through and find the column "Group" and merge for the first cell, but will then get stuck in a loop just concatenating "Sex" to the end continuously. I need it to concatenate both columns entirely together. Any simple fix to what I modified? Previous thread should you be interested:Find Part Text On All Sheets & Delete The Column.
In Excel 2007 I want to concatenate two columns of text. In Column A all the cells contain a single statement that I want to prefix the statements in the cells of column B (the statements in column B differ from cell to cell) I have used the formula =A1&" "&B1 and this is fine for that row, when I use the fill handle and pull it down the page the formula changes accordingly i.e.=A2&" "&B2, =A3&" "&B3 etc. But when I make the text appear using control+ I only get the concatenation of the first row repeated all the way down, irrespective of the contents of other cells in Column B.
I am concatenating a date with some text. However, after concatenating, the date becomes a text string. How do I preserve the date format? Pls see the attached file.
I cant quite get this, I have 4 cells in one row (a selection and can be any row), I need to string the values together and add some simple text.
Example:
Assume row 1 and columns A, B, C, D.
496 | 6d7 | Minor | Ø.375 +/-.010
What I need this to read as one long string:
#496, 6d7, Minor, Ø.375 +/-.010
Adding the text in Red.
Column A may be a single digit or up to 10 digits in length. Once the string is configured I want to copy it and manually paste in another application. If the string is placed any where else in the worksheet for formatting, it can be deleted after the copy/paste or macro end.
Using Excel 2007. Can I use IF along with CONCATENATE. =if(h26<.0099(concatenate(ak26,AK27))if(h26>.010(concatenate(ak26,aQ26,ak27)) Can't seem to get the proper syntax.
I have attached an table and need to combine name from different rows which a condition that the ID is the same and the name are different from others. However, the code that i have will combine all the name if the ID is match. I'm not familiar with the Macro.
I have a formula, and it works okay, but I have to change it based on how many characters appear in a cell. I would like to avoid this, as there is just too much room for error.
The end result of my formula is to get a result like the following: U1C01EP or U1C01L04EP depending on which columns are completed. But the "variation" on the number of characters in the cell (1 or 2) and accounting for the leading zero is what is getting me confused.
Column V always contains only 1 character, so this is not an issue. "U" will be added before the contents of this column in the formula Column W will contain either 1 or 2 characters. If there is only 1 character, the concatenate needs to add a leading 0 following the "C" and then the contents of column W.
Column X will contain either 1 or 2 characters. If there is only 1 character, the concatenate needs to add a leading 0 following the "L" and then the contents of column X. Now this column may also be blank, if that happens, then there should be no "L" within the result, that portion would be left off.
Cell R1 contains the code that is added to the very end of my result. This code will change, so it is placed in a fixed location to be added to the end.
And I would like to keep the IF statement that I wrote accounting for Column S being Blank, yielding a blank cell completely as a result.
I quickly put together an inventory form on our company server where employees can essentially just enter part numbers, quantities, and a few other things, rather than write everything out. So what might happen is, an employee puts together a list of 10 items with their respective quantities, but a few days later, another employee pulls up the spreadsheet and needs to add a few more of a part already listed. Protocol for the company is to do a separate line, rather than add it to the line where the item is already listed. What I am trying to do is, create a separate sheet within that workbook, that simply lists the part numbers, in order, with their total quantity. I have attached a basic spreadsheet showing what I would want, with a tab at the bottom labeled, "What I want."
I'm trying to create a SUMIF-style formula which is able to test for more than one condition. i.e if the values in column C meet a certain criteria and if the values in column E satisfy another criteria then add all the corresponding values in column D. In my particular spreadsheet, column C contains a date value. So, for all the dates in column C which are later than 01/01/2008 and for all the cells in column E which contain the word "oustanding", I need to add up all the corresponding values in column D.
I have a time in cell C10 in hh:mm format, in cell D10 I want to add the text "DFL" in front of the time, remove the ":" and add "HRS." at the end of it. For example "DFL0715HRS." I have been able to get the following formula to work:
=CONCATENATE("DFL",(TEXT(C10,"hhmm")),"HRS.")
However sometimes it may not be a time, it could also be "Rest Day" in cell C10, if it says "Rest Day" I need it to add the text "DFL" in front as before and then change "Rest Day" to "RDFLEXI." For example "DFLRDFLEXI." I have been able to get the following formula to work but only if it is "Rest Day", if it is a time such as 07:15 it doesn't work.
It shows "DFL07157R0.": =CONCATENATE("DFL",IF(C10="Rest Day","RDFLEXI.",(TEXT(C10,"hhmm"&"HRS."))))
I think it is trying to continue doing something with the text function
I am trying to figure out how to Concatenate a group of cells if they contain a certain text. Basically I have a list of Architects in column A and the account name in column B... and I want to say Concatenate column A, IF column B=Specific Account (ex: Coca-Cola). The list will be added to frequently so the formula must refer to the entire columns, not just a certain range.
As of right now, only the first architect associated with each account will appear. I have attached a workbook with the example..
I would like to concatenate text comments from 14 worksheets and place a soft return between each next set of text. To further complicate, not all of the worksheets will have comments.
Is there a way to make the CONCATENATE formula put all the text on different lines? It keeps combining some on the same lines and I want them all on separate - like a list.
I am trying to concatinate a cell to show both text and date (in format mmmm dd yyyy) using a vlookup from another worksheet.
Below is the coding I have in the cell: ="High Importance Request - effective " & VLOOKUP(1,'Fuel Surcharge Key Dates'!$A$6:$E$65536,2,FALSE)
The result I get in the cell from the formula is: I still get it to come out to 39327 instead of June 4 2007
The vlookup in the formula above retrieves a date that has a cell custom formatted to mmmm d, yyyy ... as this cell is not a date that is entered by a user but is further retrieved by another vlookup of dates. Even if I place the VLOOKUP in cell A3, reformat that cell to be a date and change the coding in the formula to: ="High Importance Request - effective " & A3. I still get it to come out to 39327 instead of June 4 2007
I'm working with a datafeed and basically I have a column with the prices of each product in the same row. What I need to do is take the value in the price column and insert it in a specific spot of a different cell (but still on the row).
A1 = <b></b> B1 = 29.99
How would I get that price information between those two bold tags, and do this for all the rows I have that contain specific price info to that row?
I have a lot of HTML in the line I want to bring the price over to and I have tried the following formula at the beginning cell of A1 ="<b>"&B1&"</b>" but I get an error.
