Transpose 2 column [Key, values] list to mulit column [unique Key, Value1, value2, value3, ......] list

[ganesannv]

I have the following data in Sheet 1:

Code Values
1001 2101
1001 5205
1001 2605
1001 9285
2604 4256
2604 7458
2604 3555

Required result:

Code Value1 Value2 Value3 Value4
1001 2101 5205 2605 9285
2604 4256 7458 3555

Can get a formula to get the result.

[p45cal]

What version of Excel do you have?
Does it have to be a formula?
Could it be a user defined function (uses macro/vba but is used just like a normal formula in a cell).

[DocAElstein]

Hi, ganesannv
Welcome to ExcelFox

Please answer the questions from p45cal as it will help us to help you.

Regarding you question, I am no formula expert, but I will give my input…
I am not sure if you Can get a formula to get the result. I am not a formula expert, but I expect it would be a tall order to get a single formula to get the results. Maybe theoretically possible, but I can’t do it, at least not in a realistic time. It would probably take me a year, I expect!
( I have seen some single formula solutions that transform your data into a form of your wanted Value1, value2, Value3 ... output, but they don't include thhe first unique data column that you also want in the output)

Possibly you are asking for a formula solution, rather than a formula?



But, anyway, for now, I will try to make a start for you....
In the first column of results you have,
1001
2604
, which is getting the unique values from this column
1001
1001
1001
1001
2604
2604
2604
If you try and Google for any variation of Excel formula Unique Values then you will get plenty of hits.
One array formula seems to come up time and time again, applied to your data, and wanted results, assuming it looks like this, https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=16658&viewfull=1#post16658
, the formula for you would be:
=IFERROR(INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D1,$ A$2:$A$8),0)),"")
If you are using early versions of Excel you may need a longer formula version
=IF(ISERROR(INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D 1,$A$2:$A$8),0))),"",INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$8 ),0)))

You need to put one of those formulas in D2, enter it using the CSE type 1 entry, and drag it down
So that would be half the story.



To get the rest, one solution I can think of would be some sort of VLookUp formula, which
_ has the look up value of the unique code,
_ is fiddled to have the LookUp range in it, where the first value in that LookUp range would be the next for a particular code.

I have seen some variations of those sorts of formulas.
I expect I could find them on a search or even have a go myself.
But I will wait to see if a smarter formula expert picks it up, and also wait to see what feedback we get from you first.

Please give us some feedback so we know better what it is you want, or fuck off! :)

Alan

[sandy666]

you can try Power Query (excel 2016 and up)
simple version:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Type = Table.TransformColumnTypes(Source,{{"Values", type text}}),
GR = Table.Group(Type, {"Code"}, {{"GrBy", each Table.Transpose(Table.FromList([Values]))}}),
Exp = Table.ExpandTableColumn(GR, "GrBy", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}),
Type2 = Table.TransformColumnTypes(Exp,{{"Column1", type number}, {"Column2", type number}, {"Column3", type number}, {"Column4", type number}})
in
Type2

A
B
C
D
E
F
G
H
I

1CodeValuesCodeColumn1Column2Column3Column4


2
1001
2101
1001
2101
5205
2605
9285


3
1001
5205
2604
4256
7458
3555


4
1001
2605


5
1001
9285


6
2604
4256


7
2604
7458


8
2604
3555

[p45cal]

you can try Power Query (excel 2016 and up)simple version:
Doesn't this assume a max of 4 repeated codes?
I was waiting for the OP to come back before posting.
I did a PQ offering. A bit convoluted (I'm sure you can streamline it):
The query:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Code"}, {{"grp", each _, type table [Code=number, Values=number]}}),
#"Invoked Custom Function" = Table.Combine(Table.AddColumn(#"Grouped Rows", "fnTranspose2", each fnTranspose2([grp]))[fnTranspose2])
in
#"Invoked Custom Function"
The called function named fnTranspose2:
(tbl)=> [AddIndx = Table.AddIndexColumn(tbl, "Index", 1, 1, Int64.Type),
AddPrefix = Table.TransformColumns(AddIndx, {{"Index", each "Value" & Text.From(_), type text}}),
TakeColms = Table.ReorderColumns(AddPrefix,{"Index", "Code", "Values"}),
Result = Table.Pivot(TakeColms, List.Distinct(TakeColms[Index]), "Index", "Values", List.Sum)][Result]

Also a formula approach:
In one cell (eg. cell F8):
=UNIQUE(Table1[Code])
In the cell to the right:
=TRANSPOSE(FILTER(Table1[Values],Table1[Code]=F8))
copied down as far as necessary. F8 is the address of that cell to the right.

[sandy666]

Code from above was for example from OP
Here is more flexible code

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
TypeRD = Table.Distinct(Table.TransformColumnTypes(Source,{ {"Values", type text}})),
GR = Table.Group(TypeRD, {"Code"}, {{"GrBy", each Table.Transpose(Table.FromList([Values]))}}),
MT = Table.AddColumn(GR, "ColCount", each Table.ColumnCount([GrBy])),
LD = List.Distinct(List.Combine(Table.AddColumn(MT, "ColNames", each Table.ColumnNames([GrBy]))[ColNames])),
EXRC = Table.RemoveColumns(Table.ExpandTableColumn(MT,"GrBy",LD),{"ColCount"}),
UNP = Table.UnpivotOtherColumns(EXRC, {"Code"}, "Attribute", "Value"),
RPL = Table.ReplaceValue(Table.TransformColumnTypes(UNP, {{"Value", Int64.Type}}),"Column","Value",Replacer.ReplaceText,{"Attribute"}),
PVT = Table.Pivot(RPL, List.Distinct(RPL[Attribute]), "Attribute", "Value")
in
PVT

A
B
C
D
E
F
G
H
I
J
K
L
M

1CodeValuesCodeValue1Value2Value3Value4Value5Value 6Value7Value8


2
1001
2101
1001
2101
2605
5205
9285


3
1001
2605
2604
3555
4256
7458


4
1001
5205
3111
1218
1222
1226
1230
1234
1235
1236
1237


5
1001
9285


6
2604
3555


7
2604
4256


8
2604
7458


9
3111
1218


10
3111
1222


11
3111
1226


12
3111
1230


13
3111
1234


14
3111
1234


15
3111
1235


16
3111
1236


17
3111
1237


btw. functions UNIQUE and FILTER are for EX365 and higher but not lower version

[ganesannv]

Instead of formula, UD function is also OK
Thanks

[ganesannv]

it works. Let me try for more data.
Thanks