Merging two spreadsheets to override data. By common identifier or ID

[kstenson]

Hello, I have a spreadsheet with reporting data, in which each row has a unique case number/identifier. The report is missing an important field of who owns the account. Because the report changes—with new rows added and some removed each week— I can't just paste over the top.

When the new report is run, I want to update/override the data from the previous report, with the exception of my added column with the account owner's name. I'm hoping to find matching rows based on the unique case number and update the row if it still exists and if it doesn't, remove the row, as well as add any new rows from the most current report.

Apologies, I'm not great with Excel and don't know how to go about this and am looking for direction, in layman's terms.

I tried attaching a screenshot but it kept erroring out. Here it is: screenshot

 

[kstenson]

post a link to the shared excel file with representative example of source data and expected result, use OneDrive, GoogleDrive or any similar

Thank you. Here are the two example files. Let me know if it's not clear.

Merging Spreadsheet Example Files - Google Drive

drive.google.com

 

[sandy666]

I see you've excel 2016 so why there is xls extension (excel 2003) ?
and Power Query will work on Mac or still not?

 

[kstenson]

I see you've excel 2016 so why there is xls extension (excel 2003) ?
and Power Query will work on Mac or still not?

Not sure why they were .xls. Sorry, I uploaded .xlsx versions to the same folder.

I don't believe Power Query is available yet.

 

[sandy666]

I don't believe Power Query is available yet.

so I can't help, sorry
Have a nice day

 

[ajetrumpet]

stenson,

have you solved this issue yet? I haven't been able to respond to anyone here because of the funeral and a few other things. Now I'm at a monastery but I am able to get back here. Do you still need assistance? I don't know if I can help, but I can try. Let me know if you are still looking to get something accomplished.

 

[kstenson]

stenson,

have you solved this issue yet? I haven't been able to respond to anyone here because of the funeral and a few other things. Now I'm at a monastery but I am able to get back here. Do you still need assistance? I don't know if I can help, but I can try. Let me know if you are still looking to get something accomplished.

Hi, Adam. Yes, this is still an open question. One gal suggested Power Query but I don't believe that is available on Mac yet.

 

[petertenthije]

I do think a VLOOKUP formula will suffice, if I understand your problem correctly.

In Sheet1, type the following formula in cell H2:
=VLOOKUP($B2,'Sheet2'!$B:$C,2,false)

Then copy said formula down to every row with data.

 

[kstenson]

I do think a VLOOKUP formula will suffice, if I understand your problem correctly.

In Sheet1, type the following formula in cell H2:
=VLOOKUP($B2,'Sheet2'!$B:$C,2,false)

Then copy said formula down to every row with data.

When adding that formula to the doc "Original Report-New.xlxs" to cell H2 and drag it down to every row with data, they all display "#N/A". It's referencing sheet 2, which the file does not have. To test, I adding a sheet 2 and copied the data from "Updated Report-New.xlxs", but the cells still display #N/A on sheet 1. These links are to live spreadsheets, so you can see how it looks with the formula added or if you want to try anything.

Apologies if I misunderstood—I'm not very good with Excel yet.

 

[petertenthije]

I failed to recognise you were working with two different sheets. I did not open your attachments.

Se below in red the update:
=VLOOKUP($B2,'[Updated Report-New.xlsx]Sheet 2'!$B:$C,2,FALSE)


EDITED TO ADD, my version of Excel uses ; instead of ,
I replaced the ;'s