Find Criteria & Count Unique Values Once or Avoid Duplicate Counting

Hi,

Have a list of POs that has multiple items associated to them & therefore, may be listed multiple times

Want to look up "450" & count unique POs once


In this example - There's 51 listed POs - If filtered & duplicates removed, will result to 27 Unique POs

This function kind of works but can't figure out how to search for "450" only

=SUMPRODUCT((I47:I500<>"")/COUNTIF(I47:I500,I47:I500&""))




26876554
4502013907
26862423
26862251
4502014834
26862252
4502015778
26177970
26178161
4502013907
4502013907
26178022
26178028
25994321
4502037950
26483200
25785438
4502033839
4502033690
4502040468
26188497
26178103
26178262
26178667
26178266
26177992
26178279
26178261
4502037504
4502033701
4502037504
26178019
4502037504
26177996
4502039031
26177998
26178017
26178256
4502037507
26178044
26178046
26178048
4502032710
4502032710
26112562
26862426
26862253
4502015776
26862293
26197604
26862254
4502015779
26862255
26863346
26197672
26178224
26178000
26178006
26178146
26178147
4502040503
4502028286
26178695
26112532
26178277
4502033441
4502013907
4502013907
26178022
26178028
25994321
4502037950
26483200
4502033817
26178817
26178813
26178814
26178815
26178816
26188497
26178103
26178262
26178279
26178261
4502037504
4502033701
4502037504
26178019
4502037504
26216846
26112562
4502039031
26177998
26178017
26178256
4502037507
26178044
26178046
26178048
4502032710
4502037504
26197535
26862428
4502031515
4502031515
26197543
26197544
26197529
26197537
26883370
26207435
26207437
26216870
26216871
26281309
26281063
26281064
26281065
26281306
26281307
26281308
26887161
4502037482
26178125
4502035546
26112562
26216903
4502039081
4502037474
26216867
4502039004
26112532
26197672
26281327
4502033701
4502040400
4502040400
4502037504
4502028119
26216926
26216927
26216928
4502037504
26887160
26216861
26216860
26887796
26216903
26216867
4502039004
26112532
26197672
26216877
4502037504
26281327
4502036510
26216937
26216869
26216874
26216875
26216876
26216873
26216872
26216865
26216885
26216848
26216878
26216924
4502037482
26216925

Next time, please post a sample sheet with sample expected answers. It's not entirely clear what sort of searches you want to majke.
But try this array formula in the attached sheet:

=IFERROR(INDEX($A$2:$A$118,MATCH(0,INDEX(IF(LEFT($A$2:$A$118,LEN($D$2))+0=$D$2,COUNTIF($F$1:$F1,$A$2:$A$118)),0),0)),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

or... if you just want to count them:

=SUM(INDEX((LEFT($A$2:$A$118,LEN(D2))+0=D2)*($A$2:$A$118<>"")/COUNTIFS($A$2:$A$118,$A$2:$A$118&"",$A$2:$A$118,$A$2:$A$118&""),0))

same sheet, normal formula.

One last thing, please amend your profile to show us which Excel version you are using and roughly where you are. Both influence answers. GA = Gabon?

Hi Glenn Kennedy

Both of these examples works great!!!

Thanks

You're welcome.

Hi Glen,

This function works well if stay within index range that has actual data:

=SUM(INDEX((LEFT($A$2:$A$118,LEN(D2))+0=D2)*($A$2:$A$118<>"")/COUNTIFS($A$2:$A$118,$A$2:$A$118&"",$A$2:$A$118,$A$2:$A$118&""),0))


When changed the "118" to "500" - Function doesn't work so well & getting "#VALUE!"
-- I'll be adding more rows later

How to modify function?

Thanks

Set up a Named Range. CTRL-F3 to view/edit. I called it "List":

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(1E+100,Sheet1!$A:$A))

Then use this to list them;
=IFERROR(INDEX(List,MATCH(0,INDEX(IF(LEFT(List,LEN($D$2))+0=$D$2,COUNTIF($F$1:$F1,List)),0),0)),"")
array entered.

and this to count them:
=SUM(INDEX((LEFT(List,LEN(D2))+0=D2)*(List<>"")/COUNTIFS(List,List&"",List,List&""),0))

The range will adjust automatically.