Count Only Unique Data Points By Day

[mcchurchmouse]

Hello all!

I feel like this shouldn't be so hard but I can't figure anything out and when I search existing posts the formulas are so complex and customized that I can't make sense of them. I am a birder and keep detailed records of my finds and I simply love having way too many options to customize the information I can get back out of a workbook. Attached you will find a very, very condensed sample of relevant data. While it is only text, in reality these are nearly all pulling from various other sources, but I don't see how that will harm anything.

The goal is to get an easily updatable (automatic), sortable set of numbers, one for each date. I want to see how many unique species I saw in a day regardless of how many I saw or how many places I saw them. So two Northern Cardinals seen at four different sites should only count for a single tally for the day.

For the data given the results should read:

5/15/2013: 42
5/17/2013: 16
5/20/2013: 16
5/22/2013: 64
5/25/2013: 27
5/27/2013: 25

And again, sortable by numeric result is the key. Thank you for looking and at least checking this out for me!

Benjamin

[FDibbins]

Hi and welcome to the forum

Im not sure if this will help you (Im pretty sure its not exactly what you wanted)

On sheet2, I have a dynamic table that will grow with your data (within reason, at the moment - you will probably have to increase the ranges).

I have added a filter so that you can filter on whatever you want, take a look and let me know?

[mcchurchmouse]

Ok, I was able to add a "countif" function row to sum up the number of founds to give me the number I am looking for. I've not done much (or anything before 5 minutes ago) with dynamic tables, would I be able to sort the columns based on the values of row B? This is already better then anything I've come up with, but the problem I see here is the vast amount of dates to have to manually enter in, both in the past and going ahead into the future. My main Sightings entry area is down to line 4212 and I haven't entered in the two places I went today yet. I am currently at 259 individual dates with information already logged. Could I pull a pivot table column, each with a single date, into the dynamic table (equivalent to what you made "Sheet 1 / Column J")?

If there is anything simpler and / or more automated I'd love to hear it, but this is at least a way to get what I want provided I do more then just refresh a table. Thank you again and I will be back to check for more ideas tomorrow!

Benjamin

[FDibbins]

No, the dates will extract themselves. I figured you would have a ton of them, so i added a formula, copied down, in sheet1 J2:J16. If you need more dates, copy that down further. Note that it is an array formula - if you edit it, re-enter by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Also, on sheet2, I only carried the formula in row 1 as far as K1 - this pulls from J on sheet1, copy it further to the right as needed (make sure you have at least as many copied down on sheet1 as you have copied across on sheet2)

I have added some additional functionality to sheet1. I know of know formula way to sort text (doesnt mean there isnt 1 though), so I have used the 1st 3 letters, converted to numeric, to base the sort on (may not be completely perfect), and then based the extract on sheet2 on that.

Take a look and let me know.

[mcchurchmouse]

Ok, sorry it took me so long to get back to you but I've been playing with adding / adapting your formulas and ideas into my sheet. On your sample sheet I understand what you are doing (not the formulas, but the theories) and I am able to get the answers I am looking for. The trouble I am having now is when I try filling all the working edited formulas both to capture the date and everything from your "Summary" page Excel and my computer just can't seem to handle it. As it tries to crunch the data my cpu is maxing 100% and runs for 30min+ for each little section of fill (as in 15 dates instead of all dates). Even now, I'm typing and the text comes up probably 5 seconds after I type. I am running a 4 core 2.90ghz machine with 16gb of RAM (Windows 7 - 64bit) Ram usage has been below 5gb total while working with the sheet.

Is this unexpected while processing this amount of data? If I take my time and get the formulas and arrays current would future days data cause more issues as everything rechecks its reference ranges?

Benjamin

[FDibbins]

a few questions...
how many rows of data are we talking about?
when you adjusted the formulas, did you change them to full columns (A:A) instead of just extending the range...
=INDEX($C$2:$L$253,MATCH(SMALL($L$2:$L$253,ROW(A1)),$L$2:$L$253,0),1)
to
=INDEX($C$2:$L$2000,MATCH(SMALL($L$2:$L$2000,ROW(A1)),$L$2:$L$2000,0),1)
?

[mcchurchmouse]

I extended the range as far as I can remember. I had to force close Excel just to do anything else and now the auto-recovered version wants to calculate everything so I'm lagging again. Pretty good estimate of what I've got so far are 4200 rows (each row has a date, a location, and a single bird all in separate columns) with 240 unique birds (4200 sightings consisting of 240 separate birds) spread across 259 individual dates (each date has from 1 to ~5 locations and each location within a date has all birds seen there even if it duplicates a bird seen at a different location on the same day). Hopefully I broke that down enough to make sense, the sample sheet I posted originally was a small section directly from where the information is entered and would be pulling from to make your dynamic tables and date isolator (not sure what to call it, but your Sheet1 J:J column).

Thank you for all your help, at least I know at this point I wasn't simply missing a simple answer. Not giving up, just making sure to thank you again before you throw in the towel and I miss my chance...

Benjamin

PS: I ended up using task manager to kill excel again after trying to open up the auto-saved version of what I was working on earlier. It ran for about 30 minutes staying most of the time at 48% (never higher). It is definitely not locking the computer, the processes tab shows it is actively using the processor...

[mgdac]

This problem is solved easily by the use of Pivot tables. Although the process is not automatic, you will get to the solution fairly quickly.
(1) Insert Pivot table in new worksheet
(2) Select Date, Bird, Qty
(3) Put Date in Rows, Bird in Columns, Count of Qty in Values(Sum of Qty is ok to use)
(4) At the end of the column of birds, after each date row put a countif formula for the number of values in each row.

Not elegant but gives the results without messing with macros or complicated formulas.

[mcchurchmouse]

Ok, as I sit down and do the Pivot Table seems to work ok, my computer crunches that data instantly. I then created a second pivot table with the column of the "counta" function and the date field in the first pivot table. This allowed me to make the dates / numbers orderable. I'm going to catch my sheet up with the birds I've seen since I started working on this "expansion" a couple days ago. I'll be back to report my findings later tonight!

Benjamin

[mcchurchmouse]

Well, that seemed to do the trick. I really only have to refresh the two pivot tables and make sure a couple rows and columns are expanded enough. Not too shabby! Shame, I could see that "FDibbins" methodology was going to produce the right results as well but my computer just couldn't handle that level of computation within a reasonable time. Thanks very much to both of you!

Benjamin

[FDibbins]

Happy to help, and im glad you found a solution that worked for you