SOLVED

Matching pair of values in two columns

%3CLINGO-SUB%20id%3D%22lingo-sub-3192143%22%20slang%3D%22en-US%22%3EMatching%20pair%20of%20values%20in%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3192143%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3CBR%20%2F%3EThere%20is%20a%20table%20where%20some%20data%20are%20stored%20in%20one%20columned%2C%20but%20it%20may%20happen%20that%20the%20same%20data%20could%20be%20found%20in%20the%20second%20column.%20I%20need%20to%20pair%20thouse%20entries%20and%20declare%20them%20as%20having%20equal%20meaning%20by%20grouping%20them%20in%20the%20third%20column%2C%20like%20given%20below.%20Note%20that%20the%20entries%20in%20%22Group%22%20column%20are%20done%20manualy.%3C%2FP%3E%3CTABLE%20width%3D%22250%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2282%22%3EProd_1%3C%2FTD%3E%3CTD%20width%3D%2284%22%3EProd_2%3C%2FTD%3E%3CTD%20width%3D%2284%22%3EGroup%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E00214%3C%2FTD%3E%3CTD%3E08756%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E00214%3C%2FTD%3E%3CTD%3E14267%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E00273%3C%2FTD%3E%3CTD%3E09783%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E00278%3C%2FTD%3E%3CTD%3E11891%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E08756%3C%2FTD%3E%3CTD%3E00214%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E09783%3C%2FTD%3E%3CTD%3E00273%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E11891%3C%2FTD%3E%3CTD%3E00278%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E14267%3C%2FTD%3E%3CTD%3E00214%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20I'm%20asking%20for%20the%20formula%2C%20which%20will%20help%20me%20to%20generate%20group%20numbers%20for%20%22Group%22%20column%20over%20all%20record%20set%20vithout%20any%20VBA%20coding.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20in%20advance%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3192143%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3192249%22%20slang%3D%22en-US%22%3ERe%3A%20Matching%20pair%20of%20values%20in%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3192249%22%20slang%3D%22en-US%22%3EOne%20more%20commnet%3A%20For%20the%20same%20pairs%20group%20number%20can%20be%20%22Prod_1%22%20%26amp%3B%20%22Prod_2%22%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3192254%22%20slang%3D%22en-US%22%3ERe%3A%20Matching%20pair%20of%20values%20in%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3192254%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1313342%22%20target%3D%22_blank%22%3E%40George_Jokha%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20attached%20file%20lists%20the%20unique%20products%20from%20both%20columns%20in%20i3%23%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bosinander_0-1645438282150.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F349923i50C7E83BD2359AC8%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22bosinander_0-1645438282150.png%22%20alt%3D%22bosinander_0-1645438282150.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ec2%23%20looks%20for%20matches%20in%20i3%23%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLET(prod1%3BA2%3AA9%3B%0A%20%20%20%20%20prod2%3BB2%3AB9%3B%0Amatch1%3BXMATCH(prod1%3BI3%23)%3B%0Amatch2%3BXMATCH(prod2%3BI3%23)%3B%0Aoutput%3BIF(match2%3CMATCH1%3E%3C%2FMATCH1%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThe%20vstack%20in%20i3%20is%20done%20by%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLET(list1%3BA2%3AA100000%3B%0A%20%20%20%20%20list2%3BB2%3AB100000%3B%0AmaxRows%3BMAX(ROWS(list1)%3BROWS(list2))%3B%0Aseq%3BSEQUENCE(maxRows*2%3B%3B0)%3B%0Arow%3BMOD(seq%3BmaxRows)%2B1%3B%0Aloop%3BINT(seq%2FmaxRows)%2B1%3B%0Adimensions%3BCHOOSE(loop%3B%0A%20%20%20INDEX(list1%3Brow%3BSEQUENCE(1%3BCOLUMNS(list1)))%3B%0A%20%20%20INDEX(list2%3Brow%3BSEQUENCE(%3BCOLUMNS(list2))))%3B%0Aoutput%3BUNIQUE(dimensions)%3B%0Aoutput%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BIf%20you%20want%20to%20debug%20the%20LET%20function%2C%20change%20the%20last%20line%20from%20output%20to%20as%20maxRows%20and%20you%20will%20see%20the%20result%20from%20that%20part.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3192341%22%20slang%3D%22en-US%22%3ERe%3A%20Matching%20pair%20of%20values%20in%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3192341%22%20slang%3D%22en-US%22%3EHmm%2C%20very%20interesting.%20Does%20it%20work%20for%20text%20values%20also%3F%20because%2C%20for%20some%20reason%2C%20when%20I%20put%20the%20text%20Products%20codes%20then%20the%20formula%20give%20an%20error%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3192446%22%20slang%3D%22en-US%22%3ERe%3A%20Matching%20pair%20of%20values%20in%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3192446%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1313342%22%20target%3D%22_blank%22%3E%40George_Jokha%3C%2FA%3E%26nbsp%3BSeems%20ok%20with%20version%202201%20(Excel%20365%2C%20jan%202022).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bosinander_1-1645440069934.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F349933i57BED2DBB95FE139%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22bosinander_1-1645440069934.png%22%20alt%3D%22bosinander_1-1645440069934.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIn%20cell%20i3%2C%20you%20may%20want%20to%20add%20sort%20but%20it%20will%20give%20you%20different%20group%20numbers.%20If%20group%20name%20is%20important%20you%20can%20use%20XLOOKUP%20instead%20of%20XMATCH%20(in%20attached%20file)%3C%2FP%3E%3CP%3Eoutput%3BSORT(UNIQUE(dimensions))%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3192453%22%20slang%3D%22en-US%22%3ERe%3A%20Matching%20pair%20of%20values%20in%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3192453%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1159986%22%20target%3D%22_blank%22%3E%40bosinander%3C%2FA%3E%26nbsp%3BThank%20you%20very%20much.%20It%20seems%20to%20be%20good%20dessision.%20Difficult%2C%20but%20good!%20%3A))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3192468%22%20slang%3D%22en-US%22%3ERe%3A%20Matching%20pair%20of%20values%20in%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3192468%22%20slang%3D%22en-US%22%3EBut..%20ooh%2C%20%22x%22%20formulas%20seems%20to%20work%20only%20for%20higher%20version%20of%20the%20windows.%20I%20have%20windows%2010%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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_1Prod_2Group
00214087561
00214142671
00273097832
00278118913
08756002141
09783002732
11891002783
14267002141

 

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
One more commnet: For the same pairs group number can be "Prod_1" & "Prod_2"

@George_Jokha 

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

bosinander_0-1645438282150.png

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.

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

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

 

bosinander_1-1645440069934.png

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

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

But.. ooh, "x" formulas seems to work only for higher version of the windows. I have windows 10
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).

@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

@George_Jokha 

 

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

bosinander_0-1645514534444.png

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.

 

@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_1Prod_2
0021408756
0021414267
0027309783
0027811891
0028409829
0028509829
0036609526
0059212174
0131102149
0131203771
0131212659
0131406377
0133610416
0148810415
0156812309
0156907137
0157007138
0157008624
0157011417
0157111418
0157112162
0158107489
0158107859
0158107869
0158208319
0158307877
0158307883
0159111219
0159310428
0184210714
0184310061
0184311228
0198704509
0198707412
0198711230
0199608318
0214901311
0223411567
0331011235
0335810486
0336508153
0343710885
0358611920
0377101312
0385412567
0386811921
0387712560
0387812562
0387814652
0388012563
0388212526
0388214403
0388308004
0388314486
0388408010
0410812170
0450901987
0479006481
0510211891
0511205113
0511212048
0511305112
0529612327
0565211234
0570509783
0583012807
0583107363
0583910281
0603212168
0617012470
0617014288
0617312806
0637701314
0637714484
0642610997
0647910414
0648104790
0648110417
0658510492
0660910487
0660914297
0713701569
0713801570
0714213485
0714214261
0719912807
0729611920
0733308006
0733408006
0733508006
0733608006
0733712319
0733812319
0734712346
0736305831
0741201987
0744411235
0748901581
0785409829
0785810714
0785901581
0786810714
0786901581
0787110714
0787311234
0787701583
0788301583
0793811237
0794009783
0797609925
0797610033
0797610208
0797611577
0799112161
0799612328
0799712327
0800312561
0800314378
0800403883
0800607333
0800607334
0800607335
0800607336
0800612311
0801003884
0801812560
0802012561
0802014378
0802411375
0802411376
0803211221
0803311224
0815303365
0815312701
0831801996
0831901582
0832011219
0862401570
0875600214
0876109829
0876312470
0876314288
0876712532
0876714379
0884912330
0896212702
0897209910
0898312527
0952600366
0970612327
0977411891
0978300273
0978305705
0978307940
0978311011
0982900284
0982900285
0982907854
0982908761
0991008972
0992412328
0992507976
1000412585
1002312170
1003307976
1006101843
1006211235
1020412174
1020807976
1026712307
1027713050
1028010714
1028105839
1041406479
1041501488
1041601336
1041706481
1041811234
1042801593
1042910962
1047410784
1048603358
1048706609
1049206585
1066811222
1067011221
1067311223
1067511224
1071401842
1071407858
1071407868
1071407871
1071410280
1071412339
1071414327
1071414398
1071414456
1073612560
1078212560
1078410474
1078612561
1078614378
1088503437
1091411373
1091412343
1091414664
1095912406
1096210429
1096311117
1099706426
1099712564
1099712565
1099812562
1099814652
1101109783
1111613485
1111614261
1111710963
1121901591
1121908320
1121912312
1121912341
1122108032
1122110670
1122112866
1122113503
1122114298
1122114406
1122114407
1122210668
1122212865
1122213502
1122214408
1122214409
1122310673
1122312867
1122313504
1122314411
1122314413
1122408033
1122410675
1122412868
1122413505
1122414412
1122512168
1122801843
1123001987
1123405652
1123407873
1123410418
1123503310
1123507444
1123510062
1123707938
1137213485
1137214261
1137310914
1137314263
1137508024
1137608024
1141701570
1141801571
1156702234
1157707976
1189100278
1189105102
1189109774
1192003586
1192007296
1192103868
1204805112
1216107991
1216114286
1216201571
1216806032
1216811225
1217004108
1217010023
1217014252
1217400592
1217410204
1217412345
1230710267
1230714319
1230714490
1230901568
1231108006
1231211219
1231412563
1231907337
1231907338
1232705296
1232707997
1232709706
1232712334
1232712555
1232807996
1232809924
1233008849
1233412327
1233910714
1234111219
1234310914
1234314263
1234512174
1234607347
1240610959
1247006170
1247008763
1247012556
1247012558
1247014393
1247014395
1252603882
1252708983
1253208767
1255512327
1255612470
1255614288
1255812470
1255814288
1256003877
1256008018
1256010736
1256010782
1256014378
1256108003
1256108020
1256110786
1256203878
1256210998
1256303880
1256312314
1256410997
1256510997
1256703854
1258510004
1265901312
1270108153
1270208962
1270214318
1280606173
1280705830
1280707199
1286511222
1286611221
1286711223
1286811224
1305010277
1348507142
1348511116
1348511372
1350211222
1350311221
1350411223
1350511224
1425212170
1426107142
1426111116
1426111372
1426311373
1426312343
1426700214
1428612161
1428806170
1428808763
1428812556
1428812558
1429706609
1429811221
1431812702
1431912307
1432710714
1437808003
1437808020
1437810786
1437812560
1437908767
1439312470
1439512470
1439810714
1440303882
1440611221
1440711221
1440811222
1440911222
1441111223
1441211224
1441311223
1445610714
1448406377
1448603883
1449012307
1465203878
1465210998
1466410914

 

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

bosinander_1-1645519946013.png

 

 

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

Thank you very much! It realy helps! Great!

@George_Jokha You're welcome