Get unique list based on date-location


Attached is an example. Sheet1 has 3 columns of data. Date, Name, Location

What I need is on sheet 2 to dynamically generate a list of the unique names by location for any date entered in the "date" block.

I think this would work best if it were a formula just so it is more "dynamic" and can update without having to run a macro each time the date is changed, however, I could make a macro solution also work.

Thanks in advance,

Who is Participating?

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

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.

Rob HensonFinance AnalystCommented:
How about a Pivot Table?

That can give as below:
Count of Location            
Location      Name      Total
G1               Bob      8
                 Jim      5
                 Joe      5
                Rob      8
               Sam      6
                Tim      15
               Tom      7
G2               Bob      3
                 Joe      6
               Rob      4
G3                 Art      3

Rob H
Rob HensonFinance AnalystCommented:
That didn't format very well, see attached.
swjtx99Author Commented:
Hi Rob,

Thanks for the reply. I should have mentioned I'd already tried a pivot table. It's not a good solution for what I am trying to do. I don't need a total count and I'd have to "refresh-all" every time I change the date and adds a lot to file size.

Once I get the list of names, I'll need to add some columns after that and that would also be problematic with a Pivot Table.


JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

ProfessorJimJamMicrosoft Excel ExpertCommented:

please find attached solution for your workbook

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
swjtx99Author Commented:
Wow! This is great!

Thanks Professorjimjam
ProfessorJimJamMicrosoft Excel ExpertCommented:
you are welcome swjtx99

thanks for the feedback
Rob HensonFinance AnalystCommented:
You beat me this time Prof. I was travelling.
ProfessorJimJamMicrosoft Excel ExpertCommented:
:-) Rob,

You catched and answered almost all of today's questions in Excel topic area

Had to get this one;-)
swjtx99Author Commented:
Not sure if this is still active but I am running into a bit of a performance problem on the working file. I used the formula in about 40 rows and there is about a 5-7 second delay when I change the date. Was wondering if maybe a VBA solution might work better?

Maybe this is another separate question altogether.


ProfessorJimJamMicrosoft Excel ExpertCommented:

UDF user defined functions  can be almost twenty times slower than built-in Formulas.

i would not recommend using UDF when result can be achieved by Formula.

i think perhaps, you are using entire column as lookup reference and that will surely slow down the calculation time.

if you want to avoid the whole column reference in formula, i suggest you use Tables which are dynamic and will speed up your calculation time, instead of whole column reference.  or alternatively if not Tables, then dynamic range
swjtx99Author Commented:
Hi Professorjimjam,

You are correct. I did try using whole column and that was really bad. My data set is 22,000 and grows daily so I did think of using a dynamic reference. How would I do that? I thought of using a cell to count the rows, then reference that cell in the formula instead of $C$2:$C$50000 how would I write that? $C$2:"$A$1" ? <wag


ProfessorJimJamMicrosoft Excel ExpertCommented:
I would recommend using table which is also dynamic.

Then let's aay your table name is Table1 and c column name is Its header name then you type like this instead of that C2:C50000

Rob HensonFinance AnalystCommented:
With formula you have a couple of options


Start point - the first cell at top of range
Offset Rows / Columns - if the starting point of your data is known, eg  C1 in your example, these can be zero
Height - this is where the count of rows comes in
Width - as it is only one column then set to 1

Other option is INDIRECT but less flexible. You build a string of text to create your range.
swjtx99Author Commented:
Thank you ProffessorJimJam and Rob,

I appreciate the advice and thank you both for taking the time to respond. I'll give it a go and If I can't come up with a workable solution, I'll make it another question someone can earn some points from.


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.