Sorting values with formula, in a weird way.

[Badgerfh]

Hello and I'm in need of some help
Not sure how to word it to make sense, som I'm doing it with an example.


Below is my data set, they are independent of each other, as in different lengths and locations.

Model grp.	Model	Unique id		Prt.lst.	Model grp.
1ab	Red	1ab1		aa 1ab bb	1ab
1ab	Blue	1ab2		aa 1ab bb	1ab
1ab	Green	1ab3		aa 1ab bb	1ab
2ab	Blue	2ab		aa 2ab bb	2ab
3ab	Red	3ab1		aa 3ab bb	3ab
3ab	Orange	3ab2		aa 3ab bb	3ab

And this is what I want my final list to look like.

Prt.lst.	Model grp	Model
aa 1ab bb	1ab	Red
aa 1ab bb	1ab	Blue
aa 1ab bb	1ab	Green
aa 2ab bb	2ab	Blue
aa 3ab bb	3ab	Red
aa 3ab bb	3ab	Orange

Is there any hope to get this to work?

Sorry if vague description, happy to provide more info if it helps, just don't know what I'd need to say

 

[alansidman]

Bring each table into Power Query which is called Get and Transform Data in O365. Once there create a join. Mcodes for each follows

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Model grp.", type text}, {"Model", type text}, {"Unique id", type text}})
in
    #"Changed Type"

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Prt.lst.", type text}, {"Model grp.", type text}})
in
    #"Changed Type"

let
    Source = Table.NestedJoin(Table2, {"Model grp."}, Table1, {"Model grp."}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {"Model"}, {"Table1.Model"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Table1")
in
    #"Removed Duplicates"

Book8
	A	B	C	D	E	F
1	Model grp.	Model	Unique id		Prt.lst.	Model grp.
2	1ab	Red	1ab1		aa 1ab bb	1ab
3	1ab	Blue	1ab2		aa 1ab bb	1ab
4	1ab	Green	1ab3		aa 1ab bb	1ab
5	2ab	Blue	2ab		aa 2ab bb	2ab
6	3ab	Red	3ab1		aa 3ab bb	3ab
7	3ab	Orange	3ab2		aa 3ab bb	3ab
8
9
10	Prt.lst.	Model grp.	Table1.Model
11	aa 1ab bb	1ab	Red
12	aa 1ab bb	1ab	Blue
13	aa 1ab bb	1ab	Green
14	aa 2ab bb	2ab	Blue
15	aa 3ab bb	3ab	Red
16	aa 3ab bb	3ab	Orange
Sheet1

 

[Badgerfh]

Thanks a lot for the help, and for the quick reply, it got me looking into Power Query.

I got it working, I didn't know about Power Query before, but have scraped on the surface of it now.
The link on the end of the forum "Power Query Examples" helped more I think than the M code snips tho