# Formula to Concatenate data not on same row

1. ## Formula to Concatenate data not on same row

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.

Thank you.  Register To Reply

2. ## Re: Formula to Concatenate data not on same row

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

3. ## Re: Formula to Concatenate data not on same row

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  Register To Reply

4. ## Re: Formula to Concatenate data not on same row

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?  Register To Reply

5. ## Re: Formula to Concatenate data not on same row

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.  Register To Reply

6. ## Re: Formula to Concatenate data not on same row 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.  Register To Reply

7. ## Re: Formula to Concatenate data not on same row

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

8. ## Re: Formula to Concatenate data not on same row 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

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

9. ## Re: Formula to Concatenate data not on same row

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.  Register To Reply

10. ## Re: Formula to Concatenate data not on same row

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)  Register To Reply

11. ## Re: Formula to Concatenate data not on same row

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" ?  Register To Reply

12. ## Re: Formula to Concatenate data not on same row 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" ?  Register To Reply

13. ## Re: Formula to Concatenate data not on same row

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.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 