matching and ordering data from multiple worksheets

[hees]

I have a question regarding matching data from 1 worksheet to another.
I currently have two worksheets, 1 consists of a list of companies and their unique ID's, one company per row. The other one consists of the same firms, but multiple rows, with in the columns after the company name, a number. I would like to get the numbers from worksheet 2, to the first worksheet. Or put differently, I would like to get the data from the different rows from worksheet two, to rows in worksheet 1. Doing it manually is not really an option, I have hundreds of firms in the dataset.
I tried to match it with VLOOKUP, but it didn't really work out. Can someone else think of an answer? Thanks a lot!

So to clarify:

Please Login or Register  to view this content.

[JBeaucaire]

I don't think you even need the worksheet1. Just make a copy of worksheet 2 and run this macro:

Please Login or Register  to view this content.

How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

[hees]

Thanks for the quick answer!
I want to get all the data to worksheet 1 because that is my primary dataset.

However, I get an error when trying to run the macro: Ambiguous name detected: Consolidate. Probably because "Sub Consolidate()" is in there twice? Renaming one doesnt help..

[hees]

I see, the code was in there twice. I tried messing around with it. However, in my current format with the columns like this

Please Login or Register  to view this content.

It can't find cells. When I put the Number (or actually it is a percentage) as first column, it matches the other way around.

After some lay-out changes, I managed to get it partly working. However, suppose firm Apple has 4 number (so four rows), it creates a second row in the final result for the last two number. So the sheet only generates 1 extra column and uses more rows for numbers not fitting in these two columns.

[hees]

Anyone with an idea?
It comes down to transposing duplicate rows to one row, adding the different information that the duplicates have in column two, to more columns.

[JBeaucaire]

The macro is sound as I've used it for his task in the past. Before we can discount it you should see it actually working.

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook. I'll see if I can spot why your having trouble with it.