Reaching-Out to VBA Experts ( Identify Duplicates across 2 Sheets and Retun "X" or "Y" ) Please Help

[Please_H]

Dear All,

I hope everyone of you are indoors, safe and sound.

Point 01 - What I have :

wip Excel Sheet.xlsm
	A	B	C	D
1	Dups (X/Y)	Bank Cheque No.	Value (Dr)	Value (Cr)
2		986001	500.00	-
3		986002	1,000.00	-
4		986003	1,500.00	-
5		ABC	-	300.00
6		DEF	-	3,000.00
7		GHI	-	4,500.00
Sheet1

wip Excel Sheet.xlsm
	A	B	C	D
1	Dups (X/Y)	Bank Cheque No.	Value (Dr)	Value (Cr)
2		986010	-	700.00
3		986001	-	500.00
4		986020	-	950.00
5		QRS	8,500.00	-
6		ABC	300.00	-
7		DEF	3,000.00	-
Sheet2

Point 02 - What I Need :

wip Excel Sheet.xlsm
	A	B	C	D
1	Dups (X/Y)	Bank Cheque No.	Value (Dr)	Value (Cr)
2	X	986001	500.00	-
3		986002	1,000.00	-
4		986003	1,500.00	-
5	Y	ABC	-	300.00
6	Y	DEF	-	3,000.00
7		GHI	-	4,500.00
Sheet1

wip Excel Sheet.xlsm
	A	B	C	D
1	Dups (X/Y)	Bank Cheque No.	Value (Dr)	Value (Cr)
2		986010	-	700.00
3	X	986001	-	500.00
4		986020	-	950.00
5		QRS	8,500.00	-
6	Y	ABC	300.00	-
7	Y	DEF	3,000.00	-
Sheet2

Point 03 - What am I trying to do here :

I am trying to find Duplicates across 2 sheets and get a "X" and "Y" in return for what I am looking for :

Point 04 - How do I want to Return "X" ( to understand ; check Green Highlights ) - and Note "X/Y" needs to get returned in both Sheets, so that If I put a SUMIF to them the Value's match.

04.1 - Sheet 1/ Col 'B' & Col 'C' ( vs ) Sheet 2/ Col 'B' & Col 'D'

Point 04 - How do I want to Return "Y" ( to understand ; check Green Highlights ) - and Note "X/Y" needs to get returned in both Sheets, so that If I put a SUMIF to them the Value's match.

04.2 - Sheet 1/ Col 'D' ( vs ) Sheet 2/ Col 'C'


One of the Well-known Members in here, "JLGWhiz" wrote the below coding for me to find duplicates between 2 Sheets.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
    With sh1
        For Each c In .Range("B2", .Cells(Rows.Count, 2).End(xlUp))
            Set fn = sh2.Range("B:B").Find(c.Value, , xlValues, xlWhole)
                If Not fn Is Nothing Then
                    adr = fn.Address
                    Do
                        If c.Offset(, 1).Value = fn.Offset(, 2).Value Then
                            c.Offset(, -1) = "X"
                            fn.Offset(, -1) = "X"
                            Exit Do
                        End If
                        Set fn = sh2.Range("B:B").FindNext(fn)
                    Loop While fn.Address <> adr
                End If
        Next
        For Each c In .Range("D2", .Cells(Rows.Count, 4).End(xlUp))
            If c.Value > 0 And c.Offset(, -1).Value <> "X" Then
                Set fn = sh2.Range("C:C").Find(c.Value, , xlValues, xlPart)
                    If Not fn Is Nothing Then
                        If Len(c) = Len(fn) Then
                            c.Offset(, -3) = "Y"
                            fn.Offset(, -2) = "Y"
                        End If
                    End If
            End If
        Next
    End With
End Sub

Point 05 - What is the issue so far ;

05.1 - For now I have found the Macro is detecting Zero's (Cells are on Accounting format) and it identifies Zero's as Duplicates as well.
05.2 - In some instances I found "X/Y" appears only in one sheet despite an entry having been identified as a Duplicate.

I am no expert of VBA.

Please help me out.

Thank you.

 

[Anthony47]

I am no expert of VBA

So try in Sheet1 A2 this formula
=IF((COUNTIFS(Sheet2!$B$1:$B$1000,B2,Sheet2!$C$1:$C$1000,C2)+COUNTIFS(Sheet2!$B$1:$B$1000,B2,Sheet2!$D$1:$D$1000,D2))>0,"Y","")

Then copy down

In Sheet2 you will use the same formula, but replacing Sheet2 with Sheet1

Bye

 

[Please_H]

So try in Sheet1 A2 this formula
=IF((COUNTIFS(Sheet2!$B$1:$B$1000,B2,Sheet2!$C$1:$C$1000,C2)+COUNTIFS(Sheet2!$B$1:$B$1000,B2,Sheet2!$D$1:$D$1000,D2))>0,"Y","")

Then copy down

In Sheet2 you will use the same formula, but replacing Sheet2 with Sheet1

Bye

Thanks a lot for your Reply Brother,

The reason I am looking for a VBA is due to the fact I'd be deleting the info related to whatever was identified as X and Y (cz they are duplicates).
Therefore if I use a formula I'd have to keep copy pasting formulas.

 

[Anthony47]

I'd suggest that you don't delete information based on macros that you don't control…
I should rather go with autorecorded macro, to automatize small portion of the task and you check the result before the next portion.

If you, anyway, wish to procede with the macro then I should suggest that you share a sample workbook to work on

Bye

 

[Please_H]

I did try the auto recording to achieve what I am trying to do, but the result was a mess...
What I am trying in here in accounting terms is known as Reconciliation...
Bank info will be going to the Sheet 1
Our Recorded info goes to Sheet 2
Then I will be applying the Macro,
"X" captures stuff via the Cheque Numbers
"Y" captures details via Values only.

Then I leave the result as it is in a different sheet and move the info to a different place again via another Macro and Delete all the Captured info via another Macro. ( So I have a back up of duplicates captured )
Then finally I move the info via another Macro to the worksheet we submit to our Department Heads.
This saves a lot of time.

Plus,
The workbook I will be applying the above have filters. So through them I will try to identify if Duplicates are been captured correctly before I Delete,
Top of that Sumif's would be used to see if "X" and "Y" Captured Total Amounts are Matching between sheets...
So the protocols for safety are in place...

Thanks lot again for your advise bro, Appreciate it man...

Link as requested,
.
Sample Sheet

 

[Please_H]

I'd suggest that you don't delete information based on macros that you don't control…
I should rather go with autorecorded macro, to automatize small portion of the task and you check the result before the next portion.

If you, anyway, wish to procede with the macro then I should suggest that you share a sample workbook to work on

Bye

Thinking further on your advise also makes me realize that what I need can be achieved taking your equation as a Helper column... But would take more work to be done on my existing sheet at work.
Nevertheless I will see if something can be done until a Macro comes in.

 

[Please_H]

So try in Sheet1 A2 this formula
=IF((COUNTIFS(Sheet2!$B$1:$B$1000,B2,Sheet2!$C$1:$C$1000,C2)+COUNTIFS(Sheet2!$B$1:$B$1000,B2,Sheet2!$D$1:$D$1000,D2))>0,"Y","")

Then copy down

In Sheet2 you will use the same formula, but replacing Sheet2 with Sheet1

Bye

There are 4 Sheet references in your formula, I did change all of them for the sheet2 but nothing happened...

 

[Anthony47]

There are 4 Sheet references in your formula, I did change all of them for the sheet2 but nothing happened...

If "nothing happens" then you need to reinstall Windows and reinstall Office

Also, you should have noticed that I didn't realize you need to insert X or Y (my formula returns only X)
Anyway, in terms of macro my best guess is this:

Sub DuplXY()
Dim tOne As Range, tTwo As Range, T1CR As Range, T2CR As Range, I As Long, J As Long
Dim Last1 As Long, Last2 As Long, C1X As Long, C2X As Long
'
Set tOne = Sheets("Sheet1").Range("A1")     '<<< Start of first table
Set tTwo = Sheets("Sheet2").Range("A1")     '<<< Start of second table
'
For J = 1 To 2
    If J = 1 Then
        Set T1CR = tOne.CurrentRegion
        Set T2CR = tTwo.CurrentRegion
    Else
        Set T2CR = tOne.CurrentRegion
        Set T1CR = tTwo.CurrentRegion
    End If
    Last1 = T1CR.Rows.Count
    Last2 = T2CR.Rows.Count
    For I = 2 To Last1
        C1X = Application.WorksheetFunction.CountIfs(T2CR.Cells(1, 2).Resize(Last2, 1), T1CR.Cells(I, 2), T2CR.Cells(1, 4).Resize(Last2, 1), T1CR.Cells(I, 3) + 9999.8765 * (T1CR.Cells(I, 3) = 0))
        C1Y = Application.WorksheetFunction.CountIfs(T2CR.Cells(1, 2).Resize(Last2, 1), T1CR.Cells(I, 2), T2CR.Cells(1, 3).Resize(Last2, 1), T1CR.Cells(I, 4) + 9999.8765 * (T1CR.Cells(I, 4) = 0))
        If C1X > 0 Then
            T1CR.Cells(I, 1).Value = String(C1X, "X")
        ElseIf C1Y > 0 Then
            T1CR.Cells(I, 1).Value = String(C1Y, "Y")
        End If
    Next I
Next J
MsgBox ("Completed...")
End Sub

Copy the code in a Standard Module of your vba project; the lines marked <<< need to be compiled according the comment

I use CurrentRegion to select the complete table; but this require that there is one empty column to the right and to the left (if the startin column is not A), and one empty row at the bollom; if your data cannot guarantee this then we need to change someting in the code.

Please note that I chosed to insert as many Y or X as many times the line is found in the other worksheet; so Y means "only once", but YY means "twice"

Bye

 

[Please_H]

If "nothing happens" then you need to reinstall Windows and reinstall Office

I did add a Colomn to the Left and Right to the Table does have enough available colomns,
Did change the Macro area to B1s as well...
Yet Sheet 2 Returned 2X's and not sure why that happened...

wip Excel Sheet.xlsm
	B	C	D	E
1	Dups (X/Y)	Bank Cheque No.	Value (Dr)	Value (Cr)
2		986010	-	700.00
3	Y	986001	-	500.00
4		986020	-	950.00
5		QRS	8,500.00	-
6	X	ABC	300.00	-
7	X	DEF	3,000.00	-
Sheet2

And Colomns to Right and Left Row below would be an issue...

If the Macro is a lot of Work,
Can we go back to the Formula Bro?
Can you use the excel I've provided and workout the Formula?

 

[Anthony47]

Please don't leave on me understnding what in your opinion went wrong...
I understand that you used the macro; did you test it with the test workbook you shared or what? And which results were not ok?

You say something about "2X's"; cold that be in relation with my choice "Please note that I chosed to insert as many Y or X as many times the line is found in the other worksheet; so Y means "only once", but YY means "twice""?
Bye