Match multiple values from one column with single value from other column

lgrzan

New Member
Joined
Oct 13, 2019
Messages
4
Dear MrExcel community, greetings!

After thorough searching through forum, I could not find an answer to my needs (maybe I didn't search properly?)
I have a following issue: I have a LOC column that has unique values (locations) and GRADE column (which has only 4 possible values).

ex01.png


My problem is: how can I return the LOC values as shown in (lower) blue table? Is there any practical way of doing it? Orange and green columns can be switched/transposed to rows if needed, no problem. Nota bene: I cannot use headers in this table, as it is a part of a bigger project.

Any help would be appreciated and welcome! Thanks a lot in advance!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
with Power Query aka Get&Transform

LOCGRADEBLSMTM3GRADELoc
1P
3​
17870.5263​
2428.082256​
2841.1915​
1​
2P, 2S, 6P, 6S, SS
1S
4​
17782.4735​
2416.118454​
2827.1922​
2​
3S, 4P, 4S, 7P, 7S, SP
2P
1​
23306.66621​
3166.694799​
3705.47016​
3​
1P, 3P, 5P, 5S
2S
1​
23209.01606​
3153.426997​
3689.945​
4​
1S
3P
3​
24167.76524​
3283.692991​
3842.3742​
3S
2​
24070.04729​
3270.415977​
3826.83826​
4P
2​
24148.63217​
3281.093366​
3839.33228​
4S
2​
24050.99435​
3267.82724​
3823.80908​
5P
3​
23817.60667​
3236.116674​
3786.70334​
5S
3​
23699.58448​
3220.080909​
3767.93928​
6P
1​
24151.12243​
3281.43172​
3839.7282​
6S
1​
24053.47228​
3268.163918​
3824.20304​
7P
2​
22473.81681​
3053.534905​
3573.05746​
7S
2​
22549.87446​
3063.868917​
3585.14968​
SP
2​
2431.422827​
330.3593036​
386.566​
SS
1​
6043.836742​
821.1807815​
960.8949​

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Tzype = Table.TransformColumnTypes(Source,{{"LOC", type text}, {"GRADE", Int64.Type}, {"BLS", type number}, {"MT", type number}, {"M3", type number}}),
    Group = Table.Group(Tzype, {"GRADE"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Loc", each Table.Column([Count],"LOC")),
    Extract = Table.TransformColumns(List, {"Loc", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    Sort = Table.Sort(Extract,{{"GRADE", Order.Ascending}})
in
    Sort[/SIZE]

btw. your result is incorrect
 
Last edited:
Upvote 0
Another option if you have a later ver. of Excel with the TEXTJOIN function.
This is an array formula that must be entered with CTRL-SHIFT-ENTER.
Excel Workbook
ABCDEF
1
2LOCGRADEBLSMTM3
31P317870.52632428.0822562841.1915
41S417782.47352416.1184542827.1922
52P123306.666213166.6947993705.47016
62S123209.016063153.4269973689.945
73P324167.765243283.6929913842.3742
83S224070.047293270.4159773826.83826
94P224148.632173281.0933663839.33228
104S224050.994353267.827243823.80908
115P323817.606673236.1166743786.70334
125S323699.584483220.0809093767.93928
136P124151.122433281.431723839.7282
146S124053.472283268.1639183824.20304
157P222473.816813053.5349053573.05746
167S222549.874463063.8689173585.14968
17SP22431.422827330.3593036386.566
18SS16043.836742821.1807815960.8949
19
20GRADELoc
2112P, 2S, 6P, 6S, SS
2223S, 4P, 4S, 7P, 7S, SP
2331P, 3P, 5P, 5S
2441S
Sheet
 
Upvote 0
@sandy666

Thanks for your effort. It's working, however, some minor hiccups surfaced: If I have only 3 "grades", lowest row of the blue table will dissapear. Is there any way to make it fixed? For example: Let all 4 rows be visible and fixed, and if there is no data, it will be blank. Because I am trying to fetch data from this solution and take it elsewhere, so I get errors when last row dissapears after sorting. I hope you understand what I mean?

@AhoyNC

Thanks a million for your solution. While it seems more applicable to my needs than sandy's - I cannot use it as I have Excel 2016, unfortunately.

Thank you both for inputs and help, much appreciated!

Regards,

Lx
 
Upvote 0
you mean like this?

LOCGRADEBLSMTM3GradeLoc
1P
3​
17870.5263​
2428.082256​
2841.1915​
1​
2P, 2S, 6P, 6S, SS
1S
3​
17782.4735​
2416.118454​
2827.1922​
2​
3S, 4P, 4S, 7P, 7S, SP
2P
1​
23306.66621​
3166.694799​
3705.47016​
3​
1P, 1S, 3P, 5P, 5S
2S
1​
23209.01606​
3153.426997​
3689.945​
4​
3P
3​
24167.76524​
3283.692991​
3842.3742​
3S
2​
24070.04729​
3270.415977​
3826.83826​
4P
2​
24148.63217​
3281.093366​
3839.33228​
4S
2​
24050.99435​
3267.82724​
3823.80908​
5P
3​
23817.60667​
3236.116674​
3786.70334​
5S
3​
23699.58448​
3220.080909​
3767.93928​
6P
1​
24151.12243​
3281.43172​
3839.7282​
6S
1​
24053.47228​
3268.163918​
3824.20304​
7P
2​
22473.81681​
3053.534905​
3573.05746​
7S
2​
22549.87446​
3063.868917​
3585.14968​
SP
2​
2431.422827​
330.3593036​
386.566​
SS
1​
6043.836742​
821.1807815​
960.8949​
 
Upvote 0
@sandy666 - Yes, that's exactly what I mean! Because if all 4 rows remain (even blank), I can easily make a reference to that cells and include it in my project. Can you provide the code update, so I can try it out?
Thanks again for your immense help, you're a saviour!
 
Upvote 0
sure,

Code:
[SIZE=1]// Table1
let
    GB = Table.FromList({1..4}, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"LOC", type text}, {"GRADE", Int64.Type}, {"BLS", type number}, {"MT", type number}, {"M3", type number}}),
    Group = Table.Group(Type, {"GRADE"}, {{"Count", each _, type table}}),
    Extract = Table.TransformColumns(Table.AddColumn(Group, "Loc", each Table.Column([Count],"LOC")), {"Loc", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    Merge = Table.NestedJoin(GB,{"Column1"},Extract,{"GRADE"},"Extract",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Merge, "Extract", {"GRADE", "Count", "Loc"}, {"GRADE", "Count", "Loc"}),
    Sort = Table.Sort(Table.RenameColumns(Table.RemoveColumns(Expand,{"GRADE", "Count"}),{{"Column1", "Grade"}}),{{"Grade", Order.Ascending}})
in
    Sort[/SIZE]
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top