Summarize multiple string fields independent of each other

[sowjanyayinti]

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.

[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 · Accepted solution]

Like so?

[Qiu · Accepted solution]

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

[sowjanyayinti]

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

[flying008 · Accepted solution]

Hi, @sowjanyayinti 

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

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.