Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT ASSIST NEEDED IF POSSIBLE RE VLOOKUP/MATCH
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT ASSIST NEEDED IF POSSIBLE RE VLOOKUP/MATCH
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT ASSIST NEEDED IF POSSIBLE RE VLOOKUP/MATCH
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT ASSIST NEEDED IF POSSIBLE RE VLOOKUP/MATCH
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT ASSIST NEEDED IF POSSIBLE RE VLOOKUP/MATCH
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT ASSIST NEEDED IF POSSIBLE RE VLOOKUP/MATCH
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT ASSIST NEEDED IF POSSIBLE RE VLOOKUP/MATCH
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT ASSIST NEEDED IF POSSIBLE RE VLOOKUP/MATCH
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help needed for Excel formula using if, iserr, match and vlookup! | Excel Worksheet Functions | |||
Match, VLookup possible formula needed | Excel Discussion (Misc queries) | |||
Using Vlookup or other function can assist? | Excel Worksheet Functions | |||
Assist with Match Dates | Excel Discussion (Misc queries) | |||
Help Needed Urgent | Excel Discussion (Misc queries) |