Printable View
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.
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).
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
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
Code Values Code Column1 Column2 Column3 Column4
Doesn't this assume a max of 4 repeated codes?Quote:
Originally Posted by sandy666
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:The called function named fnTranspose2: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"
Also a formula approach: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]
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.
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
Code Values Code Value1 Value2 Value3 Value4 Value5 Value6 Value7 Value8
btw. functions UNIQUE and FILTER are for EX365 and higher but not lower version
Instead of formula, UD function is also OK
Thanks
it works. Let me try for more data.
Thanks