Forum Discussion
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,
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.
13 Replies
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.
- 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).
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));
- One more commnet: For the same pairs group number can be "Prod_1" & "Prod_2"
