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?
 
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
 
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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
 
M_PattonAuthor Commented:
Great responses and explanations.  Some small in-code notes would nice.
0
 
Ejgil HedegaardCommented:
With code comments.
CubeListExampleA.xlsm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.