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

**ganesannv** · 07-31-2022, 02:58 PM

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** · 08-01-2022, 02:28 AM

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** · 08-01-2022, 01:05 PM

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/showthrea...ll=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:D1,$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** · 08-02-2022, 02:24 AM

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

Code:

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	E	F	G	H	I
1	Code	Values	Code	Column1	Column2	Column3	Column4
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** · 08-03-2022, 01:29 AM

Originally Posted by sandy666

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:

Code:

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:

Code:

(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** · 08-03-2022, 03:08 AM

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

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	E	F	G	H	I	J	K	L	M
1	Code	Values	Code	Value1	Value2	Value3	Value4	Value5	Value6	Value7	Value8
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