trying to extract unique values with a formula

[nmss18]

Hello,
I have a very large table in Sheet 1, in the following range A10:1:3068.
Column C has the company name, and there are multiple instances of the same company name in this column. (Columns E to I are part of a pivot table and I have various formulas in A thru D).

In column A I have a value indicating whether the company has 'exposure' (an internal meaning for our sales team). If the company has exposure, then the word exposure is indicated otherwise the cell is blank.

What I need to do is in Sheet 2, create a table that extracts a unique list of company names from column C only where it has the word 'Exposure' next to it in column A.

I would like this to be dynamic so if I change a value in the pivot table in sheet 1 the values in the table in sheet 2 will update accordingly.

I am figuring this is an index and match or perhaps a countifs but my head is stuffed.

Any ideas would be much appreciated.
Thanks,
nmss18

 

[KMacauley]

Hi nmss18,

Name your range of company names as "Company" (or whatever you want), and then use this formula:
{=IFERROR(INDEX(Company, MATCH(0, IF(MAX(NOT(COUNTIF($B$1:B1, Company))*(COUNTIF(Company, ">"&Company)+1))=(COUNTIF(Company, ">"&Company)+1), 0, 1), 0)), "")}


Note: You will have to fix the references

I have been using this formula for a few years now. The author of this formula is Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula | Get Digital Help - Microsoft Excel resource

Kim.

 

[nmss18]

Kim,
Thanks for the answer and the link. I will take a look at the link later, but in the meantime, I tried to use your formula but it didnt work. I created the range for the column with the company names but I am getting one company name repetitively.
What are the references that I have to adjust? Should I reference the 'Exposure' values that I have in columnA?

Thanks much,
nmss18

 

[Aladin Akyurek]

What is A10:1:3068 exactly? You want a unique list from column C, the elements of which correspond "exposure" in column A. Is this correct?

 

[nmss18]

Yes, that is what i am looking to do. ColumnA just identifies whether the company name in columnC has 'exposure'.
In column D -which is in the first column of a pivot table- I have the unique user names of this company. That is why I might see multiple instances of the company name in column C. The identification of 'exposure:' associated with the company name tells me that the user names in column D have not traded any market data with our firm hence they are at risk (or at 'exposure') for cancelation.At this stage I don't need to extract those names but perhaps later I might try.
Thanks much.
Nmss18

 

[KMacauley]

Kim,
Thanks for the answer and the link. I will take a look at the link later, but in the meantime, I tried to use your formula but it didnt work. I created the range for the column with the company names but I am getting one company name repetitively.
What are the references that I have to adjust? Should I reference the 'Exposure' values that I have in columnA?

Thanks much,
nmss18

Hi nmss18,

I missed (or forgot) the request for only showing the companies that have "Exposure", so I have changed my steps, though it is not the most elegant:

1. In column J, add the formula
   =IF($A2="Exposure", $C2, "")
2. On Sheet 2, create a formula in A2
   {=IFERROR(INDEX(Sheet1!$J$2:$J$3068, MATCH(0, COUNTIF($P$1:P1, Sheet1!$J$2:$J$3068), 0)), "")}
   
   Note: Do not type in the {}, but press Ctrl+Shift+Enter
   Note: You may need to change your sheet reference, that is if the sheet name is not Sheet1, then change it to the correct name
3. Drag this formula down to row 3068

Kim.

 

[nmss18]

Kim,
Still didnt work. First i realised that I could use your formula you specified in step on for my 'Exposure' column in column E (I moved it from column A, because I want to create this list in the same sheet so I just moved the whole pivot table over a few coklumns). So now in my first column (column E) I have a column with a header called Exposure and I created the entire length of the column into a range called 'Exposure'.

I then modified your formula to look like this:

{=IFERROR(INDEX(Exposure, MATCH(0, COUNTIF($L$1:L2, Exposure), 0)), "")}

But all I am getting is this:

Exposure

Exposure

Exposure

Exposure

Exposure

Exposure

Exposure

Exposure

Exposure

Exposure

Exposure

Exposure

Exposure

Exposure

Exposure

Exposure

Exposure

<colgroup><col style="width: 48pt;" width="64">
<tbody>


</tbody>

First of all what is your (original reference) in the match to COUNTIF($P$1:P1) - P1 happens to be blank and is on top of my pivot table. It just seems random and was wondering that perhaps you meant to reference another cell.
I feel we are very close and almost there....

 

[KMacauley]

Kim,
Still didnt work. First i realised that I could use your formula you specified in step on for my 'Exposure' column in column E (I moved it from column A, because I want to create this list in the same sheet so I just moved the whole pivot table over a few coklumns). So now in my first column (column E) I have a column with a header called Exposure and I created the entire length of the column into a range called 'Exposure'.

I then modified your formula to look like this:

{=IFERROR(INDEX(Exposure, MATCH(0, COUNTIF($L$1:L2, Exposure), 0)), "")}

But all I am getting is this:

Exposure

Exposure

Exposure

Exposure

Exposure

Exposure

Exposure

Exposure

Exposure

Exposure

Exposure

Exposure

Exposure

Exposure

Exposure

Exposure

Exposure

<tbody>
</tbody>

First of all what is your (original reference) in the match to COUNTIF($P$1:P1) - P1 happens to be blank and is on top of my pivot table. It just seems random and was wondering that perhaps you meant to reference another cell.
I feel we are very close and almost there....

Hi,

In the COUNTIF, you need to reference $L$1:L1, not $L$1:L2. I know it's blank (or a header or something), that's fine. It's just a counting point of reference. The first line has to "point" to the same line.

Kim.

 

[Aladin Akyurek]

Yes, that is what i am looking to do. ColumnA just identifies whether the company name in columnC has 'exposure'.
In column D -which is in the first column of a pivot table- I have the unique user names of this company. That is why I might see multiple instances of the company name in column C. The identification of 'exposure:' associated with the company name tells me that the user names in column D have not traded any market data with our firm hence they are at risk (or at 'exposure') for cancelation.At this stage I don't need to extract those names but perhaps later I might try.
Thanks much.
Nmss18

Column A consists of "exposure" when appropriate.

Column C houses company names with duplicates.

Column D houses "unique user names of company" (a column of some pivot table).

The above and what expires in the last exchanges thwart one's understanding of the problem which must be solved.

You could post a 5 row sample along with the results you want to see.

 

[nmss18]

Kim\Aladin
Sorry for the confusion. I'll provide an example. (I wanted to install Mrexcel HTML maker but the webserver appears to be offline).
Here is a modified version of my table in columns E to I:

Exposure	Not Traded- User count	Overall # Of Users in Firm	Client Firm	End User
	3	10	Amazing Growth	AKIRI@AMAZING
	3	8	ELEVATOR LLC	DCLO@ELEV
	4	6	Partners Capital Management LP	CK@CP
	1	7	WINDSOR Capital Advisors Corp	AMYSULLIVAN@windsor
Confluence Capital Management LLC	4	4	confluence Capital Management LLC	BLOFTON@confluence
	5	10	Lodzkie Bank	ADMIN@LODZKIE
XYZ Securities NV	5	5	XYZ Securities NV	DVANIMPE@XYZ
Brian Mitchel	1	1	David collins	bmitchel@BM
pearl Manager, LLC	1	1	pearl Manager, LLC	pearl@PEARL
	12	14	brantford Bank Securities	BCIPRIANI@BREPS
	14	61	ginsha LLC	DWILSON@GB
	2	3	Best Asset Management LLC	CARSLAN@TBAM
	14	38	CirclePoint Trading LLC	ADMIN11@CPADMIN
Lena William	1	1	Lena Williams	LENA@LIGHTSPEED
confluence Capital Management LLC	4	4	confluence Capital Management LLC	12097800@confluence
confluence Capital Management LLC	4	4	confluence Capital Management LLC	ADMIN@WEINSTEIN
Jeff Mallin	1	1	Jeff Mallin	JMALLIN@LIGHTSPEED
	1	6	Markets Limited	BCRITCHLEY@ML
	12	14	brantford Bank Securities	CLEE@BRANTFORD
	3	7	SpeedTrading	DDEMO1@DEMO
XYZ Securities NV	5	5	XYZ Securities NV	BMCCAULLEY@WASHINGTON
	3	7	SpeedTrading	DDEMO2@ST

<tbody>
</tbody>

So from the example above you can see that if the number of users who have NOT traded is the same as the number of all users in that firm, that means that no one in that firm has traded and the firm is at risk for being closed.

These are the results I would like to see in columns A and B

UNIQUE Values	# Users
Confluence Capital Management LLC	4
XYZ Securities NV	5
Brian Mitchel	1
pearl Manager, LLC	1
Lena William	1
Jeff Mallin	1

<tbody>
</tbody>

I could do a simple VLOOKUP for the values in column B.

Since my real table is over 3000 records long I was wondering if there is any concern with the formula taking a long time to calculate each row.
Anyway, I sincerely appreciate the advice and help and look forward to resolving this soon.
nmss18