Please_H
Board Regular
- Joined
- Apr 16, 2017
- Messages
- 181
- Office Version
- 2019
- Platform
- Windows
Dear All,
I hope everyone of you are indoors, safe and sound.
Point 01 - What I have :
Point 02 - What I Need :
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.
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.
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.
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 SubPoint 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.
