Compare 2 worksheets and update one of them

[Closed]
Report
Posts
1
Registration date
Saturday March 12, 2016
Status
Member
Last seen
March 12, 2016
-
Posts
2782
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 2, 2021
-
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

Posts
2782
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 2, 2021
468
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,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!