PDA

View Full Version : vlookup match columns



Emoncada
03-31-2008, 06:19 AM
I have two column's of data about 20 cells down.
Basically what I have is a Total on one spreadsheet then have it look at the other spreadsheet column and see if that total is the same.
What I need if to make sure the data matches, the problem is they won't be in any type of order. What is the best way to make this happen? Should I sort it first?

Example
Spreadsheet 1

Column A..........B
T7316 .............2
6408D .............1
T7316E ...........1
8410D .............2

Total ...............6

*Note both column's will not be the same as in Letter.

Spreadsheet 2

Column J
T7316 .............2
BIS 10 .............2
6408D .............1
T7316E ...........1

Total ...............6

As you can see what's in BOLD. The Total will be the same but Models are different in one of them.
how can i have it test this and if not matching give me a "Check Models" in a cell.

Any Ideas?

Bob Phillips
03-31-2008, 08:18 AM
Presumably, although they may not match line for line, they may match data item for data item as two items may be out of order on sheet? If this is the case, do you want the No Match message?

Emoncada
03-31-2008, 08:32 AM
Yeah if the data matches even if there not in any particular order. I wan't it to say "Good" or something. If the data doesn't match then give the No Match message.

Bob Phillips
03-31-2008, 09:36 AM
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow1 As Long
Dim LastRow2 As Long
Dim msg As String

With ActiveSheet

LastRow1 = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
LastRow2 = Worksheets("Sheet2").Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
msg = "All OK"
If LastRow1 <> LastRow2 Then

msg = "Check Models"
Else

For i = 2 To LastRow

If IsError(Application.Match(.Cells(i, "A").Value, Worksheets("Sheet1").Columns(1), 0)) Then

msg = "Check Models"
Exit For
End If
Next i
End If

MsgBox msg
End With

End Sub

Emoncada
03-31-2008, 10:23 AM
XLD is there a way that I can have this work for ranges.
Example
I need a formula that would look at sheet 1
column CT (Models) & CU (Qty) Cells 7 : 26

Then Sheet 2
Column EV (Models) & EW (Qty) Cells 9 : 28

Then the next Day I would need it to work for this
I need a formula that would look at sheet 1
column CX (Models) & CY (Qty) Cells 7 : 26

Then Sheet 2
Column EX (Models) & EY (Qty) Cells 9 : 28

hope that helps. I can't have it just look at one column

WinteE
03-31-2008, 10:26 AM
Try this :



Sub CheckMatch()

i = Worksheets("Sheet1").Cells(Cells.Rows.Count,"A").End(xlUp).Row
k = Worksheets("Sheet2").Cells(Cells.Rows.Count,"J").End(xlUp).Row

For Each x in Worksheets("Sheet1").Range("A1:A" & i)
z = 0
For Each y in Worksheets("Sheet2").Range("J1:J" & k)
If x.Value = y.Value Then
z = 1
End If
Next y

If z = 1 Then
x.Interior.ColorIndex = 3
End If
Next x

End Sub


This code will turn the background to red of every cell in Sheet1 that doesn't match with Sheet2.


Erik