How To Create A Unique List From Multiple Sheets Using Excel Formula

Question

I am working on a payroll sheet, and I need to extract Unique Employee ID from multiple sheets in the workbook and place them in the same workbook in another sheet.

Although I am able to create a formula, to get those Unique List however I am not able to make it dynamic, since every month I shall be importing a new sheet in the workbook and that should be taken into consideration, which is not working out with my formula.

I have tried using the INDIRECT Function to dynamically refer all the sheets but in vain may be I am doing something wrong here. I know it can be done with Power Query but I dont want to change the structure of the database also its possible with VBA, but I'm reluctant to it, specifically want to accomplish it using Excel Formula.

The below formula which I have used in Master_List Cell A2

="ID_"&SORT(SUBSTITUTE(UNIQUE(
FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",,Blad1:Blad3!A2:A1000)
,",","</b><b>")&"</b></a>","//b")),"ID_","")+0)

I tried using this as SHEETS Defined Name within the formula, but it gives #REF Error

=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")&T(NOW())

The above formula, is used for grabbing other sheets data for calculations, tried implementing it within the formula by wrapping it within INDIRECT function, but doesn't work, I know why is n't will it possible to make it dynamic or is there any workaround. I hope am able explain. Thank you for the effort and time.

Note: This is a sample data, created for the query.

Answer 1

If you insist on formulae, here is what I did to make this work:

• I created a name formula in the name manager: SHEETNAME. It refers to: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
• Assuming you have at least two sheets (a 'Master' and any other sheets have ID's in column A;

Now I used in A2 in the masterlist:

=UNIQUE(FILTERXML("<t><s>"&REDUCE("",SEQUENCE(SHEETS()-1,,2),LAMBDA(a,b,TEXTJOIN("</s><s>",,a,INDIRECT(INDEX(SHEETNAME,b)&"!A2:A100"))))&"</s></t>","//s"))

Note1: I assumed you have only got ID's in the range of A2:A100 to keep it rather simple.

Note2: This use of TEXTJOIN() can hit it's limits rather quick.

Note3: You could try to nest a 2nd UNIQUE() that would make sure that each iteration handles as little as records as possible. This would hopefully make sure that the limits of TEXTJOIN() aren't hit as quickly.