Creating a list from data on several sheets

Copper Contributor

Hi all,

Is it possible to have excel retrieve data entries on other worksheets in the same workbook and create a single list of them? I have a workbook with five worksheets, four of which have similar data imported from another source, each comprising of a report concerning a different locale. On each of these four sheets there are data entries (some of which are text, others are numbers or other such values that have been calculated or concatenated from data from columns further along). The number of row entries varies. What I want to do next is combine the data of all the four location sheets and their columns A, B, F and G into a master list on the fifth worksheet to help me create a .csv to import the date into yet another application. I would need all the data entries from Sheet1's column A to be retrieved with Sheet2's data from column A following them, followed by the data entries from column A from Sheet3 etc. I do not want to lose duplicates. I also need the data from the corresponding sheets and rows from columns B next to them and the same with the data from columns F and G. 

 

I'd like to achieve this with formulas due to the fact that the sheet will be updated with new data imports monthly. It would speed the process and eliminate the risk of errors if all I could do is copy+paste the appropriate columns into the sheets of each of the locations and then Excel would automatically compile a master list of these. I'm beginning to suspect this can't be done, what I'm struggling with is the fact that the amount of rows varies. Thus far the master list has been compiled by copying and pasting the necessary data matrixes from the location specific sheets onto the master list but I'm not liking the fact there is so much copying and pasting involved. Could anything be done about this? 

 

Attached an example of a very rough version of what I'm after.

1 Reply

Hi Eirko,

 

That could be done with Power Query, and perhaps without Sheet1,.. etc, if you don't need them, directly from data sources - depends on what they are.