Moving data from excel spreadsheet to spreadsheet based on present or absent criteria

Copper Contributor

Hi, this is a problem that I cannot solve myself. I have an excel spreadsheet which has unique customer codes in one column and a column next it which contains comments based on my phone conversation with each customer. These conversations took place last month and I now have a new spreadsheet for the current month which only displays the customers from last month who did not book in for my services. The new spreadsheet has about 50% less customers because half booked in for my services and they are removed from the new spreadsheet. What I need to do is transfer only the comments from the first spreadsheet to the second spreadsheet which match the unique customer codes. This is so I can follow up on those customers in the new month who did not book my services last month. 


My question is, is there a formula which I can apply to each new monthly spreadsheet which will move only the comments for the remaining customers across from last months spreadsheet?  

2 Replies

I found that IndexMatch does what I want it to do on a worksheet or between worksheets on different tabs. Excellent! However, I want two separate workbooks (separate excel files) to do this between. Is this possible?


Yes: open both workbooks in Excel.

You can then point to a range in a sheet in the other workbook when you create the INDEX/MATCH formulas.

When you're done, close (and if necessary save) the 'source' workbook. this will add the path of that workbook to the formulas in the 'target' workbook.

Finally, save the 'target' workbook.