Need help in arranging the data in xl from Vertical to Horizontal

[ans511]

Dear experts

I need your help in arranging the data in xl from Vertical to Horizontal.

Attaching the file with Input and output expected.

The main constraints is How man lines a serial number will have is not known
The data file would have large line items

Please help and thanks in advance

Rgds

 

[Marc L]

Hi !

In real worksheet, where starts source area ?
And where output should be ?
​

 

[ans511]

Hi !

In real worksheet, where starts source area ?
And where output should be ?
​

Hi Marc

Thanks for the response.

In real work sheet source starts from A1 which is a header and the output can be in a new worksheet or a work book

I hope I answered your question.

Thanks once again

 

[Marc L]

Try this !​

Sub Demo1()
     Application.ScreenUpdating = False
     Sheet2.UsedRange.Clear
With Sheet1.Cells(1).CurrentRegion.Rows
        .Item("1:2").Copy Sheet2.Cells(1)
        L& = 2
    For R& = 3 To .Count
         V = Application.Match(.Cells(R, 1).Value, Sheet2.UsedRange.Columns(1), 0)
        If IsError(V) Then
            L = L + 1
            .Item(R).Copy Sheet2.Cells(L, 1)
        Else
            .Item(R).Columns("B:C").Copy Sheet2.Cells(V, 1).End(xlToRight)(1, 2)
        End If
    Next
End With
With Sheet2.UsedRange.Rows
     Application.Goto .Cells(1), True
    .HorizontalAlignment = xlCenter
    .Range("B1:C1").Value = [B1:C1&"1"]
    .Range("B1:C1").AutoFill .Cells(2).Resize(, .Columns.Count - 1)
    .Item("2:" & .Count).Borders.ColorIndex = xlAutomatic
End With
     Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[ans511]

Thanks Marc

I got below error



I copied and pasted the code in new book .

Added the data in sheet 1 ...
Added another blank sheet 2..

Attaching herewith the file for your reference
Rgds
Andy

 

[Marc L]

Did you first try with your original attachment ?!

Works with both attachments on my side …
​

 

[ans511]

Hey Marc it worked I added data in Sheet 2 and I got the result in sheet 1.

Thanks a Ton buddy ....

One question I have , How many rows will it work ?

 

[Marc L]

No rows limit but the result limit is the number of columns … (16384)

How many rows in real source data ?
​