URGENT ASSIST NEEDED IF POSSIBLE RE VLOOKUP/MATCH

I have the following, in 4 different columns:

Col A Col B Col C Col D
Serial Type Serial Type
12345 0475 12345 0475
12345 4394 12345 4850
12345 9800 12345 9800
49302 1929 49302 0493
49302 8473 49302 8747

I need to determine if there are any types in Col D that are NOT in Col B,
for the same serial (i.e., also matching Col A and C). Columns A/B have
about 50,000 rows; Columns C/D have about 27,000. Is there a way to do this
quickly with some VB code? I'm at a loss here and have to get this done by
end of today. Thanks for any help......Paige

PS: Forgot to say that (for example) a serial (in Col A) could have 40 rows
of types (in Col B), and the same serial (in Col C) could have 20 rows of
types (in Col D) or 150. A serial in Col A could start on row 450, and the
same serial in Col C may start on row 20, or row 1500.

"Paige" wrote:

> I have the following, in 4 different columns:
>
> Col A Col B Col C Col D
> Serial Type Serial Type
> 12345 0475 12345 0475
> 12345 4394 12345 4850
> 12345 9800 12345 9800
> 49302 1929 49302 0493
> 49302 8473 49302 8747
>
> I need to determine if there are any types in Col D that are NOT in Col B,
> for the same serial (i.e., also matching Col A and C). Columns A/B have
> about 50,000 rows; Columns C/D have about 27,000. Is there a way to do this
> quickly with some VB code? I'm at a loss here and have to get this done by
> end of today. Thanks for any help......Paige

Try this (limited testing done!):

Sub a()

Dim n1 As Integer, n2 As Integer
Dim srow As Long
Dim rngA As Range, rngB As Range, rngC As Range, rngD As Range
Dim rng1 As Range, rng2 As Range

With Worksheets("sheet1")

Set rngA = Range("A1:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
Set rngB = Range("B1:B" & .Cells(Rows.Count, "B").End(xlUp).Row)
Set rngC = Range("C1:C" & .Cells(Rows.Count, "C").End(xlUp).Row)
Set rngD = Range("D1:D" & .Cells(Rows.Count, "D").End(xlUp).Row)

r = 2
Do
n1 = Application.CountIf(rngA, .Cells(r, "A"))
n2 = Application.CountIf(rngC, .Cells(r, "A"))
' Check if serial in column A is in column C
srow = Application.Match(.Cells(r, "A"), rngC, 0)
If IsError(srow) Then
MsgBox .Cells(r, "A") & " not found in column C"
Else
Set rng1 = Range(rngB(r), rngB(r + n1 - 1))
Set rng2 = Range(rngD(srow), rngD(srow + n2 - 1))
' Loop through column D to find matches in column B
For Each cell In rng2
If Application.CountIf(rng1, cell) = 0 Then 'No match .....
MsgBox cell & " not found in Column B"
End If
Next cell
End If
r = r + n1
Loop Until r > rngA.Count
End With
End Sub

"Paige" wrote:

> PS: Forgot to say that (for example) a serial (in Col A) could have 40 rows
> of types (in Col B), and the same serial (in Col C) could have 20 rows of
> types (in Col D) or 150. A serial in Col A could start on row 450, and the
> same serial in Col C may start on row 20, or row 1500.
>
> "Paige" wrote:
>
> > I have the following, in 4 different columns:
> >
> > Col A Col B Col C Col D
> > Serial Type Serial Type
> > 12345 0475 12345 0475
> > 12345 4394 12345 4850
> > 12345 9800 12345 9800
> > 49302 1929 49302 0493
> > 49302 8473 49302 8747
> >
> > I need to determine if there are any types in Col D that are NOT in Col B,
> > for the same serial (i.e., also matching Col A and C). Columns A/B have
> > about 50,000 rows; Columns C/D have about 27,000. Is there a way to do this
> > quickly with some VB code? I'm at a loss here and have to get this done by
> > end of today. Thanks for any help......Paige

For what it's worth, you can do this in Excel, but it's a task better suited
for a database.

One way in Excel is to concatenate the first pair of columns and compare
those values to the concatenation of the second pair of columns. So, in F1
use the formula

=A1&" - "&B1

and copy it down to the end of the data in the first 2 columns.

In cell G1 use the formula

=C1&" - "&D1

and copy it down to the of the data in those columns.

Now you can use a VLOOKUP formula along the lines of

=IF(ISNA(VLOOKUP(G1,F1:F50000,1,FALSE)),"Missing","")

copy that down to the end of the data in column G. The ones that are
Missing will pop out.



"Paige" wrote:

> PS: Forgot to say that (for example) a serial (in Col A) could have 40 rows
> of types (in Col B), and the same serial (in Col C) could have 20 rows of
> types (in Col D) or 150. A serial in Col A could start on row 450, and the
> same serial in Col C may start on row 20, or row 1500.
>
> "Paige" wrote:
>
> > I have the following, in 4 different columns:
> >
> > Col A Col B Col C Col D
> > Serial Type Serial Type
> > 12345 0475 12345 0475
> > 12345 4394 12345 4850
> > 12345 9800 12345 9800
> > 49302 1929 49302 0493
> > 49302 8473 49302 8747
> >
> > I need to determine if there are any types in Col D that are NOT in Col B,
> > for the same serial (i.e., also matching Col A and C). Columns A/B have
> > about 50,000 rows; Columns C/D have about 27,000. Is there a way to do this
> > quickly with some VB code? I'm at a loss here and have to get this done by
> > end of today. Thanks for any help......Paige

Thanks to both of you - will try these....appreciate your help!

"Duke Carey" wrote:

> For what it's worth, you can do this in Excel, but it's a task better suited
> for a database.
>
> One way in Excel is to concatenate the first pair of columns and compare
> those values to the concatenation of the second pair of columns. So, in F1
> use the formula
>
> =A1&" - "&B1
>
> and copy it down to the end of the data in the first 2 columns.
>
> In cell G1 use the formula
>
> =C1&" - "&D1
>
> and copy it down to the of the data in those columns.
>
> Now you can use a VLOOKUP formula along the lines of
>
> =IF(ISNA(VLOOKUP(G1,F1:F50000,1,FALSE)),"Missing","")
>
> copy that down to the end of the data in column G. The ones that are
> Missing will pop out.
>
>
>
> "Paige" wrote:
>
> > PS: Forgot to say that (for example) a serial (in Col A) could have 40 rows
> > of types (in Col B), and the same serial (in Col C) could have 20 rows of
> > types (in Col D) or 150. A serial in Col A could start on row 450, and the
> > same serial in Col C may start on row 20, or row 1500.
> >
> > "Paige" wrote:
> >
> > > I have the following, in 4 different columns:
> > >
> > > Col A Col B Col C Col D
> > > Serial Type Serial Type
> > > 12345 0475 12345 0475
> > > 12345 4394 12345 4850
> > > 12345 9800 12345 9800
> > > 49302 1929 49302 0493
> > > 49302 8473 49302 8747
> > >
> > > I need to determine if there are any types in Col D that are NOT in Col B,
> > > for the same serial (i.e., also matching Col A and C). Columns A/B have
> > > about 50,000 rows; Columns C/D have about 27,000. Is there a way to do this
> > > quickly with some VB code? I'm at a loss here and have to get this done by
> > > end of today. Thanks for any help......Paige

Watch out for those unqualified ranges.

Set rngA = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
Set rngB = .Range("B1:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
Set rngC = .Range("C1:C" & .Cells(.Rows.Count, "C").End(xlUp).Row)
Set rngD = .Range("D1:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)

I like the dot in front of .rows.count, but it isn't necessary.

But the dot in front of .range("a1...
will be--unless Sheet1 is the activesheet.

Toppers wrote:
>
> Try this (limited testing done!):
>
> Sub a()
>
> Dim n1 As Integer, n2 As Integer
> Dim srow As Long
> Dim rngA As Range, rngB As Range, rngC As Range, rngD As Range
> Dim rng1 As Range, rng2 As Range
>
> With Worksheets("sheet1")
>
> Set rngA = Range("A1:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
> Set rngB = Range("B1:B" & .Cells(Rows.Count, "B").End(xlUp).Row)
> Set rngC = Range("C1:C" & .Cells(Rows.Count, "C").End(xlUp).Row)
> Set rngD = Range("D1:D" & .Cells(Rows.Count, "D").End(xlUp).Row)
>
> r = 2
> Do
> n1 = Application.CountIf(rngA, .Cells(r, "A"))
> n2 = Application.CountIf(rngC, .Cells(r, "A"))
> ' Check if serial in column A is in column C
> srow = Application.Match(.Cells(r, "A"), rngC, 0)
> If IsError(srow) Then
> MsgBox .Cells(r, "A") & " not found in column C"
> Else
> Set rng1 = Range(rngB(r), rngB(r + n1 - 1))
> Set rng2 = Range(rngD(srow), rngD(srow + n2 - 1))
> ' Loop through column D to find matches in column B
> For Each cell In rng2
> If Application.CountIf(rng1, cell) = 0 Then 'No match .....
> MsgBox cell & " not found in Column B"
> End If
> Next cell
> End If
> r = r + n1
> Loop Until r > rngA.Count
> End With
> End Sub
>
> "Paige" wrote:
>
> > PS: Forgot to say that (for example) a serial (in Col A) could have 40 rows
> > of types (in Col B), and the same serial (in Col C) could have 20 rows of
> > types (in Col D) or 150. A serial in Col A could start on row 450, and the
> > same serial in Col C may start on row 20, or row 1500.
> >
> > "Paige" wrote:
> >
> > > I have the following, in 4 different columns:
> > >
> > > Col A Col B Col C Col D
> > > Serial Type Serial Type
> > > 12345 0475 12345 0475
> > > 12345 4394 12345 4850
> > > 12345 9800 12345 9800
> > > 49302 1929 49302 0493
> > > 49302 8473 49302 8747
> > >
> > > I need to determine if there are any types in Col D that are NOT in Col B,
> > > for the same serial (i.e., also matching Col A and C). Columns A/B have
> > > about 50,000 rows; Columns C/D have about 27,000. Is there a way to do this
> > > quickly with some VB code? I'm at a loss here and have to get this done by
> > > end of today. Thanks for any help......Paige

--

Dave Peterson

Got both of these working - thanks to all of you!!! Just one quick question
for the next time I have to do this. If I want to put a message to the right
of each 'no match' (versus a message box), how would this be done?

"Paige" wrote:

> Thanks to both of you - will try these....appreciate your help!
>
> "Duke Carey" wrote:
>
> > For what it's worth, you can do this in Excel, but it's a task better suited
> > for a database.
> >
> > One way in Excel is to concatenate the first pair of columns and compare
> > those values to the concatenation of the second pair of columns. So, in F1
> > use the formula
> >
> > =A1&" - "&B1
> >
> > and copy it down to the end of the data in the first 2 columns.
> >
> > In cell G1 use the formula
> >
> > =C1&" - "&D1
> >
> > and copy it down to the of the data in those columns.
> >
> > Now you can use a VLOOKUP formula along the lines of
> >
> > =IF(ISNA(VLOOKUP(G1,F1:F50000,1,FALSE)),"Missing","")
> >
> > copy that down to the end of the data in column G. The ones that are
> > Missing will pop out.
> >
> >
> >
> > "Paige" wrote:
> >
> > > PS: Forgot to say that (for example) a serial (in Col A) could have 40 rows
> > > of types (in Col B), and the same serial (in Col C) could have 20 rows of
> > > types (in Col D) or 150. A serial in Col A could start on row 450, and the
> > > same serial in Col C may start on row 20, or row 1500.
> > >
> > > "Paige" wrote:
> > >
> > > > I have the following, in 4 different columns:
> > > >
> > > > Col A Col B Col C Col D
> > > > Serial Type Serial Type
> > > > 12345 0475 12345 0475
> > > > 12345 4394 12345 4850
> > > > 12345 9800 12345 9800
> > > > 49302 1929 49302 0493
> > > > 49302 8473 49302 8747
> > > >
> > > > I need to determine if there are any types in Col D that are NOT in Col B,
> > > > for the same serial (i.e., also matching Col A and C). Columns A/B have
> > > > about 50,000 rows; Columns C/D have about 27,000. Is there a way to do this
> > > > quickly with some VB code? I'm at a loss here and have to get this done by
> > > > end of today. Thanks for any help......Paige

Hi,

For Each cell In rng2
If Application.CountIf(rng1, cell) = 0 Then 'No match .....
cell.Offset(0, 1) = "no match" '<=== Replaces message box
End If

"Paige" wrote:

> Got both of these working - thanks to all of you!!! Just one quick question
> for the next time I have to do this. If I want to put a message to the right
> of each 'no match' (versus a message box), how would this be done?
>
> "Paige" wrote:
>
> > Thanks to both of you - will try these....appreciate your help!
> >
> > "Duke Carey" wrote:
> >
> > > For what it's worth, you can do this in Excel, but it's a task better suited
> > > for a database.
> > >
> > > One way in Excel is to concatenate the first pair of columns and compare
> > > those values to the concatenation of the second pair of columns. So, in F1
> > > use the formula
> > >
> > > =A1&" - "&B1
> > >
> > > and copy it down to the end of the data in the first 2 columns.
> > >
> > > In cell G1 use the formula
> > >
> > > =C1&" - "&D1
> > >
> > > and copy it down to the of the data in those columns.
> > >
> > > Now you can use a VLOOKUP formula along the lines of
> > >
> > > =IF(ISNA(VLOOKUP(G1,F1:F50000,1,FALSE)),"Missing","")
> > >
> > > copy that down to the end of the data in column G. The ones that are
> > > Missing will pop out.
> > >
> > >
> > >
> > > "Paige" wrote:
> > >
> > > > PS: Forgot to say that (for example) a serial (in Col A) could have 40 rows
> > > > of types (in Col B), and the same serial (in Col C) could have 20 rows of
> > > > types (in Col D) or 150. A serial in Col A could start on row 450, and the
> > > > same serial in Col C may start on row 20, or row 1500.
> > > >
> > > > "Paige" wrote:
> > > >
> > > > > I have the following, in 4 different columns:
> > > > >
> > > > > Col A Col B Col C Col D
> > > > > Serial Type Serial Type
> > > > > 12345 0475 12345 0475
> > > > > 12345 4394 12345 4850
> > > > > 12345 9800 12345 9800
> > > > > 49302 1929 49302 0493
> > > > > 49302 8473 49302 8747
> > > > >
> > > > > I need to determine if there are any types in Col D that are NOT in Col B,
> > > > > for the same serial (i.e., also matching Col A and C). Columns A/B have
> > > > > about 50,000 rows; Columns C/D have about 27,000. Is there a way to do this
> > > > > quickly with some VB code? I'm at a loss here and have to get this done by
> > > > > end of today. Thanks for any help......Paige

You're a peach!!!! Thanks so much....Paige

"Toppers" wrote:

> Hi,
>
> For Each cell In rng2
> If Application.CountIf(rng1, cell) = 0 Then 'No match .....
> cell.Offset(0, 1) = "no match" '<=== Replaces message box
> End If
>
> "Paige" wrote:
>
> > Got both of these working - thanks to all of you!!! Just one quick question
> > for the next time I have to do this. If I want to put a message to the right
> > of each 'no match' (versus a message box), how would this be done?
> >
> > "Paige" wrote:
> >
> > > Thanks to both of you - will try these....appreciate your help!
> > >
> > > "Duke Carey" wrote:
> > >
> > > > For what it's worth, you can do this in Excel, but it's a task better suited
> > > > for a database.
> > > >
> > > > One way in Excel is to concatenate the first pair of columns and compare
> > > > those values to the concatenation of the second pair of columns. So, in F1
> > > > use the formula
> > > >
> > > > =A1&" - "&B1
> > > >
> > > > and copy it down to the end of the data in the first 2 columns.
> > > >
> > > > In cell G1 use the formula
> > > >
> > > > =C1&" - "&D1
> > > >
> > > > and copy it down to the of the data in those columns.
> > > >
> > > > Now you can use a VLOOKUP formula along the lines of
> > > >
> > > > =IF(ISNA(VLOOKUP(G1,F1:F50000,1,FALSE)),"Missing","")
> > > >
> > > > copy that down to the end of the data in column G. The ones that are
> > > > Missing will pop out.
> > > >
> > > >
> > > >
> > > > "Paige" wrote:
> > > >
> > > > > PS: Forgot to say that (for example) a serial (in Col A) could have 40 rows
> > > > > of types (in Col B), and the same serial (in Col C) could have 20 rows of
> > > > > types (in Col D) or 150. A serial in Col A could start on row 450, and the
> > > > > same serial in Col C may start on row 20, or row 1500.
> > > > >
> > > > > "Paige" wrote:
> > > > >
> > > > > > I have the following, in 4 different columns:
> > > > > >
> > > > > > Col A Col B Col C Col D
> > > > > > Serial Type Serial Type
> > > > > > 12345 0475 12345 0475
> > > > > > 12345 4394 12345 4850
> > > > > > 12345 9800 12345 9800
> > > > > > 49302 1929 49302 0493
> > > > > > 49302 8473 49302 8747
> > > > > >
> > > > > > I need to determine if there are any types in Col D that are NOT in Col B,
> > > > > > for the same serial (i.e., also matching Col A and C). Columns A/B have
> > > > > > about 50,000 rows; Columns C/D have about 27,000. Is there a way to do this
> > > > > > quickly with some VB code? I'm at a loss here and have to get this done by
> > > > > > end of today. Thanks for any help......Paige