Putting data from multiple tables into one table... Can anyone assist?

Question

Good Afternoon,

Can anyone assist with some sort of search function that allows me to take data from multiple tables and put it all into one table. Lets say there is table 1, Table 2 and Table 3 and I want them all listed in a final table to be able to extract and manipulate that data. Is there a way to do this?

Some things to bear in mind which may assist...

There will never be line spaces within table 1, 2 and 3 (so for example if there is only 1 line in table 1, it will always be the first one, if there are two lines, it will always be the first two lines etc...).

A line will always be full i.e., if a line is in use, all 5 boxes will be filled (Example below would be A3,B3,C3,D3,E3 are all full, there would not ever be one missing)

As per the example below it may well be that Table 2 has more lines than table 1

Is there a way to systematically add the data to a final table, making sure that nothing is missed or duplicated?

Many Thanks!

Answer 1

Format each table as real table.

Format an Excel table - Office Support

Use Power Query to consolidate the data

Append queries (Power Query)

To analyze (e.g. sum up) the data use a Pivot table

Create a PivotTable to analyze worksheet data - Office Support

If you need further help please upload your file (maybe with anonymous data) on an online file hoster like https://www.dropbox.com and post the download link here.

Andreas.

Answer 2

Thanks Andreas,

Please find a link to the sample data.

I forgot to say, Can I create another column in the 4th table telling me what table the data was in? (I.e. Table 1, 2 or 3)

Thank you for helping.

https://1drv.ms/u/s!Aj277HgDsl7e_H6Yc2Mh7nRs_2Kj?e=4Mnt31

Answer 3

Hi,

It is ideal to stores these tables in 3 different files within a single folder. Also, remove merged and centred cells. Follow the steps shown in this video.

Answer 4

Maybe it is easier if you use a helper column and just copy & paste the data into a different sheet.

G1: Kind

G2: Table
G3: =IF(ISNUMBER(A3),IF(A2="Date",A1,G2),0)

drag G3 down.

Apply an Autofilter and filter out the 0 in column G.

Select all data rows from row 2 downwards and copy into a new sheet.

Andreas.

Answer 5

Hi Rob

You may find a Power Query solution to your problem in the link below https://we.tl/t-SPy5A47Mmr

Output

I hope this helps you and gives a solution to your problem.

Regards

Jeovany