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.
 

Please_H

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

I need the exact result I mentioned in my first post bro...
The second sheet gave me "X" and "Y" the other way around...
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Please_H

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

What I need in Sheet 2 :

wip Excel Sheet.xlsm
BCDE
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


What Macro Return in Sheet 2 :

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


So if the Macro is a challenge,
Can we fall back to a Formula or UDF?
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,135
As I said before, please don't ask us to check for the error... If you found the error please save our time, it is not less important then your. Ok?
So, after spotting the figures I realize that Y and X are reverted on sheet2; did I win anything by discovering the differences?

To recover that, replace these rows:
VBA Code:
        If C1X > 0 Then
            T1CR.Cells(I, 1).Value = String(C1X, Chr(87 + J))
        ElseIf C1Y > 0 Then
            T1CR.Cells(I, 1).Value = String(C1Y, Chr(90 - J))
        End If
 

Please_H

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows
As I said before, please don't ask us to check for the error... If you found the error please save our time, it is not less important then your. Ok?
So, after spotting the figures I realize that Y and X are reverted on sheet2; did I win anything by discovering the differences?

To recover that, replace these rows:
VBA Code:
        If C1X > 0 Then
            T1CR.Cells(I, 1).Value = String(C1X, Chr(87 + J))
        ElseIf C1Y > 0 Then
            T1CR.Cells(I, 1).Value = String(C1Y, Chr(90 - J))
        End If

I am sorry Bro,
I do not know VBA, that is why I mentioned something was wrong with the VBA and shared the image....:confused:
Am I doing something wrong?
 

Please_H

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

ADVERTISEMENT

As I said before, please don't ask us to check for the error... If you found the error please save our time, it is not less important then your. Ok?
So, after spotting the figures I realize that Y and X are reverted on sheet2; did I win anything by discovering the differences?

To recover that, replace these rows:
VBA Code:
        If C1X > 0 Then
            T1CR.Cells(I, 1).Value = String(C1X, Chr(87 + J))
        ElseIf C1Y > 0 Then
            T1CR.Cells(I, 1).Value = String(C1Y, Chr(90 - J))
        End If

OOOMMMGGG....
BROOOO...
I THINK WE DID IT.....
IT WORKS...:eek::eek::eek::love:

Thanks a lot for helping me Out Man...
I am sorry if there was a misunderstanding Bro...
Even to paste an image I was taught by the Previous Brother who helped me with the initial Coding...
 

Please_H

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows
To recover that, replace these rows:
VBA Code:
        If C1X > 0 Then
            T1CR.Cells(I, 1).Value = String(C1X, Chr(87 + J))
        ElseIf C1Y > 0 Then
            T1CR.Cells(I, 1).Value = String(C1Y, Chr(90 - J))
        End If


Okay
I have Run into a Problem,
I am trying to apply the Macro to a Sample Sheet I use at Work...

In that there are couple of Colomns in-between like Date and Narration,
I assumed the Colomns would have been mentioned in the Macro for me to adjust at my Requirement...
But I can't figure how it works in this...
😭😰😰🥺

when I run the VBA, I am getting an error and when I debug it this below is highlighted...

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


wip Excel Sheet.xlsm
BCDEFG
1Dups (X/Y)DateBank Cheque No.NarrationValue (Dr)Value (Cr)
2Row Cell Ref : C16January 1, 2020986001Check issued to Mr.A 500.00 -
3January 1, 2020986002Check issued to Mr.B 1,000,000.00 -
4January 1, 2020986003Check issued to Mr.C 1,500.00 -
5January 1, 2020ABCCash Received from Mr. J - 300.00
6January 1, 2020DEFCash Received from Mr. K - 3,000.00
7January 1, 2020GHICash Received from Mr. L - 4,500.00
Sheet1



If my Information of Cells starts from Row Number 16, Colomn C : Therefore C16
And Have Colomn for Date and Narration,
How do I adjust the Macro?
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,135

ADVERTISEMENT

So what was Column 2 is in now your real world Column 3; old Col 3 is now col 5; old col 4 is now col 6; so adjuste the C1X = etc etc line AND the C1Y = etc etc line
You have to change all those "Cells(xx, N)", by modifying each of the "N" using the above conversion table (replace Old with Now) and leaving unmodified the "xx" part

You have also to adjust the lines that define the topleft of the two tables:
VBA Code:
Set tOne = etc etc     '<<< Start of first table
Set tTwo = etc etc     '<<< Start of second table

They must point to the cells that contains Dups (X/Y)

Remember that on empty column must exist at the right of the table and, if the topleft column is higher then A, an empty column must exist also at the left

Bye
 

Please_H

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows
Okay, let me try give a shot again, I think I understand what you mean now...
 

Please_H

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows
So what was Column 2 is in now your real world Column 3; old Col 3 is now col 5; old col 4 is now col 6; so adjuste the C1X = etc etc line AND the C1Y = etc etc line
You have to change all those "Cells(xx, N)", by modifying each of the "N" using the above conversion table (replace Old with Now) and leaving unmodified the "xx" part

You have also to adjust the lines that define the topleft of the two tables:
VBA Code:
Set tOne = etc etc     '<<< Start of first table
Set tTwo = etc etc     '<<< Start of second table

They must point to the cells that contains Dups (X/Y)

Remember that on empty column must exist at the right of the table and, if the topleft column is higher then A, an empty column must exist also at the left

Bye




Okay Bro,
Still Seem to have Couple of issues...

The below sheet is When the Macro gives a Error message unable to get anything...
This was the Macro lines I changed,


VBA Code:
Set tOne = Sheets("F1 - B.Stat").Range("C15")     '<<< Start of first table
Set tTwo = Sheets("F1 - LedG").Range("C15")     '<<< Start of second table

...and,


VBA Code:
C1X = Application.WorksheetFunction.CountIfs(T2CR.Cells(1, 3).Resize(Last2, 1), T1CR.Cells(I, 3), T2CR.Cells(1, 6).Resize(Last2, 1), T1CR.Cells(I, 5) + 9999.8765 * (T1CR.Cells(I, 5) = 0))
        C1Y = Application.WorksheetFunction.CountIfs(T2CR.Cells(1, 3).Resize(Last2, 1), T1CR.Cells(I, 3), T2CR.Cells(1, 5).Resize(Last2, 1), T1CR.Cells(I, 6) + 9999.8765 * (T1CR.Cells(I, 6) = 0))


This is How the Table Looks

XBANK STATEMENT ( RECONCILIATION ) -
XPREVIOUS DATA AND LEDGER INFO ( RECONCILIATION ) - -
YBANK STATEMENT ( RECONCILIATION ) -
YPREVIOUS DATA AND LEDGER INFO ( RECONCILIATION ) - -
AMOUNTS OUT BY 733,845.47 -
SUM - BALANCE ( BY COLOMN ) 23,172,152.38 20,029,921.26
SUM - BALANCE ( BY DEDUCTION ) 23,905,997.85 20,029,921.26
SUM - X / Y - -
SUM - TOTAL 23,905,997.85 20,029,921.26
BANK STATEMENT ( RECONCILIATION )
CHEQUE INFO
XDATECHEQUE NO.NARRATIONDR ( X )CR ( Y )
1-Jan-2020xxxxxxxxxxx 50,000.00 -
1-Jan-2020917768 216,000.00 -



But then I thought what If I deleted the lines above the Headings...
So I changed the Code and deleted like below...


VBA Code:
Set tOne = Sheets("F1 - B.Stat").Range("C1")     '<<< Start of first table
Set tTwo = Sheets("F1 - LedG").Range("C1")     '<<< Start of second table


XDATECHEQUE NO.NARRATIONDR ( X )CR ( Y )
1-Jan-2020xxxxxxxxxxx 50,000.00 -
X1-Jan-2020917768 216,000.00 -



Then the Macro was able to Run...


But then there was the second issue,
But I will give you the good news First,
All "X"'s were correctly picked up with a previous Data Set (from work) I checked.

The Bad news is,
Not a single "Y" got picked up. :(:(:(


What could be the issue?

I have attached the Workbook I have been trying with Original Data
Links are below,

Reco Test Set with Headings ( Macro Didn't Work)

Reco Test Set with-out Headings ( Macro Success Only X)
 
Last edited:

Please_H

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows
So what was Column 2 is in now your real world Column 3; old Col 3 is now col 5; old col 4 is now col 6; so adjuste the C1X = etc etc line AND the C1Y = etc etc line
You have to change all those "Cells(xx, N)", by modifying each of the "N" using the above conversion table (replace Old with Now) and leaving unmodified the "xx" part



Okay Bro,
I identified what was the issue with "Y".


Seems "Y" is also capturing the details in the Colomn Number 3, "Bank Cheque Number" which I do not want to happen...

"Y" Should ONLY look for > Sheet1 : Column 6 vs Sheet2 : Column 5
"Y" ignores whatever is in Column 3 Completely.
"Y" focuses only the Values / Amounts.


So we have to do that Adjustment for the Macro.

And please advise me how to change the Macro when I have Lines above the Headings.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,217
Messages
5,623,450
Members
415,970
Latest member
ZorroOP

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