Formula to Concatenate data not on same row

**rubenidas** · 06-08-2015, 07:32 AM

Hi Excel Forum,
i would like to ask you guys for your help in building a formula to concatenate data that is in 3 different columns and on different rows as well. The way it works is like this, look down column A and if you see something, then look down column B, and if you find something as well, look down column C, and when you find something then concatenate all 3 columns. i dont mind if the formula reads backwards either, for instance, look at column C first and then B then A (searching upwards though, Column A will determine the "break point"). I have attached an example worksheet for reference.

Please help as it would be greatly appreciated. If this can only be done thru macro please let me know as i will address it to the VBA section instead.

Thank you.

**jayajaya_4** · 06-08-2015, 07:49 AM

=concatenate(c6,b4,a2)
=concatenate(c14,b11,a9)
=concatenate(c15,b11,a9)

**Special-K** · 06-08-2015, 08:04 AM

LOL, I dont think the OP is gonna know what cells the values are in hence the "look down column A" "column B" "column C" etc

**Special-K** · 06-08-2015, 08:07 AM

What decides when to print that data?
It looks like if the cell in column C is not blank print the previous values of column A and B, is that correct?

**rubenidas** · 06-08-2015, 08:08 AM

i need the formula to start at the top then be able to drag it down and make it look for the info. Not me have to name each specific cell out. The list will run down like this for about 200 lines.

**rubenidas** · 06-08-2015, 08:09 AM

Originally Posted by Special-K

What decides when to print that data?
It looks like if the cell in column C is not blank print the previous values of column A and B, is that correct?

yes! that is correct.

**Special-K** · 06-08-2015, 08:10 AM

Try this
in C2
=IF(C2="","",C2&LOOKUP(9.99999999999999E+307,B$1:B2)&LOOKUP(9.99999999999999E+307,A$1:A2))
and copy down

Source: http://www.pcreview.co.uk/threads/fi...olumn.1759433/

**rubenidas** · 06-08-2015, 08:13 AM

Originally Posted by Special-K

Try this
in C2
=IF(C2="","",C2&LOOKUP(9.99999999999999E+307,B$1:B2)&LOOKUP(9.999999999999999E+307,A$1:A2))
and copy down

Source: http://www.pcreview.co.uk/threads/fi...olumn.1759433/

OMG thank you! i think this works perfect! Can you explain what the 9.9999999E+307 part does??

**Special-K** · 06-08-2015, 08:25 AM

I think it finds the last value in the column, but as we are limiting the search from A1 up to the row that we are on it will just find the last value above, ie the previous value depending what row we are on.

**cbatrody** · 06-08-2015, 08:38 AM

An alternate method,

Try the following array formula in D2,

={IF(C2="","",INDEX(C$1:C2,MAX((C$1:C2<>"")*(ROW(C$1:C2))))&INDEX(B$1:B2,MAX((B$1:B2<>"")*(ROW(B$1:B2))))&INDEX(A$1:A2,MAX((A$1:A2<>"")*(ROW(A$1:A2)))))}

Please note that the formula needs to be confirmed by pressing CTRL+ALT+ENTER (not only ENTER)

**rubenidas** · 06-08-2015, 09:17 AM

Ok, one more problem here. I think the report i run puts column A and B into a text format because the data on those two columns comes shifted to the left of the cell since i am pulling the data using the MID formula (i didnt include that because it was company info). Is there a way to not get the N/A or do i have to copy paste and convert each cell to "convert to number" ?

**rubenidas** · 06-08-2015, 09:49 AM

Originally Posted by Special-K

I think it finds the last value in the column, but as we are limiting the search from A1 up to the row that we are on it will just find the last value above, ie the previous value depending what row we are on.

Ok, one more problem here. I think the report i run puts column A and B into a text format because the data on those two columns comes shifted to the left of the cell since i am pulling the data using the MID formula (i didnt include that because it was company info). Is there a way to not get the N/A or do i have to copy paste and convert each cell to "convert to number" ?

**cbatrody** · 06-09-2015, 01:20 AM

You can prefix an IFERROR condition to get rid of N/As & multiply the MID formula by 1 (1*MID(______)) to auto convert the result into numbers.

Formula to Concatenate data not on same row

LinkBack

Thread Tools

Rate This Thread

Display

Formula to Concatenate data not on same row

Re: Formula to Concatenate data not on same row

Re: Formula to Concatenate data not on same row

Re: Formula to Concatenate data not on same row

Re: Formula to Concatenate data not on same row

Re: Formula to Concatenate data not on same row

Re: Formula to Concatenate data not on same row

Re: Formula to Concatenate data not on same row

Re: Formula to Concatenate data not on same row

Re: Formula to Concatenate data not on same row

Re: Formula to Concatenate data not on same row

Re: Formula to Concatenate data not on same row

Re: Formula to Concatenate data not on same row

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula doesn't work when indirectly referencing a range of cells from concatenate formula

using concatenate formula ( will detemine teh data depending on the preceding column)

[SOLVED] Formula concatenate data in multiple rows based on match data in a different row.

formula to concatenate cells with data

Keeping the Data, losing the formula (concatenate)

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions