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