Counting reoccuring dates.

[Guest]

Hi,

Hope this isn't a double post, previous try ended in "server too busy".

I have an Excelsheet with columns "ReceivingDate", "Type" and "Customer"
(and some other columns that don't matter).

I need three lists that show me every date that's in "RecevingDate" with the
number of times it showed up in the list in the next column.
Same with "Type" and "Customer".

So endresult should look like this:
Column1.....Column2.....Column3.....Column4.....Column5.....etc.
6/13/2006...2...............ModelX.......4................Hainz.........etc
4/28/2006...1...............ModelY.......2......etc.
etc.
Column 3 and 4 and Column 5 and 6 can be on a different Tab because I will
copy and paste the results in another report anyway.

Thanks,
R-P

 

[Bob Phillips]

=COUNTIF(A:A,A1)

etc.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Pete_UK]

Assume your worksheet is called "Data". Insert 3 blank worksheets.

Highlight the ReveivingDate column, click <copy>, move to one of the
blank sheets, select cell A1 and press Enter. Then use Data | Filter |
Autofilter and select Unique Records and Move to Other Location
(specify $C$1) and click OK - this will give you a list of unique
dates, and you can delete columns A and B. You might like to sort the
dates at this point.

Enter this formula in B2 (you should have the heading ReceivingDate in
A1):

=COUNTIF(Data!A$2:A$1000,A2)

This assumes that the ReceivingDate column is A and that you have 1000
records in the Data sheet. Copy this formula down by double-clicking
the fill handle (the small black square in the bottom right corner of
the cursor).

You can repeat this procedure for the other columns in your main Data,
i.e. Type and Customer, copying each to a blank sheet and obtaining a
unique list then entering the COUNTIF formula with the appropriate
ranges of the Data sheet.

Hope this helps.

Pete

 

[Guest]

Works, but has an unwanted catch:
7/13/06..1
6/5/06....5
6/5/06....5
6/5/06....5
6/5/06....5
6/5/06....5
5/18/06..2

I'd rather have:
7/13/06..1
6/5/06....5
5/18/06..2

I'll try Pete_UK's solution, but since I have to do this every week, a less
labour intensive approach would obviously be prefered.

Thanks so far, will try to continue with both your answers to get the
endresult I want.

 

[Bob Phillips]

But you asked for the adjacent column. To do what you want needs two
columns, one to get a list of uniques, one for the count.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Guest]

Assume your worksheet is called "Data". Insert 3 blank worksheets.

Highlight the ReveivingDate column, click <copy>, move to one of the
blank sheets, select cell A1 and press Enter. Then use Data | Filter |
Autofilter and select Unique Records and Move to Other Location
(specify $C$1) and click OK - this will give you a list of unique
dates, and you can delete columns A and B. You might like to sort the
dates at this point.

Enter this formula in B2 (you should have the heading ReceivingDate in
A1):

=COUNTIF(Data!A$2:A$1000,A2)

This assumes that the ReceivingDate column is A and that you have 1000
records in the Data sheet. Copy this formula down by double-clicking
the fill handle (the small black square in the bottom right corner of
the cursor).

You can repeat this procedure for the other columns in your main Data,
i.e. Type and Customer, copying each to a blank sheet and obtaining a
unique list then entering the COUNTIF formula with the appropriate
ranges of the Data sheet.

Hope this helps.

Pete

 

[Guest]

Sorry about the empty post. I had been typing a reply for 15 minutes and then
you push "post" and the programm decides you have top log on again and once
you've done that, your answer is gone..... very handy....

So once again: I cannot find the "unique" button (Excel 2000, maybe that's
the problem, I only have "TOP 10", "Custom" or any of the dates from the
column in the pull down menu, and "custom" doesn't feature 'unique' either).

I used Bob's answer to create a new row with the number of times a date
occurs in it. These two rows I coppied (values only) to a new tab.Then I
sorted and used Pete's answer by means of "=IF(A2<A3, A2, "").
Copy and Paste-special=>Values again and another 'sort' and I can finally
copy the data I want.

It works, but takes a lot of time.

I'll keep at it...

Thanks !!!

 

[Pete_UK]

Sorry, it was a typo on my part - I meant to say Data | Filter |
Advanced filter, not Autofilter.

Pete

 

[Guest]

Thanks, that works like a charm combined with the =countif(A:A,A1) statement.

Is there a way to get this into a formula?
I have to make a weekly report from a .NET databaseapplication (used to be a
borland-Delphi applications, but changed last week, so all old filters are no
longer usable)
Most of the data I get with query's, but the part about reoccuring dates I
have to get from an outputted XLS-file.

*Ideally* I would want formula's into the template of the report I have to
make. Then copy the outputted data of the XLS-file into a new tab in the
report, and that's it...

If you have ideas, I'm listening, if not: the solution from you and Bob
works very well with little extra work compared to the old Database-report.

Thanks!!!

 

[Pete_UK]

There are formulae which can give you unique values bunched up so there
are no gaps, but you might find it easier to record a macro while you
go through the procedure I gave you - i.e. produce unique values in a
separate sheet using Advanced Filter and then count them. You could
assign a keyboard shortcut to the macro and store it in personal.xls,
so that you could run it again anytime you want to.

Hope this helps.

Pete