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



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
Joined
Oct 24, 2015
Messages
3,828
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
Joined
Oct 10, 2011
Messages
4,344
Office Version
365
Platform
Windows
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
Joined
Oct 13, 2019
Messages
4
@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
Joined
Oct 24, 2015
Messages
3,828
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
Joined
Oct 13, 2019
Messages
4
@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
Joined
Oct 24, 2015
Messages
3,828
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]
 

Forum statistics

Threads
1,082,346
Messages
5,364,822
Members
400,814
Latest member
gangstar67

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top