Consolidate data from multiple worksheets in a single worksheet

[datadane]

Good afternoon..

1st Post: Helping out a friend with a mentor program, but I need help compiling live data from 100 sheets into one sheet. Here is what I have:

I have 100 sheets in a Microsoft Excel workbook (saved to a OneDrive folder) that use identical column headers in each table. See example table template below:

Mentor ID	Mentor	Mentor Email	Are You a Returning Mentor?	Mentee ID	Mentee Name	Mentee Email	Mentee Department
1	John	john@mrexcel.com	Y	1009	Bill	bill@mrexcel.com	Finance
2	Mike	mike@mrexcel.com	N	1006	Winston	winston@mrexcel.com	IT
7	Rob	rob@mrexcel.com	N	2006	Matt	matt@mrexcel.com	Staffing
9	Erin	erin@mrexcel.com	N	2001	Will	will@mrexcel.com	IT

If possible I would need assistance to develop an Excel formula to display the results of column A, B, and C (Mentor ID, Mentor Name, and Mentor Email) of each table into a single table. The final product would contain only unique values and will omit empty cells.

In a normal situation I would just use PowerQuery to easily combine the tables and columns, but the data exists on an accessible OneDrive Excel sheet and needs to as live as possible. Any help is appreciated. I am open to provided any additional context if needed.

 

[Dave Patton]

The following may help.
You can test with a small number of sheets. Edit the range if necessary.
The longer formula may be useful.

• put a blank sheet named Start before the relevant sheets
• put a blank sheet named End after the relevant sheets
• put the relevant range in the formula to include your information

Try the following formula =VSTACK(Start:End!A2:C100)


=LET(v,VSTACK(Start:End!A2:C100),FILTER(v,INDEX(v,,2)<>""))

 

[datadane]

LET(v,VSTACK(Start:End!A2:C100),FILTER(v,INDEX(v,,2)<>""))

Thank you for your assistance, this one had me stumped for hours. I modified it slightly to remove duplicate returns.

=LET(v,VSTACK(Start:End!A2:C100),UNIQUE(FILTER(v,INDEX(v,,2)<>"")))

 

[datadane]

My apologies one more follow up question @Dave Patton how would I go about limiting or filtering the return of Columns A, B, and C based on mentors with a value of 'N' in Column D?

 

[Dave Patton]

=LET(v,VSTACK(Start:End!A2:D100),UNIQUE(FILTER(v,INDEX(v,,4)="N")))