Consolidate data from multiple worksheets in a single worksheet

datadane

New Member
Joined
Feb 15, 2024
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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 IDMentorMentor EmailAre You a Returning Mentor?Mentee IDMentee NameMentee EmailMentee Department
1Johnjohn@mrexcel.comY1009Billbill@mrexcel.comFinance
2Mikemike@mrexcel.comN1006Winstonwinston@mrexcel.comIT
7Robrob@mrexcel.comN2006Mattmatt@mrexcel.comStaffing
9Erinerin@mrexcel.comN2001Willwill@mrexcel.comIT

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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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)<>""))
 
Upvote 1
Solution
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)<>"")))
 
Upvote 0
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?
 
Upvote 0
=LET(v,VSTACK(Start:End!A2:D100),UNIQUE(FILTER(v,INDEX(v,,4)="N")))
 
Upvote 0

Forum statistics

Threads
1,224,245
Messages
6,177,395
Members
452,773
Latest member
D P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top