Need help with a logic to flag duplicates

[SKV]

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.

 

[MickG]

Try this for Data in Columns "A & B".

[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

 

[Joe4]

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.

 

[MickG]

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

 

[Joe4]

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.

 

[SKV]

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

 

[Joe4]

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:

ID	Sno	ColNum	ColValue
1	1	1	A
2	1	2	B
3	2	1	A
4	2	2	C
5	3	1	B
6	3	2	A
7	4	1	C
8	4	2	A

<tbody>
</tbody>

Then you can create a query off of this. Here is the SQL code of the query:

SELECT Table1.Sno, Min(Table1.ColValue) AS MinOfColValue, Max(Table1.ColValue) AS MaxOfColValue
FROM Table1
GROUP BY Table1.Sno;

and here are the results:

Sno	MinOfColValue	MaxOfColValue
1	A	B
2	A	C
3	A	B
4	A	C

<tbody>
</tbody>

Then, you can do a query of that query to weed out the duplicates, i.e.

SELECT Query1.MinOfColValue, Query1.MaxOfColValue
FROM Query1
GROUP BY Query1.MinOfColValue, Query1.MaxOfColValue;

Results:

MinOfColValue	MaxOfColValue
A	B
A	C

<tbody>
</tbody>

 

[Joe4]

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:

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.

 

[xenou]

Also came up with this:

1) assume a temp table has been created (to ease the processing in Access):

create table XTMP001 (SnoNum, FirstCol, SecondCol) -- this is pseudocode, not real code

2) Clear the temp table before each run:

delete XTMP001.* from XTMP001

3) Put the two column in order so they can be compared:

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):

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.

 

[SKV]

Thanks all.....always appreciate your help and guidence