Macro search one spreadsheet replaces in another spreadsheet

**sarahhe** · 01-28-2011, 12:31 AM

Hey guys, I`m trying to find a way to make a macro that will use the data in one spreadsheet and search another spreadsheet, once it finds a match it replaces the data. I have one data sheet (datasheet.xls) in column A under tactic, I need it to go to worksheet.xls search column D under tactic and once if finds a match, the data from the datasheet.xls selects that data from Cells A to L. And if there is no match, somehow make a note of it in the datasheet.xls, make it highlighted or somehow standout saying there`s no match. The problem is the tactic under column A will not be in sequence with the column D of the worksheet.xls, eg. tactic is in A3 on the datasheet.xls it can be on line D25 of the worksheet.xls. I have attached a zip file, in there is datasheet.xls and worksheet.xls. Thanks for helping guys.

**JBeaucaire** · 01-28-2011, 12:01 PM

1) Open both workbooks.

2) In Worksheet.xls on Sheet1 in cell P2, put this "Key" formula, then copy down your dataset.

=D2&"-"&TRIM(E2)&"-"&F2

This give your dataset a unique key for each row of data.

3) In Datasheet.xls on Sheet1 cell E3, put this formula, then copy down:

=INDEX([worksheet.xls]Sheet1!$G:$G, MATCH(LOOKUP(2, 1/($A$1:$A3<>""),$A$1:$A3)&"-"&TRIM(LOOKUP(2, 1/($B$1:$B3<>""),$B$1:$B3))&"-"&$C3, [worksheet.xls]Sheet1!$P:$P, 0))

You will get a #N/A message for tactics that don't match. The rest will match to the key column you added and bring over the matching value from column G.

**sarahhe** · 01-30-2011, 09:15 PM

JBeaucaire thanks for your help, but that's not what I want, I need for example tactic 109HD01T - Tele Follow Up in datasheet.xls which is in cell A8, I need it to find it in Column D of the worksheet.xls, but the tactics are not always in a particular order. After it finds a match, I want the range from A8 to L28 to copy over to the worksheet and replace the data. So datasheet A8:L28 and replace with D2 to O22. The thing is sometimes there are more rows added to that tactic or sometimes reps get deleted so data that's copied has to add or delete rows depending on the data from the datasheet. The range will depend on the tactic and the number of reps, column A is a merged column. Another example, tactic 100D938T - Healthcare Tele Followup eNurture 2010 in the datasheet.xls it's on A80:L84, I want that data to replace the data that's on the worksheet.xls, which is found in D52:O56. Thanks.