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

Hi Toppers,

I've copied your script to VB & then ran it, (with all fingers crossed). It
has given me an "Invalid use of property" error and highlighted the last
cells word (cells A,B and C) in the sentence " FindMatch = Cells(r, 1) &
Cells(r, 2) & Cells(r, 3) 'Concatenate
cells A,B and C

Have I done something wrong?

Cheers & thanks so much for your help.

Amanda
Johannesburg



"Toppers" wrote:

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

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

Amanda,

" 'Concatenate cells A,B and C " - it is a comment - should all be on one
line and I suspect you have it on two (because of the 'wrap-round' when code
is posted). I tried my own code with this situation and got the same error.

So simplly move the line "cells a,b ..." to the end of 'Concatenate ... or
delete it.

HTH

"Amanda" wrote:

Hi Toppers,

I've copied your script to VB & then ran it, (with all fingers crossed). It
has given me an "Invalid use of property" error and highlighted the last
cells word (cells A,B and C) in the sentence " FindMatch = Cells(r, 1) &
Cells(r, 2) & Cells(r, 3) 'Concatenate
cells A,B and C

Have I done something wrong?

Cheers & thanks so much for your help.

Amanda
Johannesburg



"Toppers" wrote:

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