Change Qty if 2 cell criteria are met

Microsoft Office excel 2007 - upgrade
November 4, 2010 at 05:35:17
Specs: Windows XP, Core Duo 2GHz / 2 Gigs RAM
Hi. I'm a 'dabbler' in VBA (I know, Dangerous! :-) and I've created a macro to custom format raw data concerning customer orders where I work. By looking up forums and learning as I go I've created a very useful tool for report creation / distribution but the report goes to several upper management and higher employees and there's one part that, while mutually understood, is very annoying.

Basically, the only unique key in the report is the work order number. I also have Sales Order(S.O.) and Line Item of the Sales order. There can be from 1 to 20 work orders per S.O. Line Item but whatever database engine SAP uses doesn't distinguish the individual orders. So, the result is something like this:

S.O. Line Item W.O. Qty
123456 10 111111222 3
123456 10 111111333 3
123456 10 111111444 3

So you can see, the Qty states 3 in every cell when it should actually be 1 whenever the SO and Line Item are the same and the W.O.# is different. I need to know how to parse through each row and change the Qty to the correct quantity dependant on the other 2 cell values. If you need an example of the raw data and then the formatted report please let me know an email address to send to.

Thank you in advance.


See More: Change Qty if 2 cell criteria are met

Report •

November 4, 2010 at 06:36:16
First, when posting data/tables, please use the pre tags to line up your columns to make the data easier for us to read. If you need instructions on how to use the pre tags, just ask.

       A           B                C           D
1   S.O.       Line Item           W.O.        Qty
2  123456         10            111111222       3
3  123456         10            111111333       3
4  123456         10            111111444       3

Based on your example data, this formula will return 1 in Column D for each item.
If you change a W.O. to match another one, you'll get 2 next to both of them:

Put this in D2 and drag it down:


Report •
Related Solutions

Ask Question