Macro or formula to find both unique and un-paired values between two columns

[Lrmux]

I'm trying to make balancing my checkbook easier, but finding it hard to find a macro or formula that does what I need.

I have two columns, one that has the charges according to my bank, and the other which has charges that I've documented myself. It's easy to find values that occur only in the first column or only in the second column. Where I'm getting stuck is finding charges that have the same value in both columns but their frequency is not in agreement, like when I record 1 of my $1.10 charges in but in reality my bank shows I had 3. Would like an easy way to see that I omitted 2 $1.10 charges.

So here would be a sample input:
[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl66, width: 64"]Input 1[/TD]
[TD="class: xl66, width: 64"]Input 2[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]1.10[/TD]
[TD="class: xl67, align: right"]1.10[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]1.10[/TD]
[TD="class: xl67, align: right"]2.20[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]1.10[/TD]
[TD="class: xl67, align: right"]5.50[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]2.20[/TD]
[TD="class: xl67, align: right"]6.60[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]3.30[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]5.50[/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]


And this would be what I'm looking for:
[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Out 1[/TD]
[TD="class: xl65, width: 64"]Out 2[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1.10[/TD]
[TD="class: xl65, align: right"]6.60[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1.10[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3.30[/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Akuini]

Try this:
The code will delete duplicate items that exist in both column.
Copy the data to another sheet, run the code, then you need to sort the data manually.

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1107980a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1107980-macro-formula-find-both-unique-un-paired-values-between-two-columns.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] a [COLOR=Royalblue]As[/COLOR] Range, b [COLOR=Royalblue]As[/COLOR] Range, c [COLOR=Royalblue]As[/COLOR] Range
[COLOR=Royalblue]Dim[/COLOR] res
[COLOR=Royalblue]Set[/COLOR] a = Range([COLOR=brown]"A2"[/COLOR], Cells(Rows.count, [COLOR=brown]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp))
[COLOR=Royalblue]Set[/COLOR] b = Range([COLOR=brown]"B1"[/COLOR], Cells(Rows.count, [COLOR=brown]"B"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp))

    [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] c [COLOR=Royalblue]In[/COLOR] a
    res = Application.Match(c, b, [COLOR=Royalblue]False[/COLOR])
    [COLOR=Royalblue]If[/COLOR] IsNumeric(res) [COLOR=Royalblue]Then[/COLOR]
        c.ClearContents
        Cells(res, [COLOR=brown]"B"[/COLOR]).ClearContents
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    [COLOR=Royalblue]Next[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[Lrmux]

Awesome! This will save me so much time. Thank you!

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.

To make it faster it's better to turn off 'Application.ScreenUpdating'. So use this one instead:

Sub a1107980a()
'https://www.mrexcel.com/forum/excel-questions/1107980-macro-formula-find-both-unique-un-paired-values-between-two-columns.html
Dim a As Range, b As Range, c As Range
Dim res
Set a = Range("A2", Cells(Rows.count, "A").End(xlUp))
Set b = Range("B1", Cells(Rows.count, "B").End(xlUp))
[COLOR=#0000ff]Application.ScreenUpdating = False[/COLOR]
    For Each c In a
    res = Application.Match(c, b, False)
    If IsNumeric(res) Then
        c.ClearContents
        Cells(res, "B").ClearContents
    End If
    Next
[COLOR=#0000ff]Application.ScreenUpdating = True[/COLOR]
End Sub

 

[Lrmux]

Perfect. This macro was the last piece of a bunch of other macros for automating my checkbook. Turned off Application.ScreenUpdating at the very beginning and back on right after your macro and it makes a nice difference. Thanks!

 

[Akuini]

I'm glad you found it useful.

 

[Peter_SSs]

And this would be what I'm looking for:
[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Out 1[/TD]
[TD="class: xl65, width: 64"]Out 2[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1.10[/TD]
[TD="class: xl65, align: right"]6.60[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1.10[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3.30[/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]

Would adding this near the end of Akuini's code better give that, or are you finding the blanks created more useful?

        Cells(res, "B").ClearContents
    End If
    Next
    On Error Resume Next
    Columns("A:B").SpecialCells(xlBlanks).Delete Shift:=xlUp
    On Error GoTo 0
Application.ScreenUpdating = True
End Sub

 

[mikerickson]

Conditional formatting will do what you want. Select A1 and set this CF condition. Copy the CF to where you need it.

=(COUNTIF($A:$A, A1)<>COUNTIF($B:$B, A1))

 

[Lrmux]

Would adding this near the end of Akuini's code better give that, or are you finding the blanks created more useful?

The blanks are actually kind of nice so I've left them. I have the full payment details in a table to the left, and the spaces keep things more in line so I have to scroll less. But I'll add your code and comment it out in case I change things around and need it later. Thanks!