Feb 21 2022 01:46 AM
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,
Feb 21 2022 02:16 AM
Feb 21 2022 02:19 AM
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.
Feb 21 2022 02:34 AM
Feb 21 2022 02:59 AM
@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));
Feb 21 2022 03:01 AM
@bosinander Thank you very much. It seems to be good dessision. Difficult, but good! :))
Feb 21 2022 03:03 AM
Feb 21 2022 04:15 AM
Feb 21 2022 11:18 PM
@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.
Feb 21 2022 11:27 PM
Solution
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.
Feb 22 2022 12:01 AM
@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 |
Feb 22 2022 06:31 AM
@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).
Feb 23 2022 10:05 AM
Mar 10 2022 10:03 PM - edited Mar 10 2022 10:04 PM
@George_Jokha You're welcome :):)
Feb 21 2022 11:27 PM
Solution
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.