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,734
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,734
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,734
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,081,626
Messages
5,360,101
Members
400,570
Latest member
Ben Morgan 1985

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top