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:

Worksheet 1:
Company            ID      Number 1   Number 2   Number 3
Apple               AP
Microsoft           MS


Worksheet 2: 
Company            ID     Number 
Apple              AP      2,2 
Apple              AP      4,5
Apple              AP      1,1

Microsoft          MS      5,6
Microsoft          MS      4,0

[JBeaucaire]

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

Option Explicit

Sub Consolidate()
'JBeaucaire  (9/18/2009)
'Columnar data is Sorted/Matched by column A values, merge all other cells into row format
Dim LastRow As Long, NextCol As Long
Dim LastCol As Long, Rw As Long, Cnt As Long
Dim delRNG As Range
Application.ScreenUpdating = False

'Remove blank rows
    Range("A1:A" & Rows.Count).SpecialCells(xlBlanks).EntireRow.Delete xlShiftUp
    
'Sort data
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1").CurrentRegion.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes
   
'Seed the delete range
    Set delRNG = Range("A" & LastRow + 10)
   
'Group matching names
    For Rw = LastRow To 2 Step -1
        If Cells(Rw, "A").Value = Cells(Rw - 1, "A").Value Then
            Range(Cells(Rw, "C"), Cells(Rw, Columns.Count).End(xlToLeft)).Copy _
                Cells(Rw - 1, Columns.Count).End(xlToLeft).Offset(0, 1)
            Set delRNG = Union(delRNG, Range("A" & Rw))
        End If
    Next Rw

'Delete unneeded rows all at once
    delRNG.EntireRow.Delete (xlShiftUp)
    Set delRNG = Nothing

'Add titles
    LastCol = Cells(1, 1).CurrentRegion.Columns.Count
    With Range("C1", Cells(1, LastCol))
        .FormulaR1C1 = "=""Number "" & COLUMN(RC[-2])"
        .Value = .Value
    End With

Cells.Columns.AutoFit
Application.ScreenUpdating = True
End Sub
Option Explicit

Sub Consolidate()
'JBeaucaire  (9/18/2009)
'Columnar data is Sorted/Matched by column A values, merge all other cells into row format
Dim LastRow As Long, NextCol As Long
Dim LastCol As Long, Rw As Long, Cnt As Long
Dim delRNG As Range
Application.ScreenUpdating = False

'Remove blank rows
    Range("A1:A" & Rows.Count).SpecialCells(xlBlanks).EntireRow.Delete xlShiftUp
    
'Sort data
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1").CurrentRegion.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes
   
'Seed the delete range
    Set delRNG = Range("A" & LastRow + 10)
   
'Group matching names
    For Rw = LastRow To 2 Step -1
        If Cells(Rw, "A").Value = Cells(Rw - 1, "A").Value Then
            Range(Cells(Rw, "C"), Cells(Rw, Columns.Count).End(xlToLeft)).Copy _
                Cells(Rw - 1, Columns.Count).End(xlToLeft).Offset(0, 1)
            Set delRNG = Union(delRNG, Range("A" & Rw))
        End If
    Next Rw

'Delete unneeded rows all at once
    delRNG.EntireRow.Delete (xlShiftUp)
    Set delRNG = Nothing

'Add titles
    LastCol = Cells(1, 1).CurrentRegion.Columns.Count
    With Range("C1", Cells(1, LastCol))
        .FormulaR1C1 = "=""Number "" & COLUMN(RC[-2])"
        .Value = .Value
    End With

Cells.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

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

Firm  ID  Number

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.