Creating a unique list using multiple sheets - excel 1

[john434]

Hello,

I have created a report which is produced monthly within excel. This report is run for 5 services who's data is stored on five different sheets (i know this isn't ideal data management but all these sheets contain tens of thousands of rows and they wouldn't all fit into one sheet). I have a macro which runs a bunch of SUMPRODUCT formula's on this data and returns the results into tables.

Currently the report is constructed to report only high level information (basically the information is calculated for individual Assistant Directors). I have now been asked to produce this report at team level.

I feel that the best way of achieving this without hardcoding the values in VBA would be to create a unique list of teams and then use this list to create a drop down list with the Macro looking at this drop down list. The teams are contained within all five service sheets, so what i need is a formula that will compare all teams across the five sheets and return a unique list.

Hope that makes sense to everyone/anyone.

Cheers

 

[SkipVought]

Hi,

You can use MS Query to get a unique list in one table.

faq68-5829

Select Distinct TheFieldName From 
(
Select Distinct TheFieldName From Sheet1$
Union
Select Distinct TheFieldName From Sheet2$
Union
Select Distinct TheFieldName From Sheet3$
......
)

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[john434]

Fantastic idea Skip,

Have used MS Query before to get data from SAGE, but i'd never though of using it like that. Brilliant solution!

Thank you!!

Have a star for your time and trouble