SOLVED

Excel help pulling data to a new sheet by comparing data for matches in 2 other work sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-2475763%22%20slang%3D%22en-US%22%3EExcel%20help%20pulling%20data%20to%20a%20new%20sheet%20by%20comparing%20data%20for%20matches%20in%202%20other%20work%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2475763%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3EI%20have%20a%20workbook%20with%202%20worksheets%2C%20Sheet1%20and%20Sheet2.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20lookup%20values%20in%20Sheet1%2C%20Col%20E%20(Employee-E)%20and%20Col%20H%20(Ded-Code-E)%26nbsp%3B%20and%20find%20matches%20in%20Sheet2%2C%20Col%20C%20(EdmEmployee)%20and%20Col%20D%20(EdmDedCode).%26nbsp%3B%20If%20I%20find%20a%20match%2C%20I%20want%20to%20copy%20the%20entire%20row%20over%20to%20new%20Sheet3.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20uploaded%20test%20spreadsheet%20with%20a%20few%20rows%20for%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20VB%20macro%20in%20there%2C%20but%20don't%20know%20how%20to%20edit%20it%20to%20do%20what%20I%20want.%26nbsp%3B%20Pretty%20new%20to%20Excel%20and%20macros%2C%20so%20I'm%20a%20bit%20over%20my%20head%20here!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20a%20great%20day%3A)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERufus%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2475763%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2475959%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%20pulling%20data%20to%20a%20new%20sheet%20by%20comparing%20data%20for%20matches%20in%202%20other%20work%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2475959%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1086093%22%20target%3D%22_blank%22%3E%40Rufus_Bertrand%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFrom%20which%20sheet%20do%20you%20want%20to%20copy%20a%20row%20to%20Sheet3%3F%20From%20Sheet1%20or%20from%20Sheet2%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2475980%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%20pulling%20data%20to%20a%20new%20sheet%20by%20comparing%20data%20for%20matches%20in%202%20other%20work%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2475980%22%20slang%3D%22en-US%22%3EHI%20Hans%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20want%20to%20return%20row%20from%20Sheet2.%3CBR%20%2F%3E%3CBR%20%2F%3Ethanks!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2476566%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%20pulling%20data%20to%20a%20new%20sheet%20by%20comparing%20data%20for%20matches%20in%202%20other%20work%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2476566%22%20slang%3D%22en-US%22%3Ethanks%20Hans!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2478400%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%20pulling%20data%20to%20a%20new%20sheet%20by%20comparing%20data%20for%20matches%20in%202%20other%20work%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2478400%22%20slang%3D%22en-US%22%3EAmazing!%20That%20is%20what%20I%20was%20looking%20for%3F%3CBR%20%2F%3E%3CBR%20%2F%3EMind%20if%20i%20ask%20a%20couple%20of%20questions%20about%20the%20code%3F%3CBR%20%2F%3E%3CBR%20%2F%3Ethanks%2C%3CBR%20%2F%3ERufus%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi all,

I have a workbook with 2 worksheets, Sheet1 and Sheet2.  

 

I need to lookup values in Sheet1, Col E (Employee-E) and Col H (Ded-Code-E)  and find matches in Sheet2, Col C (EdmEmployee) and Col D (EdmDedCode).  If I find a match, I want to copy the entire row over to new Sheet3.

 

I've uploaded test spreadsheet with a few rows for reference.

 

I have a VB macro in there, but don't know how to edit it to do what I want.  Pretty new to Excel and macros, so I'm a bit over my head here!

 

Thanks in advance!!

 

Have a great day:)

 

Rufus 

12 Replies

@Rufus_Bertrand 

From which sheet do you want to copy a row to Sheet3? From Sheet1 or from Sheet2?

HI Hans,

I want to return row from Sheet2.

thanks!
Amazing! That is what I was looking for?

Mind if i ask a couple of questions about the code?

thanks,
Rufus
ok, first, thank you very much for your help!!

I have a question about the following code:
' Create dictionary of unique helper values
Set dct = CreateObject(Class:="Scripting.Dictionary")
For curRowLookup1 = 2 To lastRowLookup1
dct(lookupSheet1.Range("L" & curRowLookup1).Value) = 1
Next curRowLookup1

* this is the first time i see the 'Scripting.Dictionary' object, so you're creating an Object called 'dct', then you search Sheet1 from row=2 to last row, and assigning it the value in col 'L'. Is '1' the key associated with it?

@Rufus_Bertrand 

For the Dictionary object, see:

Dictionary object 

Excel VBA Dictionary – A Complete Guide 

The line

dct(lookupSheet1.Range("L" & curRowLookup1).Value) = 1

does one of two things:

  1. If there is no entry with key equal to lookupSheet1.Range("L" & curRowLookup1).Value, it creates a new entry with that key, whose item (value) is 1. The 1 is not important, I could have used any value. What matters if that an entry with the cell value as key is created.
  2. If there is already an item with key equal to lookupSheet1.Range("L" & curRowLookup1).Value, its item is set to 1, so effectively nothing happens.
So in answer 1, the actual key is the value in column 'L', like '129|6000' for first record on Sheet1?
And in answer 2, if there is already a value in dct equal to '129|6000', it returns a '1'?
best response confirmed by Rufus_Bertrand (Occasional Contributor)
Solution
Great!
I'll check out the links you provided to learn more about Dictionary object usage.

thanks so much for your help Hans!!
Have a great day:)