Hi, I need help on this one because I just can't figure out how to do this.
(Excel 2002SP3).

Two sheets in same workbook. Sheet one has data in col A, B & C. Sheet 2
has identical data in col A, B & D. Hundreds of rows on each sheet - but
probably only about 10% exactly match on all three columns per row.
I need (on a seperate sheet in same workbook) to populate rows if sheet 1
A1,B1 & C1 are exactly the same as sheet 2 A1, B1 & D1 - if not exact match
on all 3 cells it must ignore. I'd prefer not to leave lines/rows between
the populated data. Hence I should end up with one sheet that contains cols
A,B & C with however many rows, where the cols are identical on both sheets.
(Be nice to have one sheet with +- 150 entries instead of two sheets with
thousands).

If anyone can help me out, I would really appreciate it. Take care
--
Amanda
Johannesburg

copy Sheet2 and name it sheet3
in column E put in the formula

Put this in E2

=IF(SUMPRODUCT(--(Sheet1!$A$1:$A$1000&Sheet1!$B$1:$B$1000&Sheet1!$C $1:$C$100
0=A2&B2&D2))0,"",na())

(change the 1000 to reflect the number of rows with data in sheet1)

then drag fill down column E next to your data

then select column E and do Edit=Goto special, select Formulas and Errors

do Edit=Delete and select EntireRow

Now delete column E.

that should give you your list.

--
Regards,
Tom Ogilvy



"Amanda" wrote in message
...
Hi, I need help on this one because I just can't figure out how to do
this.
(Excel 2002SP3).

Two sheets in same workbook. Sheet one has data in col A, B & C. Sheet 2
has identical data in col A, B & D. Hundreds of rows on each sheet - but
probably only about 10% exactly match on all three columns per row.
I need (on a seperate sheet in same workbook) to populate rows if sheet 1
A1,B1 & C1 are exactly the same as sheet 2 A1, B1 & D1 - if not exact
match
on all 3 cells it must ignore. I'd prefer not to leave lines/rows between
the populated data. Hence I should end up with one sheet that contains
cols
A,B & C with however many rows, where the cols are identical on both
sheets.
(Be nice to have one sheet with +- 150 entries instead of two sheets with
thousands).

If anyone can help me out, I would really appreciate it. Take care
--
Amanda
Johannesburg

Amanda,
Give this a try (test data first!). It creates an array
(mArr) of the concatenation of cells A,B and D in sheet2. It loops through
sheet1 and looks for a match against "mArr": if match found, A,B and C are
wriiten to sheet3.


HTH


Sub matchABC()

Dim mArr() As String
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim lastrow As Long, r As Long
Dim outrng As Range
Dim FindMatch As String

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set ws3 = Worksheets("Sheet3")

Set outrng = ws3.Range("a2")

ws2.Activate
With ws2
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
ReDim mArr(lastrow - 1) ' Assumes data starts in row 2
For r = 2 To lastrow ' Store concatenation of cells A,B and D on sheet2
mArr(r - 1) = Cells(r, 1) & Cells(r, 2) & Cells(r, 4)
Next r

End With
ws1.Activate
With ws1
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lastrow
FindMatch = Cells(r, 1) & Cells(r, 2) & Cells(r, 3) 'Concatenate
cells A,B and C
res = Application.Match(FindMatch, mArr, 0) ' Look for match in
sheet2 list
If Not IsError(res) Then
ws1.Cells(r, 1).Resize(1, 3).Copy outrng ' Copy A,B,C to sheet3
Set outrng = outrng.Offset(1, 0)
End If
Next r
End With
End Sub



"Amanda" wrote:

Hi, I need help on this one because I just can't figure out how to do this.
(Excel 2002SP3).

Two sheets in same workbook. Sheet one has data in col A, B & C. Sheet 2
has identical data in col A, B & D. Hundreds of rows on each sheet - but
probably only about 10% exactly match on all three columns per row.
I need (on a seperate sheet in same workbook) to populate rows if sheet 1
A1,B1 & C1 are exactly the same as sheet 2 A1, B1 & D1 - if not exact match
on all 3 cells it must ignore. I'd prefer not to leave lines/rows between
the populated data. Hence I should end up with one sheet that contains cols
A,B & C with however many rows, where the cols are identical on both sheets.
(Be nice to have one sheet with +- 150 entries instead of two sheets with
thousands).

If anyone can help me out, I would really appreciate it. Take care
--
Amanda
Johannesburg

Hi Tom & Toppers.

Thanks for your suggestions & fantastic help - I'm going to be using a
mixture of both methods because I have lots of data on Excel that the staff
are battling with - you are both stars! Have a fantastic day & take care.

--
Amanda
Johannesburg



"Amanda" wrote:

Hi, I need help on this one because I just can't figure out how to do this.
(Excel 2002SP3).

Two sheets in same workbook. Sheet one has data in col A, B & C. Sheet 2
has identical data in col A, B & D. Hundreds of rows on each sheet - but
probably only about 10% exactly match on all three columns per row.
I need (on a seperate sheet in same workbook) to populate rows if sheet 1
A1,B1 & C1 are exactly the same as sheet 2 A1, B1 & D1 - if not exact match
on all 3 cells it must ignore. I'd prefer not to leave lines/rows between
the populated data. Hence I should end up with one sheet that contains cols
A,B & C with however many rows, where the cols are identical on both sheets.
(Be nice to have one sheet with +- 150 entries instead of two sheets with
thousands).

If anyone can help me out, I would really appreciate it. Take care
--
Amanda
Johannesburg