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

Please_H

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows
Dear All,

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

Point 01 - What I have :

wip Excel Sheet.xlsm
ABCD
1Dups (X/Y)Bank Cheque No.Value (Dr)Value (Cr)
2986001 500.00 -
3986002 1,000.00 -
4986003 1,500.00 -
5ABC - 300.00
6DEF - 3,000.00
7GHI - 4,500.00
Sheet1


wip Excel Sheet.xlsm
ABCD
1Dups (X/Y)Bank Cheque No.Value (Dr)Value (Cr)
2986010 - 700.00
3986001 - 500.00
4986020 - 950.00
5QRS 8,500.00 -
6ABC 300.00 -
7DEF 3,000.00 -
Sheet2


Point 02 - What I Need :

wip Excel Sheet.xlsm
ABCD
1Dups (X/Y)Bank Cheque No.Value (Dr)Value (Cr)
2X986001 500.00 -
3986002 1,000.00 -
4986003 1,500.00 -
5YABC - 300.00
6YDEF - 3,000.00
7GHI - 4,500.00
Sheet1


wip Excel Sheet.xlsm
ABCD
1Dups (X/Y)Bank Cheque No.Value (Dr)Value (Cr)
2986010 - 700.00
3X986001 - 500.00
4986020 - 950.00
5QRS 8,500.00 -
6YABC 300.00 -
7YDEF 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.

VBA Code:
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.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,135
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

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Mar 29, 2006
Messages
2,135
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

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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... :unsure:
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,135
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 :LOL: :LOL:

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:
VBA Code:
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

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows
If "nothing happens" then you need to reinstall Windows and reinstall Office :LOL:
:LOL:

:ROFLMAO: :ROFLMAO: :ROFLMAO:

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
BCDE
1Dups (X/Y)Bank Cheque No.Value (Dr)Value (Cr)
2986010 - 700.00
3Y986001 - 500.00
4986020 - 950.00
5QRS 8,500.00 -
6XABC 300.00 -
7XDEF 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

Well-known Member
Joined
Mar 29, 2006
Messages
2,135
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,334
Messages
5,624,071
Members
416,010
Latest member
NJT

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
Top