Simple Way to Count the Number of Duplicate Dates on Multiple Worksheets

[Dan]

Hello all! I am tearing my hair out trying to figure this out, I won't
go into what exactly I am trying to eventually acomplish but just what
I need to work outnow. (if you know what I mean? :-/ )

I have 15 seperate worksheets each containing two columns (20 rows) of
dates. I need some sort of formula to count how many times a date
appears when it appears more than once, but only in a different sheet.
So for example if I have 2nd Jan 2006 in two seperate sheets it will
count as 1 duplicate. All I need is for a figure of how many times any
date clashes to appear on my summery page.

Hope I explained that ok. Please go easy as I'm not very hot on excel
(just enough to get me through).

Thanks in advance.
Dan

 

[Guest]

You could create a summary sheet that had all the data from each worksheet
and then you could sort on date and do a subtotal count on each change in
date.

 

[Flintstone]

Hello Dan

To follow up with what Kevin is talking about, have all of your dates
represented on a single sheet or somewhere off the visible area of your
summary worksheet such as A200: AF219, in other words suppose your
summary worksheet is Sheet1 and your two columns of dates which are 20
rows long are in sheet 2 through 16, place this =Sheet2!A1, in cell
A200 and copy down to row 219, then again with =Sheet2!B1, in cell B200
copy down, =Sheet3!A1 in cell C200 copy down, =Sheet3!B1 in cell D200
copy down, until you’ve filled in the entire range A200:AF219.

Next label the bottom of each column so as to identify which sheet it
comes from, columns A & B would be labeled as Sheet2, columns C & D
labeled as Sheet3 and so on.

Paste this formula in cell A223 and copy it throughout the entire range
of A223:AF242.

=IF(OR(A200=0,COUNTIF($A$200:$AF$219,A200)-1=0),"",IF(COUNTIF($A$200:$AF$219,A200)-1>=1,A200,""))

Place this function in cell A1 (or wherever) to see the results.

=COUNT(A223:AF242)

The range A223:AF242 will give you a visual representation of the
location for which dates are in conflict.

To the best of my knowledge I don’t believe you’re going to find a
single formula that will accomplish what you’re asking, although I
could be wrong. However, if you post your question in the Excel
Programming forum, I’m sure you’ll have a better response then my
generic version.

Matt