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

lgrzan

New Member
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).



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!
 

sandy666

Well-known Member
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:

AhoyNC

Well-known Member
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
211
2223S, 4P, 4S, 7P, 7S, SP
2331P, 3P, 5P, 5S
2441S
Sheet
 

lgrzan

New Member
@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
 

sandy666

Well-known Member
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​
 

lgrzan

New Member
@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!
 

sandy666

Well-known Member
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]
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top