Matching cells then copy another cell from one spreadsheet to another

[andrewbush]

I hope this makes sense because I have been having a difficult time trying to figure out a solution to this problem.

I have two spreadsheets and what I want is if there is a match between the two spreadsheets, I want a macro or formula to copy the sales column data from the spreadsheet that just has agent number and sales to the spreadsheet that has the agent number and blank cell for sales.

Example

Spreadsheet that has just the agent number and sales:

Spreadsheet that has agent number and sales but no data in the sales column:

Result desired for first row of the second spreadheet:

agent_number
25003

sales
311745.5

I would like this result to happen automatically for the rest of the rows of the second spreadsheet.

Any help with this would be greatly appreciated. Thank you.

 

[mirabeau]

Are you happy with a macro?

Sub fillcol()
Dim d As Object, e
Set d = CreateObject("scripting.dictionary")
For Each e In Sheets("Sheet1").Range("A1").CurrentRegion.Resize(, 1)
    d(e.Value) = e.Offset(, 1)
Next e
With Sheets("sheet2")
    For Each e In .Range("H1").Resize(.Range("H" & .Rows.Count).End(3).Row)
        If d.exists(e.Value) Then e.Offset(, 1) = d(e.Value)
    Next e
End With
End Sub

 

[andrewbush]

Thank you for the code for the macro but I'm not sure how to use it. It has been a while since I have coded a macro with vba. Could you tell me how to use this code?

Thank you

 

[mirabeau]

Open your workbook.

The code assumes that your first sheet (with the two columns) is Sheet1 and the other is Sheet2.

Press Alt F11 to open the Visual Basic window. From the menu at the top click Insert, then Module.

Copy the code I posted and paste it into the Module window.

With you cursor located anywhere in this code (in the Module window), press F5.

This will run the macro (code) and, all going well, the result you request should appear on your second sheet.

 

[andrewbush]

Thanks for the steps.

I followed them and got this error:

Run-time error '429'

ActiveX component can't create object

When I click debug on the error it hi-lights this part of the code:

Set d = CreateObject("scripting.dictionary")

Do you have any ideas what would be causing this?

Also does it matter that I'm using Excel that is part of Office for Mac 2011?

Should I be using Excel 2010 for Windows?

Thank you again for the help.

 

[mirabeau]

Do you have any ideas what would be causing this?
Also does it matter that I'm using Excel that is part of Office for Mac 2011?
Should I be using Excel 2010 for Windows?

Hi Andrew,

Yes the Mac does make quite a difference.

Although I'm not familiar with Office for Mac 11, I did use the 2004 version for a while. Microsoft had seemingly "crippled" Excel and its associated coding/macro facilities in that Mac version so it was only of limited use. Later Microsoft produced a 2008 version which had even more limited, read-only, coding ability and was supposed to rely on some pre-built functions. This was a Procrustean bed (from Greek mythology, look up its interesting origins and meaning on Google if you're not familiar with it) which I found almost totally useless to me and I found no purpose in having anything to do with it.

So I deleted Office for Mac 2008 entirely, installed Parallels and VM Fusionware and used the Windows version of Excel on my Mac. (This was the only Windows program I was using at the time. It was interesting too that my Macbook Pro could run coded Excel for Windows faster than any portable Windows-based machine at that time. From test by PC World.). Although Microsoft apparently conceded they'd mend their ways in future versions of Office for Mac, I'd had enough by that time, and don't really know what facilities Office for Mac 11 currently has.

From your experience, it seems still limited. The Scripting Dictionary, which I used to solve your problem, is fast, concise and convenient on Windows, but still seems to be useless on the Mac.

It's not that hard to produce an equivalent solution to your problem using only the basic operations such as counting, sorting and comparing, which must operate on Mac Excel even if I don't know what else does. But this would be slower and longer and I currently don't have that much time to look at it again.

So, I'm sorry I can't be of much help to you in the meantime. I can only suggest that you do run the problem using Excel for Windows if you have access to it (any recent version will do) and my VBA code macro should then work fine.

Otherwise, some of the excellent contributors to this forum should be able to help. So please post again saying you're still wanting a solution.

But .. state your operating system