2
I'm having trouble determining GCD from a dataset. I'm trying to determine the quantity of items in a box of any given item in a warehouse without physically looking at each item on the shelves.
Here's a sample dataset that I created from sales orders.
0 Part1
36 Part1
12 Part1
18 Part1
6 Part1
0 Part1
6 Part1
36 Part1
36 Part1
20 Part2
5 Part2
15 Part2
20 Part2
25 Part2
0 Part2
30 Part2
So looking at this I can see that Part1 comes in boxes of 6. Part2 comes in boxes of 5. This is the return I am looking for.
I feel like I'm close with
=GCD(SUMPRODUCT(--(A$1:A$16),(B$1:B$16=B1)),A1)
or
=GCD(SUMPRODUCT(--(A$1:A$16)*(B$1:B$16=B1)),A1)
but it's not quite working.
Basically, I want something like SUMIF. A GCDIF function where =GCDIF(range,criteria,[GCD range])
Is this possible?
Peach_kefir
Posted 2018-08-10T13:48:54.760
Reputation: 25
To clarify for other readers and potential answers, are all of the part numbers grouped together as in the question or are they a random list? Are there relatively few records and is the list static, so you can find group breaks and hard code them in the formula? Suppose you have only a few records for a part and they happen to be multiples of the same multiple (e.g., box of 5, but the records are 10 and 20). Will an answer of 10 be good enough? – fixer1234 – 2018-08-12T09:11:15.070
The part numbers are grouped together but there are more than 40,000 records so manually finding the group breaks is not an option. We will run into the problem of multiples of the same multiple resulting in a larger estimated box size than actual, but for this exercise the estimate is close enough and any errors can be fixed by the user creating purchase orders. – Peach_kefir – 2018-08-13T19:05:25.583