Nov 06 2021 05:50 AM - edited Nov 06 2021 05:59 AM
Does anyone know a function or method in excel of getting data in a state from Illustration 1 to Illustration 2. Transform could be part of the solution but I need a way to consolidate rows and columns at the same time
Nov 06 2021 05:53 AM
Nov 06 2021 05:56 AM
Sure, if only you share sample file instead of screenshot
Nov 06 2021 06:11 AM
@clieboff How about a pivot table? See attached.
Nov 06 2021 06:12 AM
Thank you.
- Not exactly necessary, but it's better to transform source data into structures table (Ctrl+L or Cltrl+T staying within it)
- Right click on it and Get Data from Table/Range or Get Data from Sheet or like depends on your Excel version
- in Power Query editor select Service Level 2 column, Transform->Pivot Column, Value as value
- land result into Excel sheet
- adjust formats as needed
Alternatively you may create PivotTable based o your source, also in attached.
Mar 26 2023 11:20 AM
This is exactly what I was looking for to do!
Two columns with about 850 rows of data, VoterID and the Dates they voted in the last 5 elections. (Voter and Date columns.) The trick was to copy the VoterID and rename it Values, then rearrange columns such that I had VoterID in column A, Dates in column B and Values in column C (a copy of column A).
After creating a Table, I used Pivot using your "sample.xlsx" spreadsheet as an example.
I'm a slow learner, but I eventually got what I needed. Thank you so much for your help and patience!