Count unique text to 1 number

[rambostine202]

Hello people,

I just gave up on trying to find a formula which I know must be easy.

Here is the data I have (simplified data)
(A, B, C and D are columns and the rows are A2 to A13 but my actual work would be for A2 to A1000 or better yet the whole All A column.

A B
John 1
John 1
Jane 2
Larry 1
John 2
John 4
Eve 1
Jason 5
John 6
Rudolf 2
Rudolf 5
Rudolf 1
Jefferson 8

What I am looking for is a formula that would give me the following in columns C and D:

C D
John 4
Jane 1
Larry 1
Eve 1
Jason 1
Rudolf 3
Jefferson 1

What happened here is:
I want to get the number of times john has come up in the first spreadsheet.
John for example was there 5 times total but the number in D2 should only display 4 because I want to count the unique times John plus the value appear.
For example John had twice "1" , so I don't want to count more than once a name with the same number (i.e since John has two instances where the cell is "1", don't count him twice but just once)

Rudolf for example also shows up 3 times but all the numbers associated to him are unique (i.e in first data set above Rudolf has three instances of showing but no duplicate numbers, so in D7 Rudolf should have an associated number 3 which is the number of distinct values associated to Rudolf.

Hope I gave enough info.

If someone could help me I would very much appreciate it.

Thanks

 

[AhoyNC]

These are array formulas and must be entered with CTRL-SHIFT-ENTER.

If your column B is numeric then the formula in D2, if it's text then the formula in column E in the example below. Drag formula down as needed.

Excel Workbook
	A	B	C	D	E
1	Name	Data	Name	Count
2	John	1	John	4	4
3	John	1	Jane	1	1
4	Jane	2	Larry	1	1
5	Larry	1	Eve	1	1
6	John	2	Jason	1	1
7	John	4	Rudolf	3	3
8	Eve	1	Jefferson	1	1
9	Jason	5
10	John	6
11	Rudolf	2
12	Rudolf	5
13	Rudolf	1
14	Jefferson	8
Sheet

 

[rambostine202]

AhoyNC,

I am speechless... This is what I have been fighting with for two days and tried every approach possible. It's perfect. I truly appreciate the help. Now I can finally move on with this thing.

Again thank you so much for this. Thanks for taking the time to help a stranger.

Good day.

 

[AhoyNC]

You're welcome. Glad to help, thanks for the feedback.