Forum Discussion
Immortalis
Feb 05, 2024Copper Contributor
Compare arrays, find value that doesn't exist.
I have an array with 3 columns and another array with 4 columns. I'm trying to find a formula that will compare the 3 values in a row in the 1st array, compare it to three values in a row in the ...
- Feb 05, 2024
Immortalis
it worked for me. I've just slightly revised the formula.=INDEX(H2:H4, MATCH(TRUE, MMULT(--(A2:C4<>E2:G4), TRANSPOSE(COLUMN(A2:C4)^0))>0, 0))
note: this formula will only return the first mismatched from your arrays. and if you have multiple mismatches, it will need a SMALL function with ROW function as the second argument to the Nth smallest value from your arrays row numbers where there is a mismatch.
formula if multiple mismatches:=IFERROR(INDEX($H$2:$H$4, SMALL(IF(MMULT(--($A$2:$C$4<>$E$2:$G$4), TRANSPOSE(COLUMN($A$2:$C$4)^0))>0, ROW($A$2:$A$4)-ROW($A$2)+1), ROW(A1))), "")
after pasting that formula (with Ctrl+Shift+Enter) example in cell B9, then copy that cell and paste it in cell B10 and B11
Immortalis
Feb 05, 2024Copper Contributor
Yes, I did that.
Rodrigo_
Feb 05, 2024Steel Contributor
Immortalis
it worked for me. I've just slightly revised the formula.
=INDEX(H2:H4, MATCH(TRUE, MMULT(--(A2:C4<>E2:G4), TRANSPOSE(COLUMN(A2:C4)^0))>0, 0))
note: this formula will only return the first mismatched from your arrays. and if you have multiple mismatches, it will need a SMALL function with ROW function as the second argument to the Nth smallest value from your arrays row numbers where there is a mismatch.
formula if multiple mismatches:
=IFERROR(INDEX($H$2:$H$4, SMALL(IF(MMULT(--($A$2:$C$4<>$E$2:$G$4), TRANSPOSE(COLUMN($A$2:$C$4)^0))>0, ROW($A$2:$A$4)-ROW($A$2)+1), ROW(A1))), "")
after pasting that formula (with Ctrl+Shift+Enter) example in cell B9, then copy that cell and paste it in cell B10 and B11
- ImmortalisFeb 06, 2024Copper Contributor
I had to make some minor changes but YES! That works!!
Thank you SOOOOOO much! You're truly amazing!!! 🙂
- Rodrigo_Feb 06, 2024Steel ContributorI'm glad that we finished this. 😄
no problem mate.- ImmortalisFeb 06, 2024Copper Contributor
I've run into another issue with the formula.
IFERROR(INDEX(BaseInfo!$W$15:$W$38, SMALL(IF((MMULT(--(AO170:AQ170<>BaseInfo!$U$15:BaseInfo!$W$38),TRANSPOSE(COLUMN(AO170:AQ170)^0))>0)*(BaseInfo!$U$15:BaseInfo!$U$38=_SectionCalc), ROW(BaseInfo!$U$15:BaseInfo!$U$38)-ROW(BaseInfo!$U$15)+1), ROW(A1))),"")
It works perfectly fine except when I actually go to put it in my workbook, I'm putting it in cell AQ170 and it returns a circular reference error.
For the AQ column I'm looking up data that matches the criteria and ten putting it in the AQ column after checking the column to make sure it hasn't already been put in. So it's not there then goes to put it in but then it is there so it can't use it and .... paradox. Is there any way to get around that?
- ImmortalisFeb 05, 2024Copper ContributorStill doesn't work for me. In my workbook it comes up with #VALUE!.
In another workbook I'm using to test things, it comes up #N/A.
Could it be because I'm using 2010?- ImmortalisFeb 05, 2024Copper ContributorI recreated your example and it worked so somethings going wrong converting to use in my workbook. I'll play with and see what I do. Thank you very much though! It gives me a valid starting point!
- ImmortalisFeb 05, 2024Copper Contributor
I converted it over and managed to get is to work,
INDEX(AQ137:AQ164, MATCH(TRUE, MMULT(--(BaseInfo!$U$15:BaseInfo!$W$114<>AO137:AQ137), TRANSPOSE(COLUMN(BaseInfo!$U$15:BaseInfo!$W$114)^0))>0, 0))
Thank you VERY much! Would you be able to add something to it though? When it finds the line that doesn't exist, I need it to check that line and make sure the cell in the "U" column is equal to _SectionCalc.
If you could do that, then my issue is completely solved!