cross tab to flat table

nparsons75

Well-known Member
Joined
Sep 23, 2013
Messages
1,254
Office Version
  1. 2016
Does anyone know a quick and easy way to convert a cross tab table into a flat table so I can analyse my data better in a pivot table?
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
That's funny, I was just reading that solution. It's an older version than I have though so struggling to grasp it. "Multiple consolidation ranges" etc. Will read through again.
 
Upvote 0
I have attached a file to try and describe exactly what i need, maybe best to create a new post as i have maybe drifted from the requirement slightly, not sure if that is against the rules or not though.

https://www.dropbox.com/s/6cje7jxegcxl7aa/crosstab%20to%20pivot.xlsx

I have a table (cross tab) I think, which has approx 20 new rows added daily. I have slicers that select different variables. I need the pivot table to display the selected slicer options so i can create multiple charts.

eg.

Each variable will show the sum of downtime for each column etc and will change depending on the slicer options selected.

I am struggling to create the pivot table, I think due to it being cross tabulated. (but not sure this is the reason).
 
Upvote 0
The technique I posted works for that data. You will need to filter and delete the blanks in the table that's created.
 
Upvote 0
I have tried it, and it worked to a degree. I have the pivot table of which I double clicked, which then created a new table of data, BUT, it contains hundreds of rows, I need it to be group by reason if that is possible?
 
Upvote 0
I got a table like this:


Excel 2010
ABC
1RowColumnValue
2operator 1Air System
3operator 1Air System
4operator 1Air System
5operator 1Assisting on other m/c
6operator 1Assisting on other m/c
7operator 1Assisting on other m/c
8operator 1Changeover / Setting up
Sheet4


You will need to delete the rows with blanks in column C and change the headings in A1:B1.
 
Upvote 0
I have the same now. Is there a way to group by column B? So in the table as above there would be 1 air system, 3 assisting on other m/c and 1 for changeover, all showing the sum value in C?
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,703
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top