Create a single-column list from a small cube of data in Excel

We have an Excel workbook with multiple pages to aggregate our credit card expenses.  There is a summary sheet and a sheet for each user's credit card statement which are pulled from their individual Excel workbook.  Each individual person's sheet has a list in cells C2:C26 of the jobs he/she worked on; not all lists will be the same length or contain the same jobs.  I am looking to generate a single list of unique values on the summary sheet.

I am including and example of the type of worksheet.  We would want to dynamically create the list in C2:C26 on the Summary sheet from the data on the other sheets' C2:C26.  VBA macros or formula language are acceptable.
CubeListExample.xlsx
M_PattonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
Try this macro.

The list can be sorted, but observe that Excel sort as text, so the order will be
Job 1
Job 10
Job 11
..
Job 2
..
Unless some special action are made to sort by the numbers.
But that is only important if the jobs are named as the example.
CubeListExample.xlsm
0
Rob HensonFinance AnalystCommented:
If all sheets are the same layout, you may be able to use a Pivot Table using "Multiple Consolidation Ranges".

The resulting Pivot Data may not be how you want it but it should give you a list of Job numbers.

Thanks
Rob H
0
M_PattonAuthor Commented:
Ejgil Hedegaard:
Thank you.  This does almost exactly what I need it to in the real world; except on the Summary worksheet I want it to start in a different range (say starting in K6 - I know that wasn't in my example).  Also, I would appreciate the mod to sort you referenced.
0
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Ejgil HedegaardCommented:
Here it is, assuming that the job description is always Job 1, Job 2 etc.
Sorted on the numerical value after "Job ".
CubeListExampleA.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
M_PattonAuthor Commented:
Great responses and explanations.  Some small in-code notes would nice.
0
Ejgil HedegaardCommented:
With code comments.
CubeListExampleA.xlsm
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.