Merging multiple rows into a single row

**barry_jordan** · 07-02-2010, 06:43 AM

I have a spreadsheet that lists all customers and order numbers, with each order number having a different line, and the customers shown multiple times.

I would like to merge this, so each customer number is shown once, and the order numbers shown in the following cells. I've attached an example sheet.

I've seen various methods explained, but can't figure them out, so I'd appreciate an example if anyone can help.

Cheers

Baz

**Marcol** · 07-02-2010, 07:56 AM

Try this

Option Explicit

Sub TransposeOrders()
    Dim wsData As Worksheet
    Dim LastRow As Long, RowNo As Long, ColNo As Long
    Dim StartRow As Long
    Dim RefNo As Long
    Dim c As Integer, i As Integer
    
    Set wsData = ActiveWorkbook.Worksheets("Sheet1")
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    With wsData.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A2:A" & LastRow), _
                    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add Key:=Range("B2:B" & LastRow), _
                    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A1:B" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    StartRow = 2
    With wsData
        For RowNo = LastRow To StartRow Step -1
            RefNo = .Range("A" & RowNo)
            Do While .Range("A" & RowNo - 1) = RefNo
                RowNo = RowNo - 1
                c = c + 1
            Loop
            For ColNo = 2 To 2 + c
                .Cells(RowNo, ColNo) = .Range("B" & RowNo + i)
                i = i + 1
            Next
            .Range(.Cells(RowNo + 1, 1), .Cells(RowNo + c, 1)).EntireRow.Delete
            i = 0
            c = 0
        Next
    End With
End Sub

Hope this helps

**barry_jordan** · 07-02-2010, 05:14 PM

Thanks Marcol

I've not been able to get the macro working. I've not really used them before.

Is there any chance you could apply it to the spreadsheet I attached so I can see how it works?

I really appreciate your help with this.

**Marcol** · 07-02-2010, 07:56 PM

That was the spreadsheet you attached.

The only differences were that
.
1/. I asumed that your headers were in row 1 in your real worksheet, and data began in row 2.
2/. I added a few lines for testing purposes

If this is not the case, please post a sample sheet, that clearly shows the sheet structure you are using.

I will be happy to help from thereon, and give any explanation you might need.

Cheers

**sensey1** · 01-13-2012, 09:26 AM

i have a similar problem with the one above.

can anyone help me to achieve the result that is in the file attached.

regards,
sebastian

probebun.xls

**Marcol** · 01-13-2012, 10:50 AM

Hi sensey1

Welcome to the forum ...

Read this Forum Rules before the moderators get you...

Forum Rules

2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.

This thread is nearly 2 years old!

If you can Edit/Delete your post, do so, if not you'll need to ask a moderator to help you.

Cheers