Excel Magic Trick 369 with blank cells

[hejsanb]

Hi,

I'm new to the forum so hello everyone! I've watched the Excel Magic Trick 369 video on YouTube and although it has helped me along the way, I'm still having problems adjusting the formula provided to my case. As it stands, my formula looks like this:

This formula is in cell A2

=SUMPRODUCT(--ISNA(MATCH(RANGE1,RANGE2,0)))

This formula is in cell A4

=IF(ROWS($A$4:A4)<=$A$2,INDEX(RANGE1,SMALL(IF(1-ISNUMBER(MATCH(RANGE1,RANGE2,0)),ROW(RANGE1)-ROW('DATA1'!$AR$2)+1),ROWS($A$4:A4))),"")

I'm making a list of values in LIST1 that aren't present in LIST2 (as the tutorial Excel Magic Trick 369 details). As such, for blank values in LIST1 I don't want anything to appear in the new list.

For example, LIST1 contains the values 1, 2, 3, BLANK, 5 and LIST2 contains the values 1, 6, 7, 8, 9. The new list which finds unique values in LIST1 would show 2, 3, 0, 5. I simply don't want that 0 value to appear, ie. it should only show 2, 3, 5 and the formula in cell A2 should only show that 3 values are unique.. I hope that makes sense! I have tried editing the SUMPRODUCT to subtract COUNTBLANK, but that doesn't provide a complete solution.

Does anyone have any idea of how I could solve this? Thank you in advance!

[NBVC]

Try changing the: ROW('DATA1'!$AR$2)+1 part to: MIN(ROW(Range1))+1

and make sure to confirm with CTRL+SHIFT+ENTER and copy down.

[hejsanb]

Try changing the: ROW('DATA1'!$AR$2)+1 part to: MIN(ROW(Range1))+1

and make sure to confirm with CTRL+SHIFT+ENTER and copy down.

Thanks for your help! I tried changing it but no luck - the formula still works as earlier but it's still showing the zero's. Do you know if there's anything else I can try?

[NBVC]

Sorry, I just reread the post and you don't want blanks to show as 0....

Change the formula in A2 to:

=SUMPRODUCT(--ISNA(MATCH(Range1,Range2,0)),--(Range1<>""))

and formula in A4 to:

=IF(ROWS($A$4:A4)<=$A$2,INDEX(Range1,SMALL(IF(1-ISNUMBER(MATCH(Range1,Range2,0)),IF(Range1<>"",ROW(Range1)-MIN(ROW(Range1))+1)),ROWS($A$4:A4))),"")

CSE confirmed

[hejsanb]

That works perfectly, you're a magician! Thank you so much!

[hejsanb]

Sorry to open this thread again but I have another question which regards the same formula. I was wondering if it is possible to remove duplicates from showing up in the uniques list?

For example, if LIST1 contains two values of 1 which aren't in LIST2, the uniques list is showing 1 two times.

Thank you very much in advance!

[NBVC]

Try replacing formula in A4 with:

=IFERROR(INDEX(SMALL(IF(1-ISNUMBER(MATCH(range1,range2,0)),IF(range1<>"",range1)),ROW(INDIRECT("1:"&$A$2))),MATCH(1,IF(SMALL(IF(1-ISNUMBER(MATCH(range1,range2,0)),IF(range1<>"",range1)),ROW(INDIRECT("1:"&$A$2)))<>"",IF(ISNA(MATCH(SMALL(IF(1-ISNUMBER(MATCH(range1,range2,0)),IF(range1<>"",range1)),ROW(INDIRECT("1:"&$A$2))),A$3:A3,0)),1)),0)),"")

confirmed with CTRL+SHIFT+ENTER and copied down.

[hejsanb]

Try replacing formula in A4 with:

=IFERROR(INDEX(SMALL(IF(1-ISNUMBER(MATCH(range1,range2,0)),IF(range1<>"",range1)),ROW(INDIRECT("1:"&$A$2))),MATCH(1,IF(SMALL(IF(1-ISNUMBER(MATCH(range1,range2,0)),IF(range1<>"",range1)),ROW(INDIRECT("1:"&$A$2)))<>"",IF(ISNA(MATCH(SMALL(IF(1-ISNUMBER(MATCH(range1,range2,0)),IF(range1<>"",range1)),ROW(INDIRECT("1:"&$A$2))),A$3:A3,0)),1)),0)),"")

confirmed with CTRL+SHIFT+ENTER and copied down.

Wow, that has got to be the biggest formula I've ever seen! Thank you!

At the moment it's only recognizing numerical input in the lists - is it possible to make it recognize alphabetic input as well as it did before?

Also, the formula in A2 doesn't reflect the update in this formula, ie. it shows the total number of values, disregarding if they are unique or not. Do you know how to make it do so?

Thanks again, I wish I was as good as you with this!

[NBVC]

Perhaps it would be easier to use the original setup first, then add another formula to get your unique values...

So if you have the original formulas in A2 and A4 copied down.. then assuming the A4 copied down range is Range3, then add in say B4 this formula:

INDEX($A$2446:$A$2464,MATCH(1,IF($A$2446:$A$2464<>"",IF(ISNA(MATCH($A$2446:$A$2464,C$2445:C2445,0) ),1)),0))))

Confirmed with CTRL+SHIFT+ENTER and copied down to get final unique list.

[hejsanb]

Perhaps it would be easier to use the original setup first, then add another formula to get your unique values...

So if you have the original formulas in A2 and A4 copied down.. then assuming the A4 copied down range is Range3, then add in say B4 this formula:

INDEX($A$2446:$A$2464,MATCH(1,IF($A$2446:$A$2464<>"",IF(ISNA(MATCH($A$2446:$A$2464,C$2445:C2445,0) ),1)),0))))

Confirmed with CTRL+SHIFT+ENTER and copied down to get final unique list.

That's a viable solution, but I'm worried that it will take a lot longer to calculate. These formulas are going to be used on tens of thousands of rows and it already takes ages to run through. Can you think of any quicker solution?

[NBVC]

If you want to avoid array formulas, then let's add a helper column beside Range1 to identify matches.

So say Range1, starts in I3, then say in H3, enter formula:

=IF(I3="","",IF(AND(COUNTIF(I$3:I3,I3)=1,ISNA(MATCH(I3,Range2,0))),COUNT($H$2:H2)+1,""))

copied down

Then to extract the unique mismatches..

=IFERROR(INDEX(Range1,MATCH(ROWS($A$1:$A1),Range3,0)),"")

where Range3 is your helper column range in H...

[hejsanb]

Ok that seems like a good solution! I've been doing some research and it seems I could do this a lot easier (and using less memory) with Excels built-in AdvancedFilter function by just recording a macro. Do you have any thoughts on this?

I've done some testing and I can't get it to work though. For example, if RANGE1 contains 16, 3 and 5 and RANGE2 contains 6, 6, and 5, the list of uniques in RANGE1 will return 16, 3 and 5. I'm not sure why the 5 turns up there... I'm guessing using VBA would make all of this easier. Do you have any ideas about what that code would look like?

[hejsanb]

I found this VBA code which does the trick perfectly!

Sub Test()
Dim ListA As Range
Dim ListB As Range
Dim c As Range
Set ListA = Range("RANGE1")
Set ListB = Range("RANGE2")
Range("C1").Value = "Values in A that are NOT in B"
Range("D1").Value = "Values in B that are Not in A"
Range("E1").Value = "Count of A"
Range("F1").Value = "Count of B"
For Each c In ListA
If c.Value <> "" Then
    Range("E2").Value = Range("E2").Value + 1
    If Application.CountIf(ListB, c) = 0 Then
        Cells(Cells(Rows.count, "C").End(xlUp).Row + 1, "C").Value = c
    End If
End If
Next c
For Each c In ListB
If c.Value <> "" Then
    Range("F2").Value = Range("F2").Value + 1
    If Application.CountIf(ListA, c) = 0 Then
        Cells(Cells(Rows.count, "D").End(xlUp).Row + 1, "D").Value = c
    End If
End If
Next c
End Sub

Only problem is that it doesn't account for unique values either... For example, if RANGE1 is 1, 1, 3 and RANGE2 is 3, 4, 5 it will spit out 1 and 1, instead of only 1. Do you by any chance know how I could change this code to do that?

Also, I've tried changing lines 7-10 to this, but it keeps giving me an error:

Range("A1").Value = "Values in A that are NOT in B"
Range("B1").Value = "Values in B that are Not in A"
Range("C1").Value = "Count of A"
Range("D1").Value = "Count of B"

I've changed a number of things lower down but it still doesn't work, so I'm guessing its some VBA syntax that I'm not aware of.

Finally, would it be possible to remove the previous data (below the headers) every time that the macro is run? Otherwise it adds data under the previous data and just continues to grow.

Thanks for your continued support!

[NBVC]

Once you go into VBA, I disappear... I am not a VBA expert... I only offer formula-based solutions for the time being.

[hejsanb]

Once you go into VBA, I disappear... I am not a VBA expert... I only offer formula-based solutions for the time being.

Ok, thank you very much for your support so far then, I really appreciate it!

If anyone ideas as to how the script I provided can be improved to fit my needs please let me know.

Thanks again!