Duplicate data

Dids

New Member
Joined
Mar 4, 2020
Messages
7
Office Version
  1. 2010
Platform
  1. Windows
Hi. I am trying to count how many different purchase orders that I have for different dates. So if I have a duplicate number on the same date i want it to count as 1 but where the number are in different dates ir

my data is basically

col a. Col b
456. 01/01/21
674 02//04/21
567 03/02/21
674. 02/04/21
334. 02/04/21
567. 06/04/21

so basically I’d expect my count to be

456. 1
674. 1
567. 2
334 1
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
How about this?

Book1
ABCDE
1PODateUniq POCount
24561/1/20214561
36742/4/20216741
45673/2/20215672
56742/4/20213441
63442/4/2021 
75676/4/2021 
Sheet1
Cell Formulas
RangeFormula
E2:E5E2=SUM(IF($A$2:$A$7=D2,(1)/COUNTIFS($A$2:$A$7,$A$2:$A$7,$B$2:$B$7,$B$2:$B$7),0))
D2:D7D2=IFERROR(INDEX($A$2:$A$7, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$7), 0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
thank you so much.

I can’t seem to get it to work it’s just returning blanks for me. I’m certain it’s user error on my part.

why in the match formula is it D1?
 
Upvote 0
Did you enter at an ARRAY formula in each case because you're using 2010?

D1 is there because it needs that to compare the values.
 
Upvote 0
thank you so much.

I can’t seem to get it to work it’s just returning blanks for me. I’m certain it’s user error on my part.
@Dids You have to hit CTRL-SHIFT-Enter, not just ENTER, For the formulas that you place in the D & E Columns. ;)
When done correctly, you will see a '{' at the beginning of your formulas and a '}' at the end of them.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top