Creating List of Distinct Values with 1 condition for very large data set

[Jayy2121]

Hello,
I've used this site and forum extensivly to solve issues for a dashboard I'm building, but can't find a good solution to the following:

Creating a list of distinct values (removing all duplicates) when a condition is met. I know there are ways to do this with Index formulas, but my data has 170,000+ data sets so the calculations I've tried get bogged down when trying to run it.

Since this will be a dynamic scorecard sent to many people, it can't contain vba, pivot tables, or filters that the recipient would need to run or update. My goal is that once updated data is dropped into the dashboard, the formulas will do the rest.

The formula should look at the result of a market drop down list on another sheet to define the market to get the list for.

Example: For the data below I will have selected San Diego market and need to generate a list of all distinct TSMs in that market, keeping in mind there are 170,000+ rows. If I select a different market, the results wil update.

I hope I havn't reached the limits of what Excel can do.

I'm a first time poster so please be kind if I didn't follow standard protocal.
Thanks in advance!!!

Jayy


Sample data:

[TABLE="class: grid, width: 128"]
<TBODY>[TR]
[TD]Market</SPAN>[/TD]
[TD]TSM</SPAN>[/TD]
[/TR]
[TR]
[TD]Los Angeles Mkt</SPAN>[/TD]
[TD] TSM: JOHN MARKARIAN</SPAN>[/TD]
[/TR]
[TR]
[TD]San Diego Mkt</SPAN>[/TD]
[TD] TSM: CARLOS GONZALEZ-CEJA</SPAN>[/TD]
[/TR]
[TR]
[TD]San Diego Mkt</SPAN>[/TD]
[TD] TSM: CARLOS GONZALEZ-CEJA</SPAN>[/TD]
[/TR]
[TR]
[TD]San Diego Mkt</SPAN>[/TD]
[TD] TSM: CARLOS GONZALEZ-CEJA</SPAN>[/TD]
[/TR]
[TR]
[TD]San Diego Mkt</SPAN>[/TD]
[TD] TSM: CARLOS GONZALEZ-CEJA</SPAN>[/TD]
[/TR]
[TR]
[TD]San Diego Mkt</SPAN>[/TD]
[TD] TSM: SHAEFER WALL</SPAN>[/TD]
[/TR]
[TR]
[TD]San Diego Mkt</SPAN>[/TD]
[TD] TSM: CARLOS GONZALEZ-CEJA</SPAN>[/TD]
[/TR]
[TR]
[TD]San Diego Mkt</SPAN>[/TD]
[TD] TSM: CARLOS GONZALEZ-CEJA</SPAN>[/TD]
[/TR]
[TR]
[TD]San Diego Mkt</SPAN>[/TD]
[TD] TSM: SHAEFER WALL</SPAN>[/TD]
[/TR]
[TR]
[TD]San Diego Mkt</SPAN>[/TD]
[TD] TSM: SACHARY_CR NARANJO</SPAN>[/TD]
[/TR]
[TR]
[TD]San Diego Mkt</SPAN>[/TD]
[TD] TSM: SHAEFER WALL</SPAN>[/TD]
[/TR]
[TR]
[TD]San Diego Mkt</SPAN>[/TD]
[TD] TSM: SHAEFER WALL</SPAN>[/TD]
[/TR]
[TR]
[TD]San Diego Mkt</SPAN>[/TD]
[TD] TSM: SHAEFER WALL</SPAN>[/TD]
[/TR]
[TR]
[TD]Inland Empire Mkt</SPAN>[/TD]
[TD] TSM: ALPHA OWENS</SPAN>[/TD]
[/TR]
[TR]
[TD]Las Vegas Mkt</SPAN>[/TD]
[TD] TSM: JAMARR OWENS</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL span=2></COLGROUP>[/TABLE]

 

[Jayy2121]

Aladin,
My initial thought was to avoid adding hundreds of thousands of additional cells of data by adding a helper column at the record row level for each of the 8 regions, but your method looks like it keeps things fairly simple and I can place them far enough to the right that data will never interfere with it.

Let me give this a shot and see if it does the trick.

Thanks!!

 

[Aladin Akyurek]

Aladin,
My initial thought was to avoid adding hundreds of thousands of additional cells of data by adding a helper column at the record row level for each of the 8 regions, but your method looks like it keeps things fairly simple and I can place them far enough to the right that data will never interfere with it.

Let me give this a shot and see if it does the trick.

Thanks!!

The set up trades off memory against speed. Moreover, the formulas which are used are very fast.

 

[Jayy2121]

Wow that works perfectly!!!!
The only thing that will take some time is dragging that formula down 170,000 lines without crashing Excel.

Thanks sooo much!!

 

[Aladin Akyurek]

Wow that works perfectly!!!!

Great.

The only thing that will take some time is dragging that formula down 170,000 lines without crashing Excel.

Wouldn't double click on AutoFill help here?

Thanks sooo much!!

You are welcome.

 

[Jayy2121]

"Wouldn't double click on AutoFill help here?"


Maybe I wasn't patient enough, but when I double clicked to autofill, it froze up Excel while it was working. I gave it about 5min and then needed to use Excel for something else so force quit out. Maybe I'll try again when I have more time and shut off calculations until the Autofill is complete. Or else I'll just go 5k rows at a time or so.

This dashboard is already getting bogged down calculation time and I have still have much more to go. I may need your advice in the near future on how to make it more efficient or if it's possible to only have it calculate at certain times (once again without macros)

Thanks again for your help!!

 

[Aladin Akyurek]

"Wouldn't double click on AutoFill help here?"


Maybe I wasn't patient enough, but when I double clicked to autofill, it froze up Excel while it was working. I gave it about 5min and then needed to use Excel for something else so force quit out. Maybe I'll try again when I have more time and shut off calculations until the Autofill is complete. Or else I'll just go 5k rows at a time or so.

This dashboard is already getting bogged down calculation time and I have still have much more to go. I may need your advice in the near future on how to make it more efficient or if it's possible to only have it calculate at certain times (once again without macros)

Too many charts, conditional formatting, volatile formulas with Indirect, Offset, inefficient look ups, etc. tend to degrade performance

Thanks again for your help!!

You are welcome.