SOLVED

# Matching pair of values in two columns

Occasional Contributor

# Matching pair of values in two columns

Hi everyone,
There is a table where some data are stored in one columned, but it may happen that the same data could be found in the second column. I need to pair thouse entries and declare them as having equal meaning by grouping them in the third column, like given below. Note that the entries in "Group" column are done manualy.

 Prod_1 Prod_2 Group 00214 08756 1 00214 14267 1 00273 09783 2 00278 11891 3 08756 00214 1 09783 00273 2 11891 00278 3 14267 00214 1

So, I'm asking for the formula, which will help me to generate group numbers for "Group" column over all record set vithout any VBA coding.

Thanks in advance,

13 Replies

# Re: Matching pair of values in two columns

One more commnet: For the same pairs group number can be "Prod_1" & "Prod_2"

# Re: Matching pair of values in two columns

The attached file lists the unique products from both columns in i3#

c2# looks for matches in i3#

``````=LET(prod1;A2:A9;
prod2;B2:B9;
match1;XMATCH(prod1;I3#);
match2;XMATCH(prod2;I3#);
output;IF(match2<match1;match2;match1);
output
)``````

The vstack in i3 is done by

``````=LET(list1;A2:A100000;
list2;B2:B100000;
maxRows;MAX(ROWS(list1);ROWS(list2));
seq;SEQUENCE(maxRows*2;;0);
row;MOD(seq;maxRows)+1;
loop;INT(seq/maxRows)+1;
dimensions;CHOOSE(loop;
INDEX(list1;row;SEQUENCE(1;COLUMNS(list1)));
INDEX(list2;row;SEQUENCE(;COLUMNS(list2))));
output;UNIQUE(dimensions);
output
)``````

If you want to debug the LET function, change the last line from output to as maxRows and you will see the result from that part.

# Re: Matching pair of values in two columns

Hmm, very interesting. Does it work for text values also? because, for some reason, when I put the text Products codes then the formula give an error

# Re: Matching pair of values in two columns

@George_Jokha Seems ok with version 2201 (Excel 365, jan 2022).

In cell i3, you may want to add sort but it will give you different group numbers. If group name is important you can use XLOOKUP instead of XMATCH (in attached file)

output;SORT(UNIQUE(dimensions));

# Re: Matching pair of values in two columns

@bosinander Thank you very much. It seems to be good dessision. Difficult, but good! :))

# Re: Matching pair of values in two columns

But.. ooh, "x" formulas seems to work only for higher version of the windows. I have windows 10

# Re: Matching pair of values in two columns

You're welcome
XMATCH and XLOOKUP are not very new but quite. Both of the are available without X and be functional in this case. But... LET, SEQUENCE and UNIQUE takes more effort to implement in older versions. 365 holds the latest possibilities, inluding the fantastic lambda (different chapter since it so far is not in use here).

# Re: Matching pair of values in two columns

@bosinander Hi (Good morning from me )

I have tried many times to apply formulas you've provided, but unfortunately, my system bocks the excel with "No responding" result.

Is it possible to upload my file (about 350 rows in total, two columns) and you could help me to group the pairs? If it won't bother you much...

P.S. BTW - I can not find the button here to upload the any excel file, as you did.

best response confirmed by George_Jokha (Occasional Contributor)
Solution

# Re: Matching pair of values in two columns

When you click "reply" you have a link to the left above the Post button where you can open full text editor.

Well there you can attach the file just above the Post Button.

Make sure there is no names or secret data in it and I will take a look.

# Re: Matching pair of values in two columns

@bosinander I'm newby here (I'm active in Power BI community mainly), so, unfortunately, I could not find the corresponding upload (attachment) button. What I can do is to provide the list in a table:

 Prod_1 Prod_2 00214 08756 00214 14267 00273 09783 00278 11891 00284 09829 00285 09829 00366 09526 00592 12174 01311 02149 01312 03771 01312 12659 01314 06377 01336 10416 01488 10415 01568 12309 01569 07137 01570 07138 01570 08624 01570 11417 01571 11418 01571 12162 01581 07489 01581 07859 01581 07869 01582 08319 01583 07877 01583 07883 01591 11219 01593 10428 01842 10714 01843 10061 01843 11228 01987 04509 01987 07412 01987 11230 01996 08318 02149 01311 02234 11567 03310 11235 03358 10486 03365 08153 03437 10885 03586 11920 03771 01312 03854 12567 03868 11921 03877 12560 03878 12562 03878 14652 03880 12563 03882 12526 03882 14403 03883 08004 03883 14486 03884 08010 04108 12170 04509 01987 04790 06481 05102 11891 05112 05113 05112 12048 05113 05112 05296 12327 05652 11234 05705 09783 05830 12807 05831 07363 05839 10281 06032 12168 06170 12470 06170 14288 06173 12806 06377 01314 06377 14484 06426 10997 06479 10414 06481 04790 06481 10417 06585 10492 06609 10487 06609 14297 07137 01569 07138 01570 07142 13485 07142 14261 07199 12807 07296 11920 07333 08006 07334 08006 07335 08006 07336 08006 07337 12319 07338 12319 07347 12346 07363 05831 07412 01987 07444 11235 07489 01581 07854 09829 07858 10714 07859 01581 07868 10714 07869 01581 07871 10714 07873 11234 07877 01583 07883 01583 07938 11237 07940 09783 07976 09925 07976 10033 07976 10208 07976 11577 07991 12161 07996 12328 07997 12327 08003 12561 08003 14378 08004 03883 08006 07333 08006 07334 08006 07335 08006 07336 08006 12311 08010 03884 08018 12560 08020 12561 08020 14378 08024 11375 08024 11376 08032 11221 08033 11224 08153 03365 08153 12701 08318 01996 08319 01582 08320 11219 08624 01570 08756 00214 08761 09829 08763 12470 08763 14288 08767 12532 08767 14379 08849 12330 08962 12702 08972 09910 08983 12527 09526 00366 09706 12327 09774 11891 09783 00273 09783 05705 09783 07940 09783 11011 09829 00284 09829 00285 09829 07854 09829 08761 09910 08972 09924 12328 09925 07976 10004 12585 10023 12170 10033 07976 10061 01843 10062 11235 10204 12174 10208 07976 10267 12307 10277 13050 10280 10714 10281 05839 10414 06479 10415 01488 10416 01336 10417 06481 10418 11234 10428 01593 10429 10962 10474 10784 10486 03358 10487 06609 10492 06585 10668 11222 10670 11221 10673 11223 10675 11224 10714 01842 10714 07858 10714 07868 10714 07871 10714 10280 10714 12339 10714 14327 10714 14398 10714 14456 10736 12560 10782 12560 10784 10474 10786 12561 10786 14378 10885 03437 10914 11373 10914 12343 10914 14664 10959 12406 10962 10429 10963 11117 10997 06426 10997 12564 10997 12565 10998 12562 10998 14652 11011 09783 11116 13485 11116 14261 11117 10963 11219 01591 11219 08320 11219 12312 11219 12341 11221 08032 11221 10670 11221 12866 11221 13503 11221 14298 11221 14406 11221 14407 11222 10668 11222 12865 11222 13502 11222 14408 11222 14409 11223 10673 11223 12867 11223 13504 11223 14411 11223 14413 11224 08033 11224 10675 11224 12868 11224 13505 11224 14412 11225 12168 11228 01843 11230 01987 11234 05652 11234 07873 11234 10418 11235 03310 11235 07444 11235 10062 11237 07938 11372 13485 11372 14261 11373 10914 11373 14263 11375 08024 11376 08024 11417 01570 11418 01571 11567 02234 11577 07976 11891 00278 11891 05102 11891 09774 11920 03586 11920 07296 11921 03868 12048 05112 12161 07991 12161 14286 12162 01571 12168 06032 12168 11225 12170 04108 12170 10023 12170 14252 12174 00592 12174 10204 12174 12345 12307 10267 12307 14319 12307 14490 12309 01568 12311 08006 12312 11219 12314 12563 12319 07337 12319 07338 12327 05296 12327 07997 12327 09706 12327 12334 12327 12555 12328 07996 12328 09924 12330 08849 12334 12327 12339 10714 12341 11219 12343 10914 12343 14263 12345 12174 12346 07347 12406 10959 12470 06170 12470 08763 12470 12556 12470 12558 12470 14393 12470 14395 12526 03882 12527 08983 12532 08767 12555 12327 12556 12470 12556 14288 12558 12470 12558 14288 12560 03877 12560 08018 12560 10736 12560 10782 12560 14378 12561 08003 12561 08020 12561 10786 12562 03878 12562 10998 12563 03880 12563 12314 12564 10997 12565 10997 12567 03854 12585 10004 12659 01312 12701 08153 12702 08962 12702 14318 12806 06173 12807 05830 12807 07199 12865 11222 12866 11221 12867 11223 12868 11224 13050 10277 13485 07142 13485 11116 13485 11372 13502 11222 13503 11221 13504 11223 13505 11224 14252 12170 14261 07142 14261 11116 14261 11372 14263 11373 14263 12343 14267 00214 14286 12161 14288 06170 14288 08763 14288 12556 14288 12558 14297 06609 14298 11221 14318 12702 14319 12307 14327 10714 14378 08003 14378 08020 14378 10786 14378 12560 14379 08767 14393 12470 14395 12470 14398 10714 14403 03882 14406 11221 14407 11221 14408 11222 14409 11222 14411 11223 14412 11224 14413 11223 14456 10714 14484 06377 14486 03883 14490 12307 14652 03878 14652 10998 14664 10914

# Re: Matching pair of values in two columns

@George_Jokha See attached file.

I added a group counter in column K. Note K36 telling that that group number is not in use (zero times correct since A45 has 01312 in column B and thus belongs to group 10 on row 11).

ie, make sure that the logic is the way you want it to be.

-/-

I have extended the formula in c2 to use from a2 and end downwards, like Ctrl+Shift+Down Arrow.

endDown(a2) may be changed to A397 but will then not follow dynamically. And same for endDown(B2).

``````=LET(prod1;A2:endDown(A2);
prod2;B2:endDown(B2);
match1;    XLOOKUP(prod1;I3#;OFFSET(I3#;0;1));
match2;    XLOOKUP(prod2;I3#;OFFSET(I3#;0;1));
output;    IF(match2<match1;match2;match1);
output
)``````

endDown is a workbook local function built with the function LAMBDA. If you copy a cell using such function and paste it in another workbook, it will follow and be functional but as of 2022, only with 365.

The (long) formula for endDown is located in the name manager (Ctrl+F3).

# Re: Matching pair of values in two columns

Thank you very much! It realy helps! Great!

# Re: Matching pair of values in two columns

@George_Jokha You're welcome