I have 2 Lists of Data on the same sheet

List A is detailed in Columns A-D

List B is detailed in Column G-K

What I want to do is compare certain Columns/Rows in both Lists and for each one that matches, place an X in Column E (covering List A) and a corresponding X in Column L (covering List B). Note the matched values will not be on the same Row in both

So what will be compared? if Col B & D rows matches somewhere with Col H & K an X is placed on the appropriate Row in Cols E & L

Essentially I want to do a Supplier A/c Reconciliation. Above would give me a good start point

Hi Sean,

Am Fri, 18 Nov 2016 11:17:48 -0800 (PST) schrieb :

I have 2 Lists of Data on the same sheet

List A is detailed in Columns A-D

List B is detailed in Column G-K

What I want to do is compare certain Columns/Rows in both Lists and for each one that matches, place an X in Column E (covering List A) and a corresponding X in Column L (covering List B). Note the matched values will not be on the same Row in both

So what will be compared? if Col B & D rows matches somewhere with Col H & K an X is placed on the appropriate Row in Cols E & L

try:

Sub CompareList()
Dim LRowA As Long, LRowB As Long, i As Long, j As Long
Dim varA As Variant, varB As Variant

With ActiveSheet
LRowA = .Cells(.Rows.Count, "A").End(xlUp).Row
LRowB = .Cells(.Rows.Count, "G").End(xlUp).Row
varA = .Range("A1:D" & LRowA)
varB = .Range("G1:K" & LRowB)

For i = LBound(varA) To UBound(varA)
For j = LBound(varB) To UBound(varB)
If varA(i, 2) & varA(i, 4) = varB(j, 2) & varB(j, 5) Then
.Cells(i, "E") = "x"
.Cells(i, "L") = "x"
'If there is only one match possible delete the
apostroph in front of next line
'Exit For
End If
Next
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Hi Sean,

Am Fri, 18 Nov 2016 11:17:48 -0800 (PST) schrieb
:

I have 2 Lists of Data on the same sheet

List A is detailed in Columns A-D

List B is detailed in Column G-K

What I want to do is compare certain Columns/Rows in both Lists and
for each one that matches, place an X in Column E (covering List A)
and a corresponding X in Column L (covering List B). Note the
matched values will not be on the same Row in both

So what will be compared? if Col B & D rows matches somewhere with
Col H & K an X is placed on the appropriate Row in Cols E & L

try:

Sub CompareList()
Dim LRowA As Long, LRowB As Long, i As Long, j As Long
Dim varA As Variant, varB As Variant

With ActiveSheet
LRowA = .Cells(.Rows.Count, "A").End(xlUp).Row
LRowB = .Cells(.Rows.Count, "G").End(xlUp).Row
varA = .Range("A1:D" & LRowA)
varB = .Range("G1:K" & LRowB)

For i = LBound(varA) To UBound(varA)
For j = LBound(varB) To UBound(varB)
If varA(i, 2) & varA(i, 4) = varB(j, 2) & varB(j, 5) Then
.Cells(i, "E") = "x"
.Cells(i, "L") = "x"
??s/b?? .Cells(j, "L") = "x"
'If there is only one match possible delete the
apostroph in front of next line
'Exit For
End If
Next
Next
End With
End Sub


Regards
Claus B.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Am Fri, 18 Nov 2016 14:46:43 -0500 schrieb GS:

.Cells(i, "E") = "x"
.Cells(i, "L") = "x"
??s/b?? .Cells(j, "L") = "x"

yes, you are right. It should be .cells(j,"L")


Regards
Claus B.
--
Windows10
Office 2016

Thanks Guys

What is it with copying the code from these forums, I always get syntax errors and end up removing all spaces from leading and end of each line?

One Q, I've no data (yet), but when I ran code it placed an X in Row 1 E&L, it should be nothing, just a quirky one at this stage

Hi Sean,

Am Fri, 18 Nov 2016 12:42:59 -0800 (PST) schrieb :

What is it with copying the code from these forums, I always get syntax errors and end up removing all spaces from leading and end of each line?

I don't know why copying code makes that issue

One Q, I've no data (yet), but when I ran code it placed an X in Row 1 E&L, it should be nothing, just a quirky one at this stage

If you have headers, change the code:

Sub CompareList()
Dim LRowA As Long, LRowB As Long, i As Long, j As Long
Dim varA As Variant, varB As Variant

With ActiveSheet
LRowA = .Cells(.Rows.Count, "A").End(xlUp).Row
LRowB = .Cells(.Rows.Count, "G").End(xlUp).Row
varA = .Range("A2:D" & LRowA)
varB = .Range("G2:K" & LRowB)

For i = LBound(varA) To UBound(varA)
For j = LBound(varB) To UBound(varB)
If varA(i, 2) & varA(i, 4) = varB(j, 2) & varB(j, 5) Then
.Cells(i + 1, "E") = "x"
.Cells(j + 1, "L") = "x"
'If there is only one match possible delete the
apostroph in front of next line
'Exit For
End If
Next
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

What is it with copying the code from these forums, I always get
syntax errors and end up removing all spaces from leading and end of
each line?

Wordwraps and/or linewraps in the news reader...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Cho mình một vé
_________________
kinky videos

https://t.co/2K1zjCFslH