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

lgrzan · Oct 13, 2019

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 · Oct 13, 2019

with Power Query aka Get&Transform

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]LOC[/td][td=bgcolor:#5B9BD5]GRADE[/td][td=bgcolor:#5B9BD5]BLS[/td][td=bgcolor:#5B9BD5]MT[/td][td=bgcolor:#5B9BD5]M3[/td][td][/td][td=bgcolor:#70AD47]GRADE[/td][td=bgcolor:#70AD47]Loc[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]1P[/td][td=bgcolor:#DDEBF7]

3

[/td][td=bgcolor:#DDEBF7]

17870.5263

[/td][td=bgcolor:#DDEBF7]

2428.082256

[/td][td=bgcolor:#DDEBF7]

2841.1915

[/td][td][/td][td=bgcolor:#E2EFDA]

1

[/td][td=bgcolor:#E2EFDA]2P, 2S, 6P, 6S, SS[/td][/tr]

[tr=bgcolor:#FFFFFF][td]1S[/td][td]

4

[/td][td]

17782.4735

[/td][td]

2416.118454

[/td][td]

2827.1922

[/td][td][/td][td]

2

[/td][td]3S, 4P, 4S, 7P, 7S, SP[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]2P[/td][td=bgcolor:#DDEBF7]

1

[/td][td=bgcolor:#DDEBF7]

23306.66621

[/td][td=bgcolor:#DDEBF7]

3166.694799

[/td][td=bgcolor:#DDEBF7]

3705.47016

[/td][td][/td][td=bgcolor:#E2EFDA]

3

[/td][td=bgcolor:#E2EFDA]1P, 3P, 5P, 5S[/td][/tr]

[tr=bgcolor:#FFFFFF][td]2S[/td][td]

1

[/td][td]

23209.01606

[/td][td]

3153.426997

[/td][td]

3689.945

[/td][td][/td][td]

4

[/td][td]1S[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]3P[/td][td=bgcolor:#DDEBF7]

3

[/td][td=bgcolor:#DDEBF7]

24167.76524

[/td][td=bgcolor:#DDEBF7]

3283.692991

[/td][td=bgcolor:#DDEBF7]

3842.3742

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]3S[/td][td]

2

[/td][td]

24070.04729

[/td][td]

3270.415977

[/td][td]

3826.83826

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]4P[/td][td=bgcolor:#DDEBF7]

2

[/td][td=bgcolor:#DDEBF7]

24148.63217

[/td][td=bgcolor:#DDEBF7]

3281.093366

[/td][td=bgcolor:#DDEBF7]

3839.33228

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]4S[/td][td]

2

[/td][td]

24050.99435

[/td][td]

3267.82724

[/td][td]

3823.80908

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]5P[/td][td=bgcolor:#DDEBF7]

3

[/td][td=bgcolor:#DDEBF7]

23817.60667

[/td][td=bgcolor:#DDEBF7]

3236.116674

[/td][td=bgcolor:#DDEBF7]

3786.70334

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]5S[/td][td]

3

[/td][td]

23699.58448

[/td][td]

3220.080909

[/td][td]

3767.93928

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]6P[/td][td=bgcolor:#DDEBF7]

1

[/td][td=bgcolor:#DDEBF7]

24151.12243

[/td][td=bgcolor:#DDEBF7]

3281.43172

[/td][td=bgcolor:#DDEBF7]

3839.7282

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]6S[/td][td]

1

[/td][td]

24053.47228

[/td][td]

3268.163918

[/td][td]

3824.20304

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]7P[/td][td=bgcolor:#DDEBF7]

2

[/td][td=bgcolor:#DDEBF7]

22473.81681

[/td][td=bgcolor:#DDEBF7]

3053.534905

[/td][td=bgcolor:#DDEBF7]

3573.05746

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]7S[/td][td]

2

[/td][td]

22549.87446

[/td][td]

3063.868917

[/td][td]

3585.14968

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]SP[/td][td=bgcolor:#DDEBF7]

2

[/td][td=bgcolor:#DDEBF7]

2431.422827

[/td][td=bgcolor:#DDEBF7]

330.3593036

[/td][td=bgcolor:#DDEBF7]

386.566

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]SS[/td][td]

1

[/td][td]

6043.836742

[/td][td]

821.1807815

[/td][td]

960.8949

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

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

AhoyNC · Oct 13, 2019

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

A

B

C

D

E

F

1

2

LOC

GRADE

BLS

MT

M3

3

1P

3

17870.5263

2428.082256

2841.1915

4

1S

4

17782.4735

2416.118454

2827.1922

5

2P

1

23306.66621

3166.694799

3705.47016

6

2S

1

23209.01606

3153.426997

3689.945

7

3P

3

24167.76524

3283.692991

3842.3742

8

3S

2

24070.04729

3270.415977

3826.83826

9

4P

2

24148.63217

3281.093366

3839.33228

10

4S

2

24050.99435

3267.82724

3823.80908

11

5P

3

23817.60667

3236.116674

3786.70334

12

5S

3

23699.58448

3220.080909

3767.93928

13

6P

1

24151.12243

3281.43172

3839.7282

14

6S

1

24053.47228

3268.163918

3824.20304

15

7P

2

22473.81681

3053.534905

3573.05746

16

7S

2

22549.87446

3063.868917

3585.14968

17

SP

2

2431.422827

330.3593036

386.566

18

SS

1

6043.836742

821.1807815

960.8949

19

20

GRADE

Loc

21

1

2P, 2S, 6P, 6S, SS

22

2

3S, 4P, 4S, 7P, 7S, SP

23

3

1P, 3P, 5P, 5S

24

4

1S

Sheet

lgrzan · Oct 13, 2019

@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 · Oct 13, 2019

you mean like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]LOC[/td][td=bgcolor:#5B9BD5]GRADE[/td][td=bgcolor:#5B9BD5]BLS[/td][td=bgcolor:#5B9BD5]MT[/td][td=bgcolor:#5B9BD5]M3[/td][td][/td][td=bgcolor:#70AD47]Grade[/td][td=bgcolor:#70AD47]Loc[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]1P[/td][td=bgcolor:#DDEBF7]

3

[/td][td=bgcolor:#DDEBF7]

17870.5263

[/td][td=bgcolor:#DDEBF7]

2428.082256

[/td][td=bgcolor:#DDEBF7]

2841.1915

[/td][td][/td][td=bgcolor:#E2EFDA]

1

[/td][td=bgcolor:#E2EFDA]2P, 2S, 6P, 6S, SS[/td][/tr]

[tr=bgcolor:#FFFFFF][td]1S[/td][td]

3

[/td][td]

17782.4735

[/td][td]

2416.118454

[/td][td]

2827.1922

[/td][td][/td][td]

2

[/td][td]3S, 4P, 4S, 7P, 7S, SP[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]2P[/td][td=bgcolor:#DDEBF7]

1

[/td][td=bgcolor:#DDEBF7]

23306.66621

[/td][td=bgcolor:#DDEBF7]

3166.694799

[/td][td=bgcolor:#DDEBF7]

3705.47016

[/td][td][/td][td=bgcolor:#E2EFDA]

3

[/td][td=bgcolor:#E2EFDA]1P, 1S, 3P, 5P, 5S[/td][/tr]

[tr=bgcolor:#FFFFFF][td]2S[/td][td]

1

[/td][td]

23209.01606

[/td][td]

3153.426997

[/td][td]

3689.945

[/td][td][/td][td]

4

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]3P[/td][td=bgcolor:#DDEBF7]

3

[/td][td=bgcolor:#DDEBF7]

24167.76524

[/td][td=bgcolor:#DDEBF7]

3283.692991

[/td][td=bgcolor:#DDEBF7]

3842.3742

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]3S[/td][td]

2

[/td][td]

24070.04729

[/td][td]

3270.415977

[/td][td]

3826.83826

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]4P[/td][td=bgcolor:#DDEBF7]

2

[/td][td=bgcolor:#DDEBF7]

24148.63217

[/td][td=bgcolor:#DDEBF7]

3281.093366

[/td][td=bgcolor:#DDEBF7]

3839.33228

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]4S[/td][td]

2

[/td][td]

24050.99435

[/td][td]

3267.82724

[/td][td]

3823.80908

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]5P[/td][td=bgcolor:#DDEBF7]

3

[/td][td=bgcolor:#DDEBF7]

23817.60667

[/td][td=bgcolor:#DDEBF7]

3236.116674

[/td][td=bgcolor:#DDEBF7]

3786.70334

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]5S[/td][td]

3

[/td][td]

23699.58448

[/td][td]

3220.080909

[/td][td]

3767.93928

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]6P[/td][td=bgcolor:#DDEBF7]

1

[/td][td=bgcolor:#DDEBF7]

24151.12243

[/td][td=bgcolor:#DDEBF7]

3281.43172

[/td][td=bgcolor:#DDEBF7]

3839.7282

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]6S[/td][td]

1

[/td][td]

24053.47228

[/td][td]

3268.163918

[/td][td]

3824.20304

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]7P[/td][td=bgcolor:#DDEBF7]

2

[/td][td=bgcolor:#DDEBF7]

22473.81681

[/td][td=bgcolor:#DDEBF7]

3053.534905

[/td][td=bgcolor:#DDEBF7]

3573.05746

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]7S[/td][td]

2

[/td][td]

22549.87446

[/td][td]

3063.868917

[/td][td]

3585.14968

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]SP[/td][td=bgcolor:#DDEBF7]

2

[/td][td=bgcolor:#DDEBF7]

2431.422827

[/td][td=bgcolor:#DDEBF7]

330.3593036

[/td][td=bgcolor:#DDEBF7]

386.566

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]SS[/td][td]

1

[/td][td]

6043.836742

[/td][td]

821.1807815

[/td][td]

960.8949

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

lgrzan · Oct 14, 2019

@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 · Oct 14, 2019

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]

lgrzan · Oct 14, 2019

@sandy666 - Thanks a million! It's tested & working like a charm!

sandy666 · Oct 14, 2019

You are welcome

Have a nice day

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

lgrzan

New Member

Excel Facts

sandy666

Banned - Rules violations

AhoyNC

Well-known Member

lgrzan

New Member

sandy666

Banned - Rules violations

lgrzan

New Member

sandy666

Banned - Rules violations

lgrzan

New Member

sandy666

Banned - Rules violations

Similar threads

Forum statistics

Share this page

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

New Member

Excel Facts

Banned - Rules violations

Well-known Member

New Member

Banned - Rules violations

New Member

Banned - Rules violations

New Member

Banned - Rules violations

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock