Concatenate, Address Vlookup

[d9h]

I am trying to concatenate lots (100's of rows) of information together and will need to select the rows via a vlookup.


I will need to do lots of these conatenates and each one will have a different starting and ending cell.


I am trying to enter the cell address in concatenate function with 'Address' and 'vlookup'


'=concatenate((ADDRESS((VLOOKUP(D3,A:B,1)),2))ADDRESS((VLOOKUP(E3,A:B,1)),2)))


in short help!.


Many thanks.

 

[Hui]

d9h


Concatenate doesn't work with ranges.


I would have a read of the following post

It contains a UDF which will assist you


http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

 

[oldchippy]

Hi d9h,


you say you have 100's of rows but not how many columns? Could you use a helper column to concatenate the cells like


=TRIM(A1)&TRIM(B1)&TRIM(C1)&TRIM(D1)&TRIM(E1) etc then use that in the VLOOKUP

 

[d9h]

Hi Hui,

Many thanks for the UDF 'concat"

it does really work very well.

however is it possible to put in a referance from another cell into the range so that the concat range is selected from a set of vairable values.


sorry to be a pain.


thanks


D9H

 

[d9h]

Hi old chippy,

thanks for the trim function, I am currently working with it.


but to answer your question, I have only on coloum of data but its rather long......


thanks for your help


D9H

 

[Hui]

D9H

I would use a formula like this

=Concat(INDIRECT(C2))

where C2 has a Text Range ie: A10:A102

you can use a series of equations to make your range

eg: =Concatenate("A",C20,":A",C21)

where C20 would contain 10 and C21 would contain 102

 

[d9h]

Hui,

excellent Many thanks.

trully a massive time saver.

It works really welll thanks


the only issue i have now is that the concat is over flowing the ammount of data that can be put into the cell, but thats okay as it flags that something else is wrong in the data.... but thats not my problem...


Thanks again