How to create a dynamic unique list from multiple sheets/column?

Hi Experts,

I have an excel problem which is the yearly report. And I have 1 excel file of each month report, and I want to combine those file into once summary report. I cannot consolidate/merge the files since every single months I have different items description.

However, I make an excel problem as in attached which I want to create a unique list in the summary report.

I hope the formula can used in office 2019 as I don't have office 365

Thank you!

Happy New Year!

Originally Posted by dflak

You sir, are suffering from what I dub "Human Resources Excel Spreadsheet Disease." The typical HR setup is one spreadsheet per year, with 12 tabs, one for each month and anywhere from 28 to 31 columns going across each spreadsheet depending on the month and employee information going down the rows on each sheet.

HRESD (HR Excel Spreadsheet Disease) is characterized by doing data entry in the format of what you want the final report to look like.

The trick is to do data entry in normalized format. You can fill in the data in normalized format as it happens and you do not have to add data for items not sold, made or whatever. Normalized data is a LOT easier to work with using pivot tables and formulas ESPECIALLY if you use Excel Tables.

The attached workbook shows data in normalized format and a pivot table that does what you are trying to do. Enter in the daily sales in the table. You can have multiple entries per day like on 2/5 and 2/17.

The beauty of normalized data is the report still work as data is entered. so when you move onto May, it will show up in the pivot table.

Hello Sir,

Thank you for your help.
I understand about combining the data and use the pivotable. However, I want to know if there is any formula to get it done such as FILTER, UNIQUE/VSTACK like this....

Again, thank you Sir

Hi Everyone,

I already found the solution as follows:

=IFERROR(IFERROR(IFERROR(INDEX(Table3[Items],MATCH(0,COUNTIF($B$3:B3,Table3[Items])+(Table3[Items]=""),0)),INDEX(Table2[Items],MATCH(0,COUNTIF($B$3:B3,Table2[Items])+(Table2[Items]=""),0))),INDEX(Table4[Items],MATCH(0,COUNTIF($B$3:B3,Table4[Items])+(Table4[Items]=""),0))),"")

Thank you!

Originally Posted by HansDouwe

FILTER, UNIQUE and VSTACK in not available in Excel 2019.

Hi Han,

Yes sir, I found solution. However, I would highly appreciate if you can share solution for office 2021 as well.

Thank you!