Complicated VLOOKUP and joining cells

[singhmrp]

Hello,

I have a very long set of data, like this

NAME1

sometext
sometext2
sometext3

NAME2

sometext4
sometext5

There are empty rows exactly as above. What i want to do is vlookup on NAME1, NAME2 (which I can do) but then I want to return something like: sometext, sometext2, sometext3 for NAME1 and sometext4, sometext5 for NAME2.

The list I have has about 100 NAMES each with a different number of 'sometext' - painful to do this manually. Any help will be appreciated.

Regards.

[cbatrody]

Hi,

Can you post a sample file in excel format? Please go through the below URL for any help needed in uploading a file.

http://www.excelforum.com/members/da...ch-a-file.html

[singhmrp]

ComplicatedLookupandJoin - Copy.xlsx

Hi,

Attached a sample file, with what i have and want i'd want.

Thanks.

[Rvsalka]

I don't think vlookup is what you need. I think what you are looking for Paste Special Transpose. Heres a quick video showing the functionality,
https://www.youtube.com/watch?v=eeIoDueIMvg

[singhmrp]

Hi,

But that would still require manual effort to select, and transpose.

[newdoverman]

Insert a row at the top of your data.
In B2 enter this formula and fill down to copy the names into column B:

Please Login or Register  to view this content.

In D2 enter this formula and fill down to eliminate the name from the column of data:

Please Login or Register  to view this content.

In I2 enter this ARRAY FORMULA and fill across and down.

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.