Looping through a column and calculate frequency of values from another column.

[gongadzem]

I am looking for ways to loop through column "Categories 1" and for each group calculate frequency from column "Categories 2"

Categories 1	Categories 2
A	Apple
B	Orange
A	Grape
C	Apple
C	Watermelon
A	Grape
B	Orange

It should yield something like this:

A: Apple 1,

    Grape 2

B: Orange 2

C Apple 1,

   Watermelon 1

It would be easier to break it up manually and then use frequency tables as long as number of categories is small. Though, I have over hundred different categories in "Categories 1" and 15-20 in "Categories 2." The dataset is larger than 500,000 records. I've looked into macros, but couldn't find any well explained examples. Any help is much appreciated.

[atcodedog05]

Hi @gongadzem 

Here is how you can do it.

Workflow:

Hope this helps : )

[gongadzem]

That looks awesome, thank you. Is there a way then to separate the table into 2 sub tables:

One would be "Categories 1" by "Categories 2"

A: Apple 1,

    Grape 2

B: Orange 2

C Apple 1,

   Watermelon 1

The other one "Categories 2" by "Categories 1"

Apple: A 1,

           C 1

Orange: B 2

Grape : A 2

Watermelon: C 1

[gongadzem]

That looks awesome, thank you. Is there a way then to separate the table into 2 sub tables:

One would be "Categories 1" by "Categories 2"

A: Apple 1,  Grape 2

B: Orange 2

C Apple 1,  Watermelon 1

The other one "Categories 2" by "Categories 1"

Apple: A 1, C 1

Orange: B 2

Grape : A 2

Watermelon: C 1

[atcodedog05 · Accepted solution]

Hi @gongadzem 

Here is how you can do it.

Workflow:

Hope this helps : )

[gongadzem]

Worked like a charm. Thank you.

[atcodedog05]

Happy to help : ) @gongadzem 

Cheers and have a nice day!