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]

 

[Slizer6893]

I know you said you used Index formulas before but it should be the fastest way to avoid VBA and Pivot Tables. Even though an output with VBA is going to beat it every time.

So I made this example for you its in Google Docs because its easier for me to share. https://drive.google.com/file/d/0B2fr4qZAYHuRQUlBTy1ZVG40WE0/edit?usp=sharing

Formulas Below

The column to find where the market exists =IFERROR(MATCH($D$1,OFFSET($A$1:$A$200,D2,0),0)+D2," ")

The column to pull in names where the Name is present : =IFERROR(INDEX($A$1:B200,$D3,MATCH($E$2,$A$1:$B$1,0)),"")

Try it out with your data, (TEST DATA first). I didn't make it pretty but it works so mess with it how you can to make it fit your style. This should work, the column just needs to be long enough to fit all the names, same for the column referencing the row. (You can hide this onto a helper sheet and just have what you want on the scorecard?

 

[Jayy2121]

Thanks Slizer, I'll give it a shot and let you know.
I know VBA is the best way to go, but I can't trust my audience to use it propoerly (or not disable it when opening).

Thanks again!!

 

[Slizer6893]

Not a problem. Let me know if you get errors or if you want me to explain the formulas more. I actually got this formula idea from a poster a few months ago who helped me on something so passing on the knowledge feels good.

 

[Jayy2121]

Slizer, your first formula worked great. The second one didn't seem to work when I applied it to my worksheet.
But I realized that your results did not remove duplicate which is something I'm hoping to acheive. What I'm working with is a huge list of 170,000 accounts, their markets, and TSMs in each market. I'm hoping to get a dynamic list of the distinct TSMs depending on which region is selected. My plan is to feed another dropdown with this list of distinct TSMs which will, in turn, feed more formulas.

I feel like I'm about 10 levels beyond my ability here so your help is appreciated.

 

[Slizer6893]

So you like the way it pulled all the information but when you select something like...San Diego Mkt. It will only pull unique names from the list. So it may be something like 400 different names. Basically all the formula needs is a way to filter out the results before posting. This can be done with a helper worksheet if you are into making a hidden sheet. Basically the helper sheet would post the results then the sheet which would be presented to whomever would only see the unique names.

A few questions
-Would this sheet just be given to someone to select a market and then they would get the results they desire or are you using it to create the list then adding that information elsewhere?
-If you can lock a hidden sheet a helper sheet will do all this for you and teach you a few new tricks
-Can you provide an example of the kind of output that would be desired? other than the one you posted (Of course removing an confidential information)

I will work on this more on my downtime.

 

[Jayy2121]

Slizer,
I have a 25mb data pull of 170,000 rows and 26 columns. I'm trying to design a front page dashboard that would allow market supervisors to pick their market and TSM to see sales opportunities they should persue with that TSM. I have the formulas built to pull the opportunities and they are tied to a spot for each drop down (market and TSM). But the TSM cell is static until I can build a drop down that allows them to choose their TSM.

There are 62 markets and 800 TSMs and I can't create a static list of TSMs for each market as the TSMs are liable to change with every data pull depending on personelle assignments. So my goal is to have a list of distinct TSMs generated based off the region selected (on a different sheet than the actual data or dashboard) that can feed my TSM drop down list.

One other note if it helps is that while each Market has 10-15 TSMs, each TSM only reports to a single market.

I can certainly add helper pages and already use a scratch sheet to feed formulas. But the file size and calculation time have to stay somewhat reasonable (I'm already taking about 10 seconds to calculate)

If you really want the full sheet, it would take me some time to scrub out confidential info and it would be big, but if that's the only way, I'll do it.

Let me know your thoughts and thanks again.

 

[Aladin Akyurek]

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[/TD]
[TD]TSM[/TD]
[/TR]
[TR]
[TD]Los Angeles Mkt[/TD]
[TD] TSM: JOHN MARKARIAN[/TD]
[/TR]
[TR]
[TD]San Diego Mkt[/TD]
[TD] TSM: CARLOS GONZALEZ-CEJA[/TD]
[/TR]
[TR]
[TD]San Diego Mkt[/TD]
[TD] TSM: CARLOS GONZALEZ-CEJA[/TD]
[/TR]
[TR]
[TD]San Diego Mkt[/TD]
[TD] TSM: CARLOS GONZALEZ-CEJA[/TD]
[/TR]
[TR]
[TD]San Diego Mkt[/TD]
[TD] TSM: CARLOS GONZALEZ-CEJA[/TD]
[/TR]
[TR]
[TD]San Diego Mkt[/TD]
[TD] TSM: SHAEFER WALL[/TD]
[/TR]
[TR]
[TD]San Diego Mkt[/TD]
[TD] TSM: CARLOS GONZALEZ-CEJA[/TD]
[/TR]
[TR]
[TD]San Diego Mkt[/TD]
[TD] TSM: CARLOS GONZALEZ-CEJA[/TD]
[/TR]
[TR]
[TD]San Diego Mkt[/TD]
[TD] TSM: SHAEFER WALL[/TD]
[/TR]
[TR]
[TD]San Diego Mkt[/TD]
[TD] TSM: SACHARY_CR NARANJO[/TD]
[/TR]
[TR]
[TD]San Diego Mkt[/TD]
[TD] TSM: SHAEFER WALL[/TD]
[/TR]
[TR]
[TD]San Diego Mkt[/TD]
[TD] TSM: SHAEFER WALL[/TD]
[/TR]
[TR]
[TD]San Diego Mkt[/TD]
[TD] TSM: SHAEFER WALL[/TD]
[/TR]
[TR]
[TD]Inland Empire Mkt[/TD]
[TD] TSM: ALPHA OWENS[/TD]
[/TR]
[TR]
[TD]Las Vegas Mkt[/TD]
[TD] TSM: JAMARR OWENS[/TD]
[/TR]
</tbody>[/TABLE]

Will the output be restricted to just one output sheet? The output would look like autofiltering: a market chosen, the associated distinct list created. When another market chosen, the new list will replace the old in the same area.

 

[Jayy2121]

Correct Aladin. There will be only one drop down location for market and that will feed only one distinct TSM list.
To be fair, there are 8 Regions and each region will have it's specific Market list. But I'm hoping once I solve for one Region, the same formulas will apply to the other regions, just pointing to a different Market drop down.


Let me know any other questions and thanks again.

 

[Aladin Akyurek]

Correct Aladin. There will be only one drop down location for market and that will feed only one distinct TSM list.
To be fair, there are 8 Regions and each region will have it's specific Market list. But I'm hoping once I solve for one Region, the same formulas will apply to the other regions, just pointing to a different Market drop down.


Let me know any other questions and thanks again.

[TABLE="width: 689"]
<TBODY>[TR]
[TD="class: xl63, width: 143, bgcolor: transparent"]Market
[/TD]
[TD="class: xl63, width: 252, bgcolor: transparent"]TSM
[/TD]
[TD="class: xl66, width: 54, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl65, width: 30, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 26, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 26, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 386, bgcolor: transparent"]San Diego Mkt
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 143, bgcolor: transparent"]Los Angeles Mkt
[/TD]
[TD="class: xl64, width: 252, bgcolor: transparent"]TSM: JOHN MARKARIAN
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]3
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 143, bgcolor: transparent"]San Diego Mkt
[/TD]
[TD="class: xl64, width: 252, bgcolor: transparent"]TSM: CARLOS GONZALEZ-CEJA
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]List
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 143, bgcolor: transparent"]San Diego Mkt
[/TD]
[TD="class: xl64, width: 252, bgcolor: transparent"]TSM: CARLOS GONZALEZ-CEJA
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]TSM: CARLOS GONZALEZ-CEJA
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 143, bgcolor: transparent"]San Diego Mkt
[/TD]
[TD="class: xl64, width: 252, bgcolor: transparent"]TSM: CARLOS GONZALEZ-CEJA
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]TSM: SHAEFER WALL
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 143, bgcolor: transparent"]San Diego Mkt
[/TD]
[TD="class: xl64, width: 252, bgcolor: transparent"]TSM: CARLOS GONZALEZ-CEJA
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]TSM: SACHARY_CR NARANJO
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 143, bgcolor: transparent"]San Diego Mkt
[/TD]
[TD="class: xl64, width: 252, bgcolor: transparent"]TSM: SHAEFER WALL
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 143, bgcolor: transparent"]San Diego Mkt
[/TD]
[TD="class: xl64, width: 252, bgcolor: transparent"]TSM: CARLOS GONZALEZ-CEJA
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 143, bgcolor: transparent"]San Diego Mkt
[/TD]
[TD="class: xl64, width: 252, bgcolor: transparent"]TSM: CARLOS GONZALEZ-CEJA
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 143, bgcolor: transparent"]San Diego Mkt
[/TD]
[TD="class: xl64, width: 252, bgcolor: transparent"]TSM: SHAEFER WALL
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 143, bgcolor: transparent"]San Diego Mkt
[/TD]
[TD="class: xl64, width: 252, bgcolor: transparent"]TSM: SACHARY_CR NARANJO
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 143, bgcolor: transparent"]San Diego Mkt
[/TD]
[TD="class: xl64, width: 252, bgcolor: transparent"]TSM: SHAEFER WALL
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 143, bgcolor: transparent"]San Diego Mkt
[/TD]
[TD="class: xl64, width: 252, bgcolor: transparent"]TSM: SHAEFER WALL
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 143, bgcolor: transparent"]San Diego Mkt
[/TD]
[TD="class: xl64, width: 252, bgcolor: transparent"]TSM: SHAEFER WALL
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 143, bgcolor: transparent"]Inland Empire Mkt
[/TD]
[TD="class: xl64, width: 252, bgcolor: transparent"]TSM: ALPHA OWENS
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 143, bgcolor: transparent"]Las Vegas Mkt
[/TD]
[TD="class: xl64, width: 252, bgcolor: transparent"]TSM: JAMARR OWENS
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

A:B houses the data of interest.

C1 must house a 0.

G1 displays a choice from the data validation list on that spot.

C2, just enter and copy down:

=IF($A2=$G$1,IF(ISNUMBER(MATCH($B2,$B$1:B1,0)),"",LOOKUP(9.99999999999999E+307,$C$1:C1)+1),"")

G2, just enter:

=LOOKUP(9.99999999999999E+307,C:C)

G4, just enter and copy down as far as needed:

=IF(ROWS($G$4:G4)<=$G$2,LOOKUP(ROWS($G$4:G4),C:C,B:B),"")

Note. Actually, a simple VBA code should look at G2 and copy the above formula G2 times from G4 on downwards.