Summarize multiple string fields independent of each other

I am drawing a blank over this and want to throw this here for some help. Let me know how silly it is of me to not be able to figure this out.... I swear I am missing something right in front of my eyes.

How do I get counts of each field in a file independent of other field in the same file? If I have more than 25+ fields in an excel sheet and I want to get counts of values in each field - summarize tool is not cutting it as I have to use too many summarize tools.

I am attaching an excel file where the first tab is some sample data and second tab is the output I am trying to get. Any help is greatly appreciated.

Summarize Sample Data.xlsx

Help

Accepted answers

caltang

Like so?

For Sowjan.yxzp

Qiu

@sowjanyayinti
I made some twist based on the idea of @apathetichell to comply with your specified format.

1102-sowjanyayinti.yxmd

flying008

Hi, @sowjanyayinti

My solution like @Qiu , but added some tools for fully meet your needs as field order and row queue.

录制_2023_11_02_15_08_34_574.gif

Input
Color	Fruit	Vegetable	Characters	Shows
Red	Apple	Tomato	Chandler	Friends
Blue	Blueberry	Blue Corn	Chandler	Everybody Loves Raymond
Blue	Blueberry	Blue Corn	Phoebe	West Wing
Green	Kiwi	Spinach	Phoebe	Newsroom
Yellow	Mango	Squash	Phoebe	Lupin
Red	Cherry		Chandler	Peaky Blinders
Red	Cherry	Tomato	Chandler	Suits


Output
Color	Color_Count	Fruit	Fruit_Count	Vegetable	Vegetable_Count	Characters	Characters_Count	Shows	Shows_Count
Red	3	Apple	1	Tomato	2	Chandler	4	Friends	1
Blue	2	Blueberry	2	Blue Corn	2	Phoebe	3	Everybody Loves Raymond	1
Green	1	Kiwi	1	Spinach	1			West Wing	1
Yellow	1	Mango	1	Squash	1			Newsroom	1
		Cherry	2		1			Lupin	1
								Peaky Blinders	1
								Suits	1

******

Hi, @Qiu , your flow is a nice play ! 👍

All comments

apathetichell

transpose. summarize tool group by name/count (or count distinct value) - that will give you a count of distinct values in each column.

caltang

Like so?

For Sowjan.yxzp

Qiu

@sowjanyayinti
I made some twist based on the idea of @apathetichell to comply with your specified format.

1102-sowjanyayinti.yxmd

sowjanyayinti

Thank you all. I didn't think of the tile tool. Appreciate your quick responses.

flying008

Hi, @sowjanyayinti

My solution like @Qiu , but added some tools for fully meet your needs as field order and row queue.

录制_2023_11_02_15_08_34_574.gif

Input
Color	Fruit	Vegetable	Characters	Shows
Red	Apple	Tomato	Chandler	Friends
Blue	Blueberry	Blue Corn	Chandler	Everybody Loves Raymond
Blue	Blueberry	Blue Corn	Phoebe	West Wing
Green	Kiwi	Spinach	Phoebe	Newsroom
Yellow	Mango	Squash	Phoebe	Lupin
Red	Cherry		Chandler	Peaky Blinders
Red	Cherry	Tomato	Chandler	Suits


Output
Color	Color_Count	Fruit	Fruit_Count	Vegetable	Vegetable_Count	Characters	Characters_Count	Shows	Shows_Count
Red	3	Apple	1	Tomato	2	Chandler	4	Friends	1
Blue	2	Blueberry	2	Blue Corn	2	Phoebe	3	Everybody Loves Raymond	1
Green	1	Kiwi	1	Spinach	1			West Wing	1
Yellow	1	Mango	1	Squash	1			Newsroom	1
		Cherry	2		1			Lupin	1
								Peaky Blinders	1
								Suits	1

******

Hi, @Qiu , your flow is a nice play ! 👍

sowjanyayinti

@flying008 - how did you get the record id in the summarize tool. I see only Name and value in the summarize tool. Also, I tried to figure out how you did your workflow and I am not getting the counts at the end. How did you get the counts without selecting them in the 'select' tool at the end?

Can you share your workflow please?

sowjanyayinti

Ignore my previous comment. I figured it out how you got the counts but still not sure how you were able to output record id in the summarize tool:).

flying008

Hi, @sowjanyayinti

FYI.

录制_2023_11_06_08_15_09_598.gif

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703