Creating a list from a large table

Help!
I am new to the Forum, I joined after spending hours banging my head against the keyboard - I am by no means an Excel expert...!

What I am trying to do is create a list from a large table (see attached). I have 2 columns (A and B) listing 'Asset' and 'Area' and then 2 rows (1 and 2) listing 'type of task' and 'task'. This forms a table with entries in the cells (from C3 onwards) denoting frequency of each task at each location, i.e. daily, monthly, 6 monthly and annual. This gives me quite a large table which is quite confusing. Can I create a formula to sort all the daily activities which will give me a list of the 4 corresponding properties 'country', 'location' (in columns A and B) and 'type of task', 'task' (in Rows 1 and 2)? Then do the same for the different frequencies populated in the table, 'monthly', '6 monthly' and 'annual' ???

Really appreciate any help :-)

I've tried it with an advanced filter and that works. The result is a sheet with only 'daily' frequencies. It is only that this result is still a big table.

Could you please upload a 'before' and 'after' sheet.

Hi,
One possible approach involves creating a reverse pivot table, so you end up with a flat list of all entries.

Here's a step by step:

First some data preparation

- remove rows 19 and 31 because they are empty and will break a continous table.
- insert a new column after column B and a new row after row 2
- in the new column C create labels for each row with the formula starting in C4

=A4&"|"&B4

- copy the formula down the table, then copy the column and paste special values into column C

- in D3 put the formula

=D1&"|"&D2

- copy across, then copy the whole row and paste special values on top of the formulas.

So far for data preparation. Now we can use a "reversed pivot table" to separate the data into individual rows.

1. Click a field inside the table, one of the "daily" cells is good.
2. Select "Data - PivotTable and PivotChart Report"
3. The wizard opens. In step 1 select "Multiple consolidation ranges" and click Next
4. in step 2a, select "I will create the page fields" and click Next
5. in step 2b, select the range 'By Asset'!$C$3:$BZ$39 then click Add and Next
6. In step 3 select "New Worksheet" and then click the Layout button
7. Drag the Row and Column fields away from the layout, so the only field that remains in the layout is "Count of Value" in the Data area. Click OK and then Finish.
8. Now you see a teensy, tiny pivot table with just one value in B4.
9. Double click the totals value in B4
10. Excel will create a new sheet with three columns: Row, Column and Value. Row and Column hold the concatenated labels we created earlier. Value holds whatever value was at that specific combination of row and column label.
11. Insert a new column after B and a new column after A.
12. Run a Text to Columns on Column A with delimiter "|", then run a text to columns on the (now) column C with delimiter "|". Confirm to overwrite existing values

You should now have in Column

A - Asset
B - Area
C - Type of task
D - Task
E - frequency

You can now sort and filter to your heart's content to extract just the jobs you want to show.

Hi,

did you make sure that the table did not have empty rows?

The flat file in Sheet2 does not match the data in the "By Asset" tab. Are you sure that Sheet2 and the pivot table were created from the "By Asset" tab in its current state?

I just used your sheet "By Asset" as I found it in the attached file and followed the process I described above (using the "|" pipe symbol as a delimiter). The result is attached. Note that there are no empty rows in Sheet4, no bold formatting, etc, just the data.

does that suit?

(file zipped to save bandwidth)

Great. Glad that was helpful. Could you mark the thread solved, please?

cheers