TRANSPOSE-type question

[ibclc2]

[FONT=&quot]I have a spreadsheet with 1000 rows that look like this:[/FONT]
[FONT=&quot]LNAME FNAME YEAR DONATION[/FONT]
[FONT=&quot]Adams John 1995 100
Adams John 1996 150
Smith Bob 1995 100
Smith Bob 1997 175
Smith Bob 1998 150[/FONT]
[FONT=&quot]I want to transform the spreadsheet to this, with only one line per donor:[/FONT]
[FONT=&quot]LNAME FNAME YEAR DONATION YEAR DONATION YEAR DONATION
Adams John 1995 100 1996 150
Smith Bob 1995 100 1997 175 1998 150[/FONT]
[FONT=&quot]The TRANSPOSE function doesn’t work because it puts years and donations
on two different lines. The number of donations per person varies.
Is there a way to do this with formulas, or arrays? Or is VBA the only choice?[/FONT]
[FONT=&quot]Thanks. [/FONT]

 

[sandy666]

you can try PowerQuery aka Get&Transform (depends on your excel version)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]LNAME[/td][td=bgcolor:#5B9BD5]FNAME[/td][td=bgcolor:#5B9BD5]YEAR[/td][td=bgcolor:#5B9BD5]DONATION[/td][td][/td][td=bgcolor:#70AD47]LNAME[/td][td=bgcolor:#70AD47]FNAME[/td][td=bgcolor:#70AD47]Value.1[/td][td=bgcolor:#70AD47]Value.2[/td][td=bgcolor:#70AD47]Value.3[/td][td=bgcolor:#70AD47]Value.4[/td][td=bgcolor:#70AD47]Value.5[/td][td=bgcolor:#70AD47]Value.6[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Adams[/td][td=bgcolor:#DDEBF7]John[/td][td=bgcolor:#DDEBF7]

1995​

[/td][td=bgcolor:#DDEBF7]

100​

[/td][td][/td][td=bgcolor:#E2EFDA]Adams[/td][td=bgcolor:#E2EFDA]John[/td][td=bgcolor:#E2EFDA]

1995​

[/td][td=bgcolor:#E2EFDA]

100​

[/td][td=bgcolor:#E2EFDA]

1996​

[/td][td=bgcolor:#E2EFDA]

150​

[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Adams[/td][td]John[/td][td]

1996​

[/td][td]

150​

[/td][td][/td][td]Smith[/td][td]Bob[/td][td]

1995​

[/td][td]

100​

[/td][td]

1997​

[/td][td]

175​

[/td][td]

1998​

[/td][td]

150​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Smith[/td][td=bgcolor:#DDEBF7]Bob[/td][td=bgcolor:#DDEBF7]

1995​

[/td][td=bgcolor:#DDEBF7]

100​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Smith[/td][td]Bob[/td][td]

1997​

[/td][td]

175​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Smith[/td][td=bgcolor:#DDEBF7]Bob[/td][td=bgcolor:#DDEBF7]

1998​

[/td][td=bgcolor:#DDEBF7]

150​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]
let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LNAME", type text}, {"FNAME", type text}, {"YEAR", Int64.Type}, {"DONATION", Int64.Type}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"YEAR", "DONATION"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Only Selected Columns", {"LNAME", "FNAME"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Value", each Table.Column([Count],"Value")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Value", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3", "Value.4", "Value.5", "Value.6"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", Int64.Type}, {"Value.2", Int64.Type}, {"Value.3", Int64.Type}, {"Value.4", Int64.Type}, {"Value.5", Int64.Type}, {"Value.6", Int64.Type}})
in
    #"Changed Type1"[/SIZE]

 

[Special-K99]

VBA solution
Input on Sheet1, output on Sheet 2

Sub k1()
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
j = ""
k = ""
l = 0
For i = 1 To lastrow
If Worksheets("Sheet1").Cells(i, 1) <> j Or Worksheets("Sheet1").Cells(i, 2) <> k Then
l = l + 1
m = 3
Worksheets("Sheet2").Cells(l, 1) = Worksheets("Sheet1").Cells(i, 1)
Worksheets("Sheet2").Cells(l, 2) = Worksheets("Sheet1").Cells(i, 2)
j = Worksheets("Sheet1").Cells(i, 1)
k = Worksheets("Sheet1").Cells(i, 2)
End If
Worksheets("Sheet2").Cells(l, m) = Worksheets("Sheet1").Cells(i, 3)
Worksheets("Sheet2").Cells(l, m + 1) = Worksheets("Sheet1").Cells(i, 4)
m = m + 2

Next i
End Sub