greatest common divisor

Question

Dear Alteryx expert,

I am looking for an easy way to calculate the GCD of a string of numbers. To put my request in context, I have joined an excel spreadsheet where for two materials I have the volumes historically ordered. I would then like to generate per material what is the GCD of volumes to better understand whether we can see a pattern/ lot sizes.

Many thanks!

example.xlsx

estherb47 · Answer

@danilang   YES. Perfect optimization.

I love solving with you

Cheers,

Esther

danilang · Answer

Excellent solutions all!

I just like to propose a small optimization to @estherb47's solution.   You only have to generate rows up to the square root of the number as opposed to 1/2 the number.  Since sqrt(x)*sqrt(x) = x, you know that anything larger than sqrt(x) has to be multiplied by a number smaller than sqrt(x).

Dan

estherb47 · Answer

Hi @Jean-Remi

This is a really fun challenge, and I wanted to see if one could do it without macros or code.

My idea was to first generate all of the divisors, and then figure out a way to figure out which were common to all of the numbers, and finally the greatest of those.

So, first step was to calculate how many different records are for each material. If a divisor were common to all of those records, the counts would match (e.g., for the 34 records for material A, a divisor could only be common if it counts to 34 when matching up to the numbers)

Next, generate all possible numbers that can be divisors by simply generating rows for each record, from 2 up until the value of half the record.

Mod is a great function to determine if a number is a divisor. If the modulo of 2 numbers is a zero, then yes, otherwise no. Filter out all of the numbers that aren't divisors.

Match up the records to see common divisors, and then summarize how many times each of these numbers appear. If they appear the same number of times as records in the material, then they are common.

Another summary tool finds the max of these numbers per material. you could also sort and then use the sample tool to pull the first (or last) record per material.

Let me know if that helps

Cheers!

Esther

GCD.yxmd