Unique records help

[nolanstern]

Hello,

I have downloaded data to an excel spreadsheet by day and need to calculate the unique records by day. Then all the daily totals should equal the monthly total if I ran the same date range for the month by removing the duplicates to get the unique records.

How can I do this?

nolanstern

 

[Smitty]

Welcome to the Board!

How about posting an example?

See: How to post your sheet on the Board.

 

[goldfish]

To count the number of times a row in Column A has the date 8/21/2008 you can use:

=COUNTIF(A:A,"8/22/2008")

if you want to count the month, you can either add up all the days using the above or use:

=SUMPRODUCT(--(MONTH(A1:A200)=8))

to get the count of the number of times the month is august (8th month).

Gold Fish

 

[nolanstern]

Unfortunately, I can't post the file as it is a work related file which contains sensitive information.

Here is more information to my problem.

I have daily records which include member numbers and always has duplicate records. How do I determine true unique records on a daily basis.

Do I remove the duplicates on day 1 then on day 2 remove the duplicates for day 1 and 2 together and subtract the that total from day 1 to get the true unique records for day 2?

Then at the end of the month remove duplicates for the whole month and it should equal the unique records as if I was doing above.

I hope I am making sense.

nolanstern

 

[Smitty]

I have daily records which include member numbers and always has duplicate records. How do I determine true unique records on a daily basis.

How can we tell Excel that the records are duplicates?

Is there only one row of data per Member # that needs to be kept or might there be more than one?

If you need to you can post a shot using sample data.

You can also check this link on using Advanced Filter to get unique records:

Delete duplicate rows from a list.

 

[goldfish]

If I understand you correctly you want the unique number of members that occur on each day? so if a member comes in on both day 1 and day 2 that would be 2 events? Check out:
http://www.cpearson.com/excel/Duplicates.aspx
for lots of tips for how to look at unique items.
A few other options available to you are pivot tables, or also Microsoft Access is very good at this kind of task.

From chip pearson's above he has the formula:

=SUM(IF(FREQUENCY(IF(LEN(B2:B11)>0,MATCH(B2:B11,B2:B11,0),""), IF(LEN(B2:B11)>0,MATCH(B2:B11,B2:B11,0),""))>0,1))

Which will count the number of unique items in a range. But you want to count a member coming in on multiple days as two events, so one way to still use this formula would be to make a third column that was a concatenation of the day and the member number
=A1&B1
and then use the above formula on column C.

hope this helps, check out his website for other information too, like how to highlight the duplicates.

GoldFish

 

[nolanstern]

Explaining what I need is very difficult but I will do the best to explain it again.

I am trying to get a true UNIQUE users by day and when you add each day up at the end it should match the totals if I was to get the true UNIQUE users if a query was run by a month range (such as July 1- 31).

PLEASE NOTE I CAN'T POST THE FILE AS IT CONTAINS SENSITIVE INFORMATION AS IT IS WORK RELATED.

 

[Norie]

How about trying Data>Pivot table...?

Also, we aren't actually asking you to post the 'real' data.

You could post something which it's representative of what you have but with dummy data.

It might also help if you posted the expected result.

 

[nolanstern]

I have tried a pivot table but that doesn't really work unless you remove the duplicates before running the pivot table. However, I am stuck with how to get the true unique users by day which equals the total by month if run separately.

 

[goldfish]

We're trying to help. Pivot tables is a perfectly good tool and I've used it frequently for exactly this, but we can't help you get it to work if all you tell us is it "doesn't really work". How doesnt' it work? I would suggest using Date and MemberID as column fields and then doing your formulas based off of your pivot table since the pivot table eleminates duplicates (the point of the pivot table is NOT to do the counting for you, it is the elemination of duplicates... if you want it to do the counting as well, you'll have to do a pivot table on the pivot table)

Did you try using my post at all? You haven't posted anything about whether the things in my post did or did not work for you, or perhaps you didn't understand them and I could clerify.