Compare two sets of columns and eliminate exact mactches

M

MrGeeky

New Member
Joined
Oct 29, 2018
Messages
6
Hi All

I am a newbie here :warning: so please bare with me. I am trying to recon two sets of data both of which has two columns of info.

I need excel/access to find the exact matches of the two sets of columns and to remove them leaving me with unique info for both the data sets as seen below. Data Below:

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD]Data Set 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Data Set 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2500[/TD]
[TD]HAR123[/TD]
[TD][/TD]
[TD]2500[/TD]
[TD]HAR123[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]HAR124[/TD]
[TD][/TD]
[TD]1501[/TD]
[TD]HAR125[/TD]
[/TR]
[TR]
[TD]1500[/TD]
[TD]HAR125[/TD]
[TD][/TD]
[TD]500[/TD]
[TD]HAR127[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]HAR126[/TD]
[TD][/TD]
[TD]2000[/TD]
[TD]HAR124[/TD]
[/TR]
[TR]
[TD]500[/TD]
[TD]HAR127[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

After Excel/Access formula or code run (Expected Outcome):

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD]Data Set 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Date Set 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1501[/TD]
[TD]HAR125[/TD]
[/TR]
[TR]
[TD]1500[/TD]
[TD]HAR125[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]HAR126[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Your assistance will be highly appreciated. :)
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
Hi. You could do this simply with a couple of COUNTIFS formulas. I copied and pasted your table into A1. Then in C2 placed:

=COUNTIFS(D:D,A2,E:E,B2)

and in F2:

=COUNTIFS(A:A,D2,B:B,E2)

You just then need to copy and paste special values over the formulas and clear/ delete any cells that dont have 0 next to them.
 
Upvote 0
Assuming Data in columns "A & B" and "D & "E" then, try this:-

Code: 
[COLOR="Navy"]Sub[/COLOR] MG29Oct37
[COLOR="Navy"]Dim[/COLOR] Rng1 [COLOR="Navy"]As[/COLOR] Range, Rng2 [COLOR="Navy"]As[/COLOR] Range, Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng1 = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Rng2 = Range("D2", Range("D" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Rng = Union(Rng1, Rng2)

[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare


[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Txt = Dn.Value & Dn.Offset(, 1).Value
        [COLOR="Navy"]If[/COLOR] Not .Exists(Txt) [COLOR="Navy"]Then[/COLOR]
             .Add Txt, Dn
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] nRng = Union(.Item(Txt), Dn)
            [COLOR="Navy"]Else[/COLOR]
                [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, .Item(Txt), Dn)
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
  nRng.ClearContents
  Rng.SpecialCells(xlCellTypeBlanks).Offset(, 1).ClearContents
[COLOR="Navy"]End[/COLOR] If

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks Steve much appreciated but Mick's seems to be more of what I'm looking for.

Hopefully some tweaking maybe might help achieve the exact code I need.

Mick your code also removes duplicate data (two rows from one set) and this causes my recon not to balance.

So ideally if there is an exact match of data between two rows from both data sets then this should be cleared out. But now I have two rows in one set that are duplicates and one row in another set, and when you the code clears contents, it clears all 3 rows and this is the problem.

Example:

Data Set 1:

1300;HAR1314
1300;HAR1314
1200;HAR1315

Data Set 2:

1300;HAR1314
1200;HAR1315

Current code removes all data in the above example, but what I need the outcome to look like is:

Data set 1:

1300;HAR1314

Data set 2:

"Blank"

Your code should ensure the rule applies on both sets of data where there is a duplicate.

Is this possible?
 
Upvote 0
What should happen if there are say 2 or more items that are the same, in one column only. ???
 
Upvote 0
Basically nothing unless there's a match in the other column then one must be cleared. Basically items should be cleared per match and not clearing multiple.

So if there are 5 items with the same value in column A and 4 items in column D with the same as in column A, then 4 should be cleared on both sides leaving one in column A.
 
Upvote 0
Just to clearly answer you question. If there multiple items with the same value in one column only, then nothing should happen and those values should remain. (So multiple values that are the same in one column will remain uncleared).

Your code is to reconcile exact matches across two columns and clear the items per one on one match as it is currently working.

Hope this helps.
 
Upvote 0
Give this a try !!!
Code: 
[COLOR="Navy"]Sub[/COLOR] MG29Oct29
[COLOR="Navy"]Dim[/COLOR] Rng1 [COLOR="Navy"]As[/COLOR] Range, Rng2 [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Ray [COLOR="Navy"]As[/COLOR] Variant, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, Q [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] S1 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] S2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng1 = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Rng2 = Range("D2", Range("D" & Rows.Count).End(xlUp))
Ray = Array(Rng1, Rng2)

[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] Ac = 0 To 1
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Ray(Ac)
        S1 = "": S2 = ""
            Txt = Dn.Value & Dn.Offset(, 1).Value
                [COLOR="Navy"]If[/COLOR] Not .exists(Txt) [COLOR="Navy"]Then[/COLOR]
                    [COLOR="Navy"]If[/COLOR] Ac = 0 [COLOR="Navy"]Then[/COLOR]
                        S1 = Dn.Address
                    [COLOR="Navy"]Else[/COLOR]
                         S2 = Dn.Address
                    [COLOR="Navy"]End[/COLOR] If
                    .Add Txt, Array(S1, S2)
                [COLOR="Navy"]Else[/COLOR]
                    Q = .Item(Txt)
                        [COLOR="Navy"]If[/COLOR] Ac = 0 [COLOR="Navy"]Then[/COLOR]
                            Q(0) = Q(0) & IIf(Q(0) = "", Dn.Address, "," & Dn.Address)
                        [COLOR="Navy"]Else[/COLOR]
                            Q(1) = Q(1) & IIf(Q(1) = "", Dn.Address, "," & Dn.Address)
                        [COLOR="Navy"]End[/COLOR] If
                    .Item(Txt) = Q
                [COLOR="Navy"]End[/COLOR] If
   [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Dim[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, Sp1 [COLOR="Navy"]As[/COLOR] Variant, oMin [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    Sp = Split(.Item(K)(0), ",")
    Sp1 = Split(.Item(K)(1), ",")
    oMin = Application.Min(UBound(Sp), UBound(Sp1))
    [COLOR="Navy"]For[/COLOR] n = 0 To oMin
        [COLOR="Navy"]If[/COLOR] Not Sp(n) = vbNullString And Not Sp1(n) = vbNullString [COLOR="Navy"]Then[/COLOR]
            Range(Sp(n)).Resize(, 2).ClearContents
            Range(Sp1(n)).Resize(, 2).ClearContents
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks Mike. This works perfect for what I am needing it to do.

If there is any additional tweaks needed how can I contact you?

Regards,
Wayne
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Compare data in three sets of columns, highlight non matches
Replies
9
Views
141
Eric W
Eric W
D
  • Question Question
Trying to create a results sheet using two data ranges
Replies
0
Views
321
Doot
D
A
  • Solved
Compare two sheets based on lastrow with highlighted cells
2 3
Replies
26
Views
257
mumps
mumps
C
  • Solved
Index Match Multiple Columns - Exact and Approximate
Replies
4
Views
1K
Fluff
Fluff
R
  • Question Question
Identifying the difference between two data sets
Replies
1
Views
174
igold
igold

Forum statistics

Threads
1,223,905
Messages
6,175,297
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top