Need help with a logic to flag duplicates

SKV

Active Member
Joined
Jan 7, 2009
Messages
257
I have a data with various combinations (Sample illustration below) where values in col1 and col2 reverses and therefore reports. I am looking for a way flag the reversed duplicates (A-->B and B-->A are same to I just want to keep one record) and ultimately delete the duplicates (#3 and #4 ).

Please advise on a good logic (may be a VBA code to solve this problem for me).

Sno Col1 Col2
1 A B
2 A C
3 B A
4 C A


Many thanks.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Try this for Data in Columns "A & B".
Code:
[COLOR="Navy"]Sub[/COLOR] MG30Aug18
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range, K [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Txt1 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Txt2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        Txt1 = Dn.Value & " " & Dn.Offset(, 1).Value
        Txt2 = Dn.Offset(, 1).Value & " " & Dn.Value
        [COLOR="Navy"]If[/COLOR] Not .exists(Txt1) And Not .exists(Txt2) [COLOR="Navy"]Then[/COLOR]
            .Add Txt1, Dn
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
[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]
            [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Dn Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Is this an Access or Excel question?
Mick supplied an Excel solution, but you posted the question in the "Microsoft Access" forum.
If it is an Excel question, let me know, and I will move the question to the proper forum.
 
Upvote 0
Hi Joe4, I was wondering why I could not see it in the Excel Threads. I got the thread from the "Zero replies", did not see the "Access" Bit
Thanks for your intervention.
Mick
 
Upvote 0
I got the thread from the "Zero replies", did not see the "Access" Bit
It happens a bit. People don't always notice that the forum is listed all the way over to the right on that "Zero Reply Posts" list.
I admit, it happened to me too once when I first started using that list.
 
Upvote 0
Is this an Access or Excel question?
Mick supplied an Excel solution, but you posted the question in the "Microsoft Access" forum.
If it is an Excel question, let me know, and I will move the question to the proper forum.

YES, this is ACCESS Task.....Can someone guide me with the VBA code?

Thanks
Sachin
 
Upvote 0
Do you have any ability to change the structure of your table? That is not a normalized structure, which makes it a bit harder to work with.

A normalized structure would look like:
111A
212B
321A
422C
531B
632A
741C
842A

<tbody>
[TH="bgcolor: #c0c0c0"] ID [/TH]
[TH="bgcolor: #c0c0c0"] Sno [/TH]
[TH="bgcolor: #c0c0c0"] ColNum [/TH]
[TH="bgcolor: #c0c0c0"] ColValue [/TH]

</tbody>

Then you can create a query off of this. Here is the SQL code of the query:
Code:
SELECT Table1.Sno, Min(Table1.ColValue) AS MinOfColValue, Max(Table1.ColValue) AS MaxOfColValue
FROM Table1
GROUP BY Table1.Sno;
and here are the results:
1AB
2AC
3AB
4AC

<tbody>
[TH="bgcolor: #c0c0c0"] Sno [/TH]
[TH="bgcolor: #c0c0c0"] MinOfColValue [/TH]
[TH="bgcolor: #c0c0c0"] MaxOfColValue [/TH]

</tbody>

Then, you can do a query of that query to weed out the duplicates, i.e.
Code:
SELECT Query1.MinOfColValue, Query1.MaxOfColValue
FROM Query1
GROUP BY Query1.MinOfColValue, Query1.MaxOfColValue;
Results:
AB
AC

<tbody>
[TH="bgcolor: #c0c0c0"] MinOfColValue [/TH]
[TH="bgcolor: #c0c0c0"] MaxOfColValue [/TH]

</tbody>
 
Upvote 0
Note. If you are unable to rearrange the table structure, you can use a query to initially get it in the Format I showed like this:
Code:
SELECT Sno, 1 as ColNum, Col1 as ColValue
FROM Table1
UNION
SELECT Sno, 2 as ColNum, Col2 as ColValue
FROM Table1;
Then, apply the other two queries to the this query, as described in the previous post.
 
Upvote 0
Also came up with this:

1) assume a temp table has been created (to ease the processing in Access):
Code:
create table XTMP001 (SnoNum, FirstCol, SecondCol) -- this is pseudocode, not real code

2) Clear the temp table before each run:
Code:
delete XTMP001.* from XTMP001

3) Put the two column in order so they can be compared:
Code:
insert into XTMP001 (SnoNum, FirstCol, SecondCol)
select 
	Sno as SnoNum, 
	iif(Col1 <= Col2, Col1, Col2) as FirstCol, 
	iif(Col1 <= Col2, Col2, Col1) as SecondCol
from
	Table5

4) Query for a distinct set (find the lowest Sno for each distinct pair of columns):
Code:
select 
	min(SnoNum) as Sno, 
	FirstCol as Col1, 
	SecondCol as Col2 
from XTMP001
group by
	FirstCol, SecondCol

If desired, the original table can be cleared out and the values inserted back in with duplicates removed (always backup your data before major changes like this).

This will not handle nulls and assumes that the data values in the two columns can be compared with normal greater than, less than, and equality rules.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

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