transposition dependent on row contents

Hi,

I've been searching through the forum for a way to do this... I can't believe this hasn't been covered before so pls excuse the double post. I've a set of data of 1000s of rows and two columns (A & B) and would like to transpose the information into multiple columns whenever the data in column A changes.

so from:

X 140
X 22
X 500
Y 33
Y 5
Z 1

I get this:

Column A     Column B     Column C
    X             Y              Z
   22             5              1
   140           33
   500

Attached a spreadsheet with some more data - if the translated information can be automatically sorted by ascending as well that would be fantastic.

Thanks for your help

I'll be doing this regularly on different spreadsheets, so something like a VBA script would be ideal

Try this. Yuo need to add a header in A1 and B1.

Sub Macro2()
 
Dim rng As Range, rng2 As Range, c As Long, r As Range
 
Application.DisplayAlerts = False

With Sheets("RAW DATA")
    Sheets.Add().Name = "Temp"
    .Range("A1", .Range("A1").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Temp").Range("A1"), Unique:=True
    Set rng = Sheets("Temp").Range("A2", Sheets("Temp").Range("A2").End(xlDown))
     For Each rng2 In rng
        c = c + 1
        Sheets("DESIRED RESULT").Cells(1, c) = rng2
        .Range("A1").AutoFilter field:=1, Criteria1:=rng2
        Set r = .AutoFilter.Range
        r.Offset(1, 1).Resize(r.Rows.Count - 1, 1).Copy Sheets("DESIRED RESULT").Cells(2, c)
        Sheets("DESIRED RESULT").Cells(1, c).Resize(r.Rows.Count - 1).Sort key1:=Sheets("DESIRED RESULT").Cells(1, c), order1:=xlAscending, header:=xlYes
    Next rng2
    Sheets("Temp").Delete
    .AutoFilterMode = False
End With
 
Application.DisplayAlerts = True
    
End Sub