We help IT Professionals succeed at work.

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

M_Patton
M_Patton asked
on
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
Comment
Watch Question

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
Rob HensonFinance Analyst

Commented:
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

Author

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.
Here it is, assuming that the job description is always Job 1, Job 2 etc.
Sorted on the numerical value after "Job ".
CubeListExampleA.xlsm

Author

Commented:
Great responses and explanations.  Some small in-code notes would nice.