0

Column A has product SKU for a shipped unit. Column B has waybill # for that shipped unit. Column C has status of that shipment (Delivered, In-Transit, Delayed etc.).

Because more than one unit ships under the same waybill (it's in the same box) there are many rows with duplicate waybill #s. For example,

Product SKU | Waybill # | Status
12345 | 356498712 | In-Transit
98765 | 356498712 | In-Transit
65432 | 123456789 | Delivered

I have hundreds of products being tracked based on the waybill #s and some waybill #s are associated with 30 or more products being shipped in the same box. (Each waybill # = 1 box)

So, if I change the status of the first product in the example above from In-Transit to Delivered, how can I have Excel automatically change the other units associated with the same waybill # to also show Delivered?

I've tried Googling this but it's hard to make a search term that doesn't return all sorts of unrelated but similar results. I'm getting results that point to counting dupes, highlighting dupes etc. But nothing to instruct on what I'm looking to do.

Norm Robichaud
  • 7
  • 3
  • Have a look at the techniques shown here : https://stackoverflow.com/q/55854654/4961700 – Solar Mike Apr 26 '19 at 08:45
  • Welcome to SO. With just 1 formula you can't. You need 2 datasets, 1 like the one you posted, and another list of just Waybill and Status, so each Waybill appears just once. Then in your original dataset, you can link the status of each waybill with [VLOOKUP function](https://support.office.com/en-us/article/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1). So the idea: your original dataset with a formula that searchs the status of each waybill in your second list, handmade, where you select the status of each Waybill just once. – Foxfire And Burns And Burns Apr 26 '19 at 10:56

1 Answers1

0

I've made an easy example, hope you can adap it.

Your data is kind of like this:

enter image description here

An easy and no complex solution would be creating a second list of just Waybills and status, like the one below (I created this data in a new sheet called WAYBILLS):

enter image description here

Then, in the data with Sku Codes, in column STATUS I have this formula in C2:

=VLOOKUP(B2;WAYBILLS!$A$2:$B$6;2;FALSE)

And dragged down all. So now the status of each product SKU is linked to second seet WAYBILLS with this formula.

So if I Change something in sheet WAYBILLS it will autoupdate.

enter image description here

enter image description here

I've uploaded a sample to my Gdrive if you want to check it out: https://drive.google.com/open?id=1NMR_zPsjGWiKj3Qdk_0SULoe_U2-ZLQe

Hope you can adapt this to your needs.

Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38
  • First off, THANK YOU! I managed to tweak the VLOOKUP so that it will do what I wanted, but when I drag it down, it only does the first 20 rows and then it gives me a #N/A for the rest. Not sure what's going on with that. – Norm Robichaud Apr 29 '19 at 21:51
  • Nevermind... I found the fix. I needed to 'lock' the table array (A2:B32 vs $A$2:$B$32) . – Norm Robichaud Apr 29 '19 at 22:14
  • @NormRobichaud Glad to hear you could fix it. If this answer helped, please, mark it as correct so the question can be closed. – Foxfire And Burns And Burns Apr 30 '19 at 08:35