count and display distinct values in excel rows

I have a table that looks like this

name	column1	column2	column3
name1	a	b	b
name2	c		a
name3	a	a	dd

I need to enter data in another table in this format:

name, label of the value, count occurances of value, label of the value, count occurances of value ...

name	label	count	label	count
name1	a	1	b	2
name2	c	1	a	1
name3	a	2	dd	1

I could copy data from the table1, if I could get it to list and display distinct values, somewhere in table1.

I tried using pivot table, but it expects data to be i columns, not in rows.

Is there any way this can be done in excel?

Thanks.

Solution

You could do this by using the Data Model and creating measures to display text values within the Value field of a PivotTable, but I think Power Query would be more efficient.

Load your data into Power Query. Data tab in the Ribbon > From Table/Range
Select the Name column, right-click, Unpivot Other Columns
Select the Name and Value columns, right-click, Group By.
New Column name = "Count", Operation = "Count Rows", Column will be blank.
Select the Value and Count columns. Go to the Transform tab in the ribbon, select Merge Columns under the Text Column section. Use whatever seperator you want, I chose colon.
Right-click the Name column, select Group By. Name the column whatever you like, and do a Sum operation on your newly Merged column.

Your resulting column will have an error. In the formula bar replace this code:

= Table.Group(#"Merged Columns", {"name"}, {{"MergedRows", each List.Sum([Merged]), type text}})

With this code, replacing MergedRows with your column header.

= Table.Group(#"Merged Columns", {"name"}, {{"MergedRows", each Text.Combine([Merged],":"), type text}})

Notice how my Text.Combine separator is the same colon as I used earlier to merge columns. Now you can selected your fully merged column, and choose Split Column under the Transform tab in the ribbon. Choose delimiter and colon.
Now you just need to clean up the column order and headers.