Distinct Values in a Column

[AnetShe]

I can't seem to figure this out or find an already posted solution - any help would be amazing!
My dilemma - if the names below were in a column (approx 1,500 rows) I need a column beside them showing a 1 or a 0 ONLY once to note if the value is unique.
Is there a formula I can use for this?
The farthest I have gotten is using this formula but it only checks the rows below the lookup value and doesn't check the rows above it.
=IFERROR(IF(K3=VLOOKUP(K3,K4:K$2264,1,FALSE),0),1)

Example columns with last value showing the 1 and 0s that I need:

Region - Partner Name - Sales - Unique Identifier
Canada - James - $10 - 1
Canada - Rob - $5 - 1
Canada - James - $5 - 0
Canada - James - $25- 0
Canada - Stephanie - $10 - 1
Canada - Rob - $10 - 0
Canada - Rob - $5 - 0
Canada - Stephanie - $10 - 0

The reason this is needed is that I need to create a pivot table showing a count of number of unique names without actually showing the names in the pivot table.

Example of the pivot values that are needed from the example values above:

Region - # of Unique Partners - Sales
Canada - 3 - $80

Let me know if this makes sense and I am open to any other suggestions to achieve what is needed.
THANK YOU!

 

[sandy666]

Re: Help! Distinct Values in a Column

Insert PivotTable with Add to DataModel then select Distinct Count for Partner Name

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Region [/td][td] Partner Name [/td][td] Sales [/td][td] Unique Identifier[/td][td][/td][td]PivotTable[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Canada [/td][td] James [/td][td]

10​

[/td][td]

1​

[/td][td][/td][td=bgcolor:#DDEBF7]Region[/td][td=bgcolor:#DDEBF7]Distinct Count of Partner Name[/td][td=bgcolor:#DDEBF7]Sum of Sales[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Canada [/td][td] Rob [/td][td]

5​

[/td][td]

1​

[/td][td][/td][td]Canada[/td][td]

3​

[/td][td]

80​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Canada [/td][td] James [/td][td]

5​

[/td][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Canada [/td][td] James [/td][td]

25​

[/td][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Canada [/td][td] Stephanie [/td][td]

10​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Canada [/td][td] Rob [/td][td]

10​

[/td][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Canada [/td][td] Rob [/td][td]

5​

[/td][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Canada [/td][td] Stephanie [/td][td]

10​

[/td][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[Fluff]

Re: Help! Distinct Values in a Column

With a formula

Excel 2013/2016
	J	K	L	M
2	Region	Partner Name	Sales	Unique Identifier
3	Canada	James	$10	1
4	Canada	Rob	$5	1
5	Canada	James	$5	0
6	Canada	James	$25	0
7	Canada	Stephanie	$10	1
8	Canada	Rob	$10	0
9	Canada	Rob	$5	0
10	Canada	Stephanie	$10	0
Archive

Cell Formulas
Range	Formula
M3	=IF(COUNTIF(K$3:K3,K3)=1,1,0)

 

[AnetShe]

Re: Help! Distinct Values in a Column

This formula worked perfectly - thank you!

 

[Fluff]

Re: Help! Distinct Values in a Column

You're welcome

 

[AnetShe]

Re: Help! Distinct Values in a Column

Thank you also for this - the data model & distinct count was a fantastic new learning!

 

[sandy666]

Re: Help! Distinct Values in a Column

Glad to help

 

[mrpavlos]

Re: Help! Distinct Values in a Column

Another formula that might come in handy if you were dealing with big data sets:

=IF(MATCH(K3,$K$3:$K$1500,0)=ROW()-1,1,0)