Sorting Apples pt. 2 - Using a Macros

[lotien15]

Hi,

I'm trying to see to if its possible to build a macro or to simply use functions in the cells with the scenario mentioned below:

We have 3 types of apples (Fuji, Red, Gala). All have different set of apples that are each uniquely barcoded that need to be evenly distributed into 6 baskets. When it comes to uneven numbers divisible by 6, they need to be added back into one of the other baskets (in others words, would restart the count OR basket 1 & 2 would get two apples from Red); can't have part of an apple.

Fuji 6 Apples
Red 8 Apples
Gala 24 Apples

The goal would be to filter at the column for basket '1' and I would get all of the listed assigned apples from each person.

(Slightly different scenario with the same idea from another thread I had posted "Sorting Apples Questions - Using Macros")

 

[Fluff]

How about

=MOD(COUNTIFS(A$2:A2,A2)-1,6)+1

 

[lotien15]

That worked! THANK YOU SO MUCH!!!!!

I'm assuming if I decided to change the number of baskets I would change the '6' value to whatever X-baskets I would like it to be?
=MOD(COUNTIFS(A$2:A2,A2)-1,6)+1

Do you mind explaining the logic of how the function above how its read? I've been trying to figure out this problem for a while and couldn't get it right -- the functions used above are some I tried to use but couldn't get it to work.

 

[Fluff]

if I decided to change the number of baskets I would change the '6' value to whatever X-baskets I would like it to be?

That's right.
The countifs just gives a running count for the values in col A & the mod function return the remainder after dividing by 6

 

[lotien15]

So the COUNTIFS is differentiating the type of apples based on change of 'text' or is it counting based on the current row+the row before?

And you put '-1' because that is how you start off the first apple in basket 1, correct?

-- if you put "=MOD(COUNTIFS(A$2:A2,A2)1,6)+1" then it would start off at 2
-- if you put "=MOD(COUNTIFS(A$2:A2,A2)0,6)+1" then it would return it as all 1-------->(this is what i kept getting in the beginning; the constant '1' values)

 

[Fluff]

Its based on the values in col A & has noting to do with what row it's on.
The -1 is needed to prevent the formula from returning 0 instead of 6