I have multiple rows that I wanted to concatenate. I want the values delimited to a symbol like +. The problem using the sample formula =CONCATENATE(A3," = ",B3,"+",C3,"+",D3) is if there's a blank row the + sign will still display. I attached sample file with the desired result.
View 5 RepliesI 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
Abdi,Salahadin
600607, 600670, 600878, 600879, 710432, 710432, 710435
I have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:
TABLE 1:
Project
Benefit Type
Delivered or Enabled
Benefit
PJ1
Financial
Delivered
Saving of $4M over 24 months.
[code]....
I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.
TABLE 2:
Financial - Delivered
Financial - Enabled
Tech - Delivered
Tech - Enabled
Green - Delivered
Green - Enabled
[code]....
So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:
Continued maintenance of hardware.
Increased capacity.
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.
View 4 Replies View RelatedCan I concatenate two different rows in a column?
View 10 Replies View RelatedThe formula below works great for counting. I need to use the same formula to CONCATENATE all of the names in B:B. It seems as though I should just replace COUNTIFS with CONCATENATE and change Data!C:C to Data!B:B, but it doesn't work. I tried a search on the web using the title of this thread but came up with very few clues. Is it even possible to CONCATENATE with multiples IFS?
=COUNTIFS(Data!C:C,IF(B2="All","*",B2),Data!D:D,IF(B3="All","="&W5,Data!E:E,"
Unfortunately my strengths do not lie in VBA for Excel. But I have a problem which needs resolving using a macro because this spreadsheet scenario happens often.
I have three columns (Record ID, Comment, Results). The Results column is added to capture concatenation of the Comments column. Using VBA, I want to concatenate Comments for each Record ID in the Results column on the row for that Record Id where the rows above have no Record Id.
Each comment should stack in order with a carriage return between them. This should happen for each row where a Record ID exists and continue for the length of the spreadsheet until it finds the last row with a Record ID and should stop after concatenating its results.
I was trying to create this certain tool using excel which queries data from database. I was successful in obtaining the data I need and paste it to my spreadsheet, however, there are some requirements I cannot create a solution with. Below is a description of my Excel file (which is also attached):
- There are 9 columns on my spreadsheet
- Number of records per data refresh are indefinite, for information in database are constantly being updated
- Several cells on the first column have exactly the same information
The requirements I cannot do are:
- For all cells on first column that have similar values, their corresponding data on column 5 should be concatenated
- Delete rows that have same data for column 1 except the first record found, place concatenated data on column 5 of first record
Example:..................
I have data in sheet 1 and sheet 2. Sheet 1 contains a product code and line number and sheet 2 has the corresponding cost.
I want to be able to bring back the cost to sheet 1 without having to concatenate the product and line number.
I'm sure this is possible with a formula, but I am currently stumped.
I pasted the sheet 1 and sheet 2 data below. Note that the sheet 2 data extends the full row and does not wrap.....
I have a macro to concatenate multiple cells but unfortunately it is not giving the result,
It has to concatenate below mentioned cells and put the result in column N
Sub ConcatColumns()
Do While ActiveCell "" 'Loops until the active cell is blank.
'The "&" must have a space on both sides or it will be
'treated as a variable type of long integer.
Range("N1").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[-10],RC[-9],RC[-8],RC[-7],RC[-6],RC[-5],RC[-1])& ActiveCell.Offset(0, 0)"
ActiveCell.Offset(1, 0).Select
Loop
End Sub
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.
In cell R4, I have the following formula:
=IF(S4="","",IF(X4="",CONCATENATE("U",V4,"C0",W4,$R$1),CONCATENATE("U",V4,"C0",W4,"L0",X4,$R$1)))
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 have a spreadsheet with three columns (check #, invoice #, vendor #). If a check pays multiple invoices, there is a separate row for each instance of invoice #. (ie if a check pays 10 invoices, there are 10 rows all with the same value for check # and vendor #, but different invoice #s). I need to concatenate the multiple rows to just one row per check #, with all the invoice #s in separate columns on the same row and delete the duplicate check # rows.
However, I can only use 10 columns, so if there are more than 8 invoices matching the same check #, the 10th column will need to contain invoices 9-n concatenated and separated by a space. I have over 5000 rows in the spreadsheet.
I've searched quite a few times for Concatenating Multiple Columns and ones for Concatenating with Spaces.
I've found solutions that say use
=concatenate(A1, " ", B1)
or the VBA equivalent
I have at least 15-20 columns which I wish to concatenate.
Is there an easier way than to type for all 15 columns?
=concatenate(A1," ", B1," ", C1,....)
Also, the number of columns of each row vary
Im tryng to do a SUMIF formula that has several criteria as answer but using the concatenate formula, but it doesnt seem to come back with the answer - im using AND/OR formula aswell, but have a feeling im doing it wrong.
It does work when i use the single concatenate, but not when i try a do more than one
Say in L4 on form test i want to look for the value of A4 in Drill data range a2 to z1000 and retun a concatenated string of something like "sav 407 : 08-may to 11-may"
View 3 Replies View RelatedI've made a "Budget Calendar" so to speak.., with my bills on the first sheet, and the Months in the following sheets. What I'm trying to do is say, for instance, the day is the 11th on the calendar, (there are two bills due on this day), and I have the bill scheduled to be on the 11th, can I use a formula to return what bills are due?
On the "Bill Dates" sheet, you can see where I'm kind of going with this...
On the August calendar, under the 11th, I want it to say "Internet, gas (utility)" in cell D20, or "Internet" and "gas (utility)" in D20&21, respectively.
I can get E20 to return the total bills due for the day using "=VLOOKUP("11th",'Bill Dates'!$B$2:$D$395,3,FALSE)", and it'd be awesome if I could get it to return them in different rows as well.
Finance Calendar.xlsx
I have 2 worksheets, Worksheet 1 has Customer Magic Number on it as a reference and a few customer details and Worksheet 2 has Customer magic number and contact fields.
I am currently using the formula
=INDEX($C$3:$C$575, SMALL(IF(N2=$D$3:$D$575, ROW($D$3:$D$575)-MIN(ROW($D$3:$D$575))+1, ""), COLUMN(A1)))
to show the contact codes in sheet 1 however I also need to show the Notes which are located in Columns G:I, Is there an easy to use the index & match functions as above with the concatenate function to add the notes in the cell beside where I am inputting the contact codes?
I am trying to concatenate multiple strings of text where each may have composite or compound formatting. Is there any way to maintain the formatting? The Characters.Insert method won't work because the resulting string is greater than 255 characters. See an example below.
String 1: When you simply concatenate
String 2: these strings, Excel discards
String 3: formatting at the character level.
Required Result: When you simply concatenate these strings, Excel discards formatting at the character level.
I think this is an easy one, but i can't figure it out. I have a spreadsheeet with multiple concatenate functions programed in. All of them work. If, however, i try to change the formula inside the concatenate function it freezes and will not calculate the new cell. I have the calculation tab set to automatic in the options tab...
View 9 Replies View RelatedIf I have the following data set in cells A1:C9:
IDCommentFirst Unique
109876Low OilTRUE
109876Checked On 12/12FALSE
109877Checked on 12/15TRUE
109878Correct LevelTRUE
109877Correct LevelFALSE
109878PerfectFALSE
109877NewFALSE
109878Correct LevelFALSE
In the First Unique column (column C) I have formulas. In C2 I have this formula (and then I copied it down):
=COUNTIF(A$2:A2,A2)=1
In cell E2, I have this formula to count unique occurrences:
=SUMPRODUCT(C2:C9*1)
In cell G2 I have this formula to extract unique records (entered with Ctrl + Shift + Enter):
=IF(ROWS(G$2:G2)
I am trying to find 3 values (X,Y,Z) in row A and concatenate those three values in column A for the entire range below, say A2:A100.
View 9 Replies View RelatedI have a text document disaggregated into sentences each in different cells. I need a way of selecting different cells with a mouse, and through a Macro, have the text from those cells viewed in concatenated form into new textbox generated by the Macro.
View 9 Replies View RelatedI have several lists of hundreds of emails that I need to list in an email string via Lotus Notes. I need a space and comma in between each name, ex:
john.doe@dm.com, john.doe1@dm.com, john.doe2@dm.com
I am using the below formula manually but it is taking too long. How to automate this? Is there a way to create the space and comma for as long as the list is?
=CONCATENATE(B6,", ",B7,", ",B8,", ",B9,", ",B10,", ",B12,", ",B13,", ",B14,", ",B15,", ",B16,", ",B17)
What is the best way to concatenate with a semi-colon across multiple cells in excel. The one kicker is I only want to concatenate where the cell is populated.
If I write a simple Concatenate or combined columns, if a cell is blank I get the multiple.
I wish to Concatenate contents of several columns into a separate column, i tried using the following "column1 & column2 & column3 etc..." however with this the blank cells get concatenated too...What should is use so that contents get concatenated but blank cells are ignored?
View 4 Replies View RelatedI have a list of P/N's that are used in more then one location. and it's sorted by P/N's.
ColA__ColB__ColC
______Loc___PN
______1_____A
______2_____A
______3_____B
______4_____C
______5_____C
I Want to be able to put in Col A the concatenate results of all equal P/N's from any given list. Or at least select the few cells that i know are duplicates and from that copy the Location to a single Column.
ColA ColB__ColC
______Loc__PN
1,2____1___A
_______2___A
_______3___B
4,5____4___C
_______5___C
I have one row per day of the month in column A. each row has one or more cell entries in column B .... to column (variable) Right now I use a formula =concatenate(B2,CHAR(10),C2,CHAR(10),D2.....) for as long as it has to be, but this has its limits and requires a lot of editing as the number of filled cells for each row vary.
would it be possible with some macro code to manage the variable number of cells and then insert verything, wrapped text, in a single cell (can be cell Bx or a cell in a new column added next to the dates column) As soon as everything is in the single cell, the original data cells can be emtied. I can record a macro but that does not allow me to manage the variable number of cells . a small sample file attached.
Sampling table :
one
two
three
four
one
two
three
one
two
one
Desired results obtained via IF =IF(B2>0,A2&" , ",A2)&IF(C2>0,B2&" , ",B2)&IF(D2>0,C2&" , ",C2)&IF(D2>0,D2,"")
one , two , three , four
one , two , three
one , two
one
Is there any smarter, shorter formula via Concatenate and Substitute or other formulas ?
My closest match, but not good enaugh is =SUBSTITUTE(CONCATENATE(A2&", "&B2&", "&C2&", "&D2), ", , ", " ")
[ returna 2 commad ]
one, two, three, four
one, two, three,
one, two
one ,
I have created a spreadsheet to show some reports and I wanted to serch for some datas which overloops themeselves. If you can have a look at a test file I attached you will see the full picture. I have 2 tables, where the 2nd one is on the right side of the 1st one. 1st table:..............
View 3 Replies View RelatedThe 3 vlookup will be in a single cell for concatenation like :
=vlookup1&"-"&vlookup2&"-"&vlookup3
Problem the are 3 vlookups where it will return names (with format -> Fname, Given Name Middle) and probably blank returns.
I have problem with the replace since there are also spaces between the Fname,Gname and Mname sample name with spaces.