Find matching Cells, then copy data over to another Worksheet 1

[ScorpioX]

ScorpioX (MIS)
Ok here we go. Not sure I'm explaining this right. So I will try the best I can.
I have Two Worksheets within the same Workbook. One is named Sheet1 the other is Sheet2. Sheet1 Has six columns (A, B, C, D, E, F)with 1000 rows. Sheet2 Has three columns (A, B, C) with 2000 rows. I would like to search Sheet2 column A, for the matching value in Sheet1 column D. If Sheet2 column A matches a cell in Sheet1 column D. Copy the data from Sheet2 column B and C into Sheet1 column E and F. Then delete the entire row from Sheet2. Can this be done?

Any help would be appreciated greatly,
Scorpiox

 

[SkipVought]

hi,

I might not use VBA. You could use a simple MATCH() lookup function to determine which values do not match, filter on the #N/A!, unmatched results, and DELETE the unmatched rows all at once.

Then use lookups to return the data you want to the other sheet.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ScorpioX]

Hi Skip,
I'm kind of new to this. Well actually really new not kind of. Anyway. I don't think I really get what you're saying.
===================================================================
Sheet1
ColumnA ||ColumnB ||ColumnC ||ColumnD ||ColumnE ||ColumnF
Model ||Date ||MachineName ||UserName ||Blank ||Blank

With roughly 1000 rows of the data above, extracted from SMS.
===================================================================
===================================================================
Sheet2
ColumnA ||ColumnB ||ColumnC
UserName ||EmployeeNumber ||Department

With roughly 1500-2000 rows or the data above, extracted from AD.
===================================================================
So I need to compare Sheet1 ColumnD to Sheet2 ColumnA. When a match is found copy Sheet2 ColumnB and ColumnC to the matching row in Sheet1 ColumnE and ColumnF. All data must remain in Sheet1 even if it does not have a match from Sheet2. Sheet2 rows can be deleted after a match is found.

I'm not even sure this can be done!
ScorpioX

 

[SkipVought]

Why not join using MS Query on UserName?

faq68-5829

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ScorpioX]

Hi Skip,
I did actually try that but it does not seem to be working correctly.
This is what I tried. All it does though is copy everything over. It does not seem to be matching just importing everything.

SELECT `Raw_Data$`.User, `ADUsers$`.EmployeeNumber, `ADUsers$`.Department FROM {oj `Raw_Data$` `Raw_Data$` LEFT OUTER JOIN `ADUsers$` `ADUsers$` ON `Raw_Data$`.User = `ADUsers$`.User}

Thanks,
ScorpioX

 

[SkipVought]

What your query sais is get ALL rows from raw data and matches only from ADUsers.

Is that what you wanted?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ScorpioX]

Yes I think so.
ADUsers has the data that needs to be copied into Raw_Data based on matching Raw_Data ColumnD to ADUsers ColumnA.

ScorpioX

 

[SkipVought]

Well a query does not COPY anything!

You will do your query, on a third sheet, referencing the data in the other two sheets as tables, using [SheetName$].

The resultset is then returned to the third sheet which contains the desired results. The other two sheets remain UNCHANGED, or you could delete them, but it would seem to me that you probably will import new data into one or bothe of the original sheets and then you can simply REFRESH the existing QueryTable object that is embedded in your third sheet.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ScorpioX]

Thank you Skip! I got it working! I was making it way to complicated and your advice worked like a charm. I'm now using a query it works awesome.
Thanks again,
ScorpioX

 

[SkipVought]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]