Attendance Roster to Main Roster

[DGebert]

I am building a workbook for my organization. What we are doing is creating a digital file that has all of our business meeting attendance for each month of each year. A single sheet will be for 1 year. Take year 2020. It will then have all the names of the members in Column A and the months in row 2. I want to create a master sheet that pulls the names from each sheet (2020, 2019, 2018 etc.) and puts it in a single Master Sheet without duplicating any names from the previous years.

Thank you!

 

[SanjayGMusafir]

Update your Current excel platform and Version to give you a solution that works on your system.

Also share XL2BB of your sample data and the final output you need to avoid any confusion and repeated efforts.

 

[DGebert]

Update your Current excel platform and Version to give you a solution that works on your system.

Also share XL2BB of your sample data and the final output you need to avoid any confusion and repeated efforts.

The data below is in individual sheets. The Sheet Name is in Cell A1 for each sheet and named accordingly.

The goal is to pull the names across multiple sheets. This is a sample, we will have a sheet for 1937 to present time moving forward. The months are not a factor in my problem. They will not be moved to the master sheet. I only need the names to come over. I also do not want any duplicates of names such as Kevin A being on all 3 years while David B is only in year 2018. It would also be nice if it auto alphabetized the names in the masterlist.

Thank you hope this helps. Was not able to get XL2BB to work.

 

[skillilea]

Can you share the workbook?

 

[SanjayGMusafir]

Are these Structured tables?

 

[DGebert]

Are these Structured tables?

No but I can make them tables

 

[SanjayGMusafir]

I tried my best to find a solution. VSTACK is the answer to your problem but Excel 2012 doesn't have this function.

Still the link is here -

Unique values from multiple ranges

To extract unique values from more than one range at the same time, you can use the UNIQUE function with the VSTACK function. In the example shown, the formula in cell H5 is: =UNIQUE(VSTACK(range1,range2,range3)) Where range1 (C5:C16), range2 (D5:D15), and range3 (F5:F13) are named ranges.

exceljet.net

Another solution I could find is here using VBA -

How to Create a List of Unique Values from Multiple Worksheets in Excel

How to Create a List of Unique Values from Multiple Worksheets in Excel - You can frequently find yourself in a position where you need to combine and analyse data from other worksheets if you work with large Excel workbooks with numerous pages. One frequent task is to remove any duplicate...

www.tutorialspoint.com

Hope this thread catches eye of someone who is better than me. I shall also learn in process.

Posting XL2BB for any expert who visits this post. At least it gives them a starting point -

Book1
	A	B	C	D	E	F
1	Members		Members	Jan	Feb	Mar
2	New 1		New 1
3	New 2		New 2
4	New 3		New 3
5	New 4		New 4
6	New 5		New 5
7	Old 1
8	Old 2		Members	Jan	Feb	Mar
9	Old 3		New 1
10	Old 4		New 2
11	Old 5		Old 1
12			Old 2
13			Old 3
14
15			Members	Jan	Feb	Mar
16			Old 1
17			Old 2
18			Old 3
19			Old 4
20			Old 5
Sheet1

Cell Formulas
Range	Formula
C8:F8	C8	=C1:F1
C15:F15	C15	=C1:F1
Dynamic array formulas.

 

[skillilea]

To be clear, you want to loop through all your worksheets and capture the names in column A and write out the unique names, sorted, in a master list?

 

[Fluff]

How many rows of data do you have on each sheet?