Detect perfect switch

[Pierre]

Hello,

I am working on a file to detect switched quantities. Here is the problem, I receive goods to a central warehouse from many shops. Each shop declares a quantity sent for each item, when goods arrive at warehouse, quantities are scanned. It can happen that errors are made at the warehouse where some quantities which should be on shop A are assigned to shop B, creating differences.

For example for item A, shop A declared 30 units, shop B declared 10. At warehouse 25 units were declared to shop A and 15 to shop B. Shop A has -5 units and shop B +5 units.
I would like to have a formula (or at worst a macro) which can detect those "perfect" switches and make the correction.

I enclose file with some examples, knowing than in real life I hava hundreds of shops with hundreds of items...

Thanks in advance for help

 

[NARAYANK991]

Hi ,

I doubt that a formula-based solution will be fool-proof ; a VBA-based solution can be.

Can you upload a bigger file with say a thousand rows of data , with many stores and many items ?

Narayan

 

[Pierre]

Hi Narayank991,

I was thinking that an array formula might do the trick, but if my data are quite big it might not be good idea...

Here is a file with a thousand rows of data, if you have an idea, it will be more than welcome

 

[p45cal]

In the attached,

• column I contains the row number to pair with
• column F contains an adjusted Warehouse scan Qty from that paired row
• column I is derived from columns G & H

360 rows have been adjusted.
Many rows had many rows that they could have been paired with; they were paired with the first available in the list.

 

[Pierre]

Hello p45cal,
That's exactly what I needed! I had the feeling an array formula could work Thanks a lot for your help