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 | ||
| 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.
CodePudding user response：
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.
CodePudding user response：
Starting from Excel 2016 you can use the "Get & Transform" capabilities.
In short, select your data, then use the "From Table" button under Data | Get & Transform". A new Query Editor window opens, select the first column, right-click and select "Unpivot other columns": you get a table with a row for each combination of row/column in your original data range. You may want to change column headers, then just close the query editor and load the unpivoted data.
For more info have a look at this article from Microsoft