Create New Count List Excluding Duplicates

[Scudder93]

Hi Guys,

I've been reading up on this site for a while, but this is my first post.

The issue I have is that I have a list of incidents occuring on various applications, and I need to create a new list from the original, that counts the number of incidents for each application, but having just one row (in the new list) for each application.

The difficulty I'm having is that I have a list of all possible applications, but not all of these applications have had incidents as of yet, so if I looked up from there, then there will be lots of zero values in the new table. I imagine the best way to solve this would be to lookup from there and not have the zero values populated in the list, but I've struggled massively to do this.

I've attached a sample of my data, hopefully it'll help. If I haven't been clear, please let me know and I'll try and clarify more. :-)

Thanks in advance,

Chris

[Marcol]

How are you counting the "Number of Incidents"?
I can't reconcile the expected result with the data table.

[Scudder93]

Thanks for the reply.

Apologies, I just randomly typed those numbers to try and explain the type of format I'm after.

I've counted them properly now and attached the new version.

Chris

[zbor]

Do you mean this in A2:

=LOOKUP(REPT("Z",255),CHOOSE({1,2},"",INDEX(Sample!$A$2:$A$1000,MATCH(TRUE,INDEX(ISNA(MATCH(Sample!$A$2:$A$1000,$A$1:$A1,0)),0),0))))

but I don't see point of putting 1 in each row by default. You don't need formula for that. You can just put 1.

[Scudder93]

Sorry, I must have uploaded the file before I made my final save. The Countif formula should have the reference to the Sample sheet so the numbers go in there automatically. I've uploaded the proper version again.

Thank you so much, the formula you've given me is exactly what I was trying to do, however when I put this into the actual document I don't get the same result. Unfortunately I can't post the original spreadsheet because it contains sensitive data.

I've changed the formula you gave slightly to try and adapt it for the original spreadsheet:
from
=LOOKUP(REPT("Z",255),CHOOSE({1,2},"",INDEX(Sample!$A$2:$A$1000,MATCH(TRUE,INDEX(ISNA(MATCH(Sample!$A$2:$A$1000,$A$1:$A1,0)),0),0))))
to
=LOOKUP(REPT("Z",255),CHOOSE({1,2},"",INDEX('P2 Incident Summary'!$A$5:$A$1000,MATCH(TRUE,INDEX(ISNA(MATCH('P2 Incident Summary'!$A$5:$A$1000,$A$5:$A5,0)),0),0))))

because 'P2 Incident Summary' is the equivalent sheet to 'Sample', and the data starts in cell A5 rather than A2. The only other difference that I can think of is that where I have given a letter for the app. name in the sample, that letter is the first letter of the full application name.

I don't suppose you could tell where I'm going wrong from that?

Thank you very much,

Chris

[Marcol]

Maybe best you use a dynamic named range "Apps" ...
Refers to:=

Please Login or Register  to view this content.

This is more economic than using a static range big enough to cover all possibilities, in as much that it calculates exactly the range required.

Then in Sheet "Example ... " A2

Please Login or Register  to view this content.

And in B2

Please Login or Register  to view this content.

Drag both down until blanks are returned then a bit more to allow for a growing Sheet"Sample"
Then if you add or delete rows before your header row in Sheet"Sample" the formulae will still work.

[martindwilson]

select new sheet
data/filter/advanced
select copy to another location and unique values only
click in list range field
click tab of your data (in this case the tab "sample"
select col a
click in the copy to field
it will jump to your new sheet
click a1
click ok
you now have an unique list
so in b2 put
=COUNTIF(Sample!A:A,A2)
drag down