formula needed

[aormonde]

I am looking for a formula and this is really stumping me. I have a
worksheet that has one column w a name and one w a date that of things given,
since the name can be on their numberous times as can the date, I need to
find a formula that will look to see if the cell above is the same or not and
count only if it is different, (which I have that formula) however, I need to
go a step further, because not all the names recieve something on the same
day, so I need it to if the cell is blank then to leave it blank. How can I
do this in ONE formula....so, in short I am looking at something that will
look at a cell and see if it is blank, if it is leave it blank, if it isn't
then to look at the cell above it to see if it is the same, if it is not the
same then count it as 1.

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Joe User]

I need to find a formula that will look to see if the
cell above is the same or not and count only if it
is different, (which I have that formula) however,
I need to go a step further, because not all the
names recieve something on the same day, so I
need it to if the cell is blank then to leave it blank.

Have you have heard the adage, "A picture is worth a thousand words"?

First, if you already have a formula that does part of what you require,
show us the formula. Second, show us an example that demonstrates the
various situations and how you want to deal with them.

For example, you have dates in column A, names in column B, and you want a 1
or blank in column C as follows, starting in row 2:

1/1/2010 john 1
1/1/2010 john
1/1/2010 john
1/2/2010 john 1
1/2/2010 john
1/2/2010 mary 1
1/2/2010 mary
1/3/2010 john 1

If that's the case, then put the following formula into C2 and copy down:

=if(or(A2<>A1,B2<>B1),1,"")


But then you write:

in short I am looking at something that will look
at a cell and see if it is blank, if it is leave it blank

Perhaps:

=if(and(A2<>"",or(A2<>A1,B2<>B1)),1,"")

If that is not what you want, please provide examples of what you do want.


----- original message -----

 

[aormonde]

Yes, something very similar to your set up is what I need, however, if there
was a date that didn't have a name I need it to stay blank. So, something
like,

Colum A Column B Column C
John 1-1-10 1
John 1-1-10
Mary 1-7-10 1
Mary 1-7-10
Mary 1-7-10
Sam
Sam
Sam
Jane 1-8-10 1

So what I need it to be able to see how many were returned, so I don't want
it to count the blanks, but because for instance Mary has more than one, I
don't want to count it three times, I want to count it once...

 

[aormonde]

so I tried this on my worksheet, and it worked GREAT!!! Thanks soooo much!

 

[Joe User]

Yes, something very similar to your set up is what I need, however,
if there was a date that didn't have a name I need it to stay blank.

.....Which is not what you show in your example. ;-) To be safe, I think the
following will do the trick:

=if(and(A2<>"",B2<>"",or(A2<>A1,B2<>B1)),1,"")


----- original message -----

 

[Don Guillett]

This macro works for the example given

Option Explicit
Sub countif()
Dim i As Long
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(i - 1, 1) <> Cells(i, 1) And _
Cells(i, 2) <> "" Then Cells(i, 4) = 1
Next i
End Sub