Finding Data in one sheet & transfer to another

[Guest]

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

 

[Tom Ogilvy]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.