Hi Dieneba,

Without a mock-up example of the final output you'd like to see, I can't give you step by step. e.g. in that file there are subtotals, totals, summaries etc. Usually when extracting data you only want the individual items because you should be using tools like PivotTables etc. to do the summarising. If you have subtotals and totals in your source data then you can easily double count. I don't know if you want the totals/summaries at the bottom and I don't know how those figures came about because everything is hard coded.

That said, sheets 2000 through to 2020 are all the same format, so these can be consolidated relatively easily (once you know the answers to the above questions). I already gave you the example queries that shows the steps (in the applied steps pane) to append sheets 2000-2020 in a previous forum question. This assumes you want all the data, including subtotals, totals etc. More on that in a moment.

However, sheet 2021 has a different layout, so it would need a separate query to get it into a tabular layout that can then be appended to the tabular layout for sheets 2000 - 2020. For sheet 2021 I would refer to lesson 4.15 Unpivot to get the data in a tabular layout, which you can then append (see lesson 3.04) to the other query for sheets 2000-2020.

In terms of excluding the subtotals and totals, you would need a lookup table that classified the item numbers in column A into those that are subtotals/totals and those that are not. You can then merge the two tables and bring in the classification column, which you can then use to filter out the totals.

Hope that points you in the right direction.

Mynda

Thanks Mynda

I will need to read your response quietly and take the time to do each of your suggestions step by step.  My final objective is to create a graph to show the revenue per country over the years.  So I would need one table showing the revenues for 2017 to 2021. Then I would like to put this revenue vs the country's GDP and the country population.  That is the final objective.  What I am struggling with is the way the table is laid out.    I do not need the expenditure at this stage.  I would need columns from B88-B106, then H88 to AJ 106 for the revenue. This would be from 2017 to 2019.

For 2021 B148 to AJ169

I do not want you to do it for me but explain the process.  I will apply lesson 3.04 fro 2017-2019 then lesson 4.15 for 2021 as you suggested. 

But that is the point. How do you decide which method to use for which table?  

Thanks, Mynda

Regards

Dieneba