Compare 2 worksheets and update one of them

kurtwm Posts 1 Registration date Saturday March 12, 2016 Status Member Last seen March 12, 2016 - Mar 12, 2016 at 09:23 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Mar 14, 2016 at 12:57 PM
I am a beginner Excel user and I hope I can get some help here. I am about to purchase a retail business. I have a copy of the master inventory list.

My plan is to physically scan the barcode of each item and log the number of items in inventory. Then take each item in my scanned worksheet and search for the matching (barcode) item in the master inventory list. When an item is found I wish to update column E in the master inventory list with the number of items scanned from my scanned worksheet. It is possible to have multiple entries of a barcode item. Therefore column E should be not copied but the inventory value added to column E.

When done I should be able to look at the master inventory sheet and compare d2 and e2 and see if the are the same or not. There are approximately 9000 items (rows) in each worksheet. I guess I could go fancy and turn one or the other value red if the numbers do not match, but that is mere than I need.

Lastly I wish to copy column D from my inventory list to the master inventory list column G. I hope that I can get some help here and thank you in advance for reading this.

The key columns in the master worksheet start in row 2:
A2 = barcode
B2 = item description
c2 = cost of each item
d2 = number of items in inventory
e2 = physically scanned number of items (starts initially with all zeros)
f2 = ignore
g2 = copied date/time stamp from my scanned worksheet column

there are more columns, but can be ignored for this purpose

The key columns in my scanned inventory worksheet are:
A2 = barcode
b2 = number of items physically scanned
c2 = ignore
d2 = date/time stamp scanned

1 reply

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Mar 14, 2016 at 12:57 PM
Hi Kurtwm,

To count the number of times you scanned a barcode, you can use the COUNTIF formula.

To compare column D with E and highlight differences, you can use Conditional Formatting.

Copy column D to G is a copy/paste action.

Give it a try and let us know if/where you get stuck.

Best regards,