Hi NG,
Here is a small example of a problem I cannot solve:
3 columns
a b c
x 2
y 1
z 4
x 5 *
p 1
q 6
p 5 *
r 2
I let Excel put a * in column c if a value in column b equals 5 so this
happens in c4 and c8

Now I want Excel to also put a * in c1 and c5 this is because I need all
occurences of once they are marked (even only once).
What should I do?
Flip

Hi Flip,
Please double check your question.

but if you want to check for two values
=IF(OR(A1=5,A1=2),"*","")
and use the fill handle to copy formula down from row 1

if you want to count the number of occurrences of a value
you can use COUNTIF.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Flip" <FlipatKonitechPuntnl wrote in message ...
Hi NG,
Here is a small example of a problem I cannot solve:
3 columns
a b c
x 2
y 1
z 4
x 5 *
p 1
q 6
p 5 *
r 2
I let Excel put a * in column c if a value in column b equals 5 so this
happens in c4 and c8

Now I want Excel to also put a * in c1 and c5 this is because I need all
occurences of once they are marked (even only once).
What should I do?
Flip

Hi

do you mean that because row 4 had a 5 in column B and in column A there was
an "x" then all other instances of "x" have to have an * too in column C.

if so, then the only way i can think to do this is by using code ... e.g.
---
Sub star()
For Each c In Range("B1:B8")
If c.Value = 5 Then
For Each cl In Range("A1:A8")
If cl.Value = c.Offset(0, -1).Value Then
cl.Offset(0, 2).Value = "*"
End If
Next
End If
Next
End Sub
---
to use this code, right mouse click on the sheet tab and choose view code to
display the vbe window, choose insert / module from the menu and copy &
paste this code in there
then switch back to your workbook using alt & f11
choose tools / macro / macros
find star
and press RUN

NOTE: this code will also put the original * against the 5s for you too.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Flip" <FlipatKonitechPuntnl wrote in message
...
Hi NG,
Here is a small example of a problem I cannot solve:
3 columns
a b c
x 2
y 1
z 4
x 5 *
p 1
q 6
p 5 *
r 2
I let Excel put a * in column c if a value in column b equals 5 so this
happens in c4 and c8

Now I want Excel to also put a * in c1 and c5 this is because I need all
occurences of once they are marked (even only once).
What should I do?
Flip

Hello JulieD,
That is exactly what I mean.
Thank you for this great work of code.

In case you wonder 'what on earth is this guy doing with this'
I am trying to trace customer activity. If there is some form of activity
(sales) in a recent period, this customer is ruled out entirely from (this)
company-action which is actually searching for customers who seem to stop
working with us.
In my example "x" is a customer, "5" is a code for a specific month, "*"
means this customer will be deleted from this (historical sales)list. You
can imagine who remain in the list...

Anyway, thanks again
Flip



JulieD schreef in berichtnieuws
...
Hi

do you mean that because row 4 had a 5 in column B and in column A there
was
an "x" then all other instances of "x" have to have an * too in column C.

if so, then the only way i can think to do this is by using code ... e.g.
---
Sub star()
For Each c In Range("B1:B8")
If c.Value = 5 Then
For Each cl In Range("A1:A8")
If cl.Value = c.Offset(0, -1).Value Then
cl.Offset(0, 2).Value = "*"
End If
Next
End If
Next
End Sub
---
to use this code, right mouse click on the sheet tab and choose view code
to
display the vbe window, choose insert / module from the menu and copy &
paste this code in there
then switch back to your workbook using alt & f11
choose tools / macro / macros
find star
and press RUN

NOTE: this code will also put the original * against the 5s for you too.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway
"Flip" <FlipatKonitechPuntnl wrote in message
...
Hi NG,
Here is a small example of a problem I cannot solve:
3 columns
a b c
x 2
y 1
z 4
x 5 *
p 1
q 6
p 5 *
r 2
I let Excel put a * in column c if a value in column b equals 5 so this
happens in c4 and c8

Now I want Excel to also put a * in c1 and c5 this is because I need
all
occurences of once they are marked (even only once).
What should I do?
Flip

Hello David,
Yes it is often difficult to explain a problem.
Below, JulieD managed verywell.
Thanks anyway for thinking with me.
Flip

David McRitchie schreef in berichtnieuws
...
Hi Flip,
Please double check your question.

but if you want to check for two values
=IF(OR(A1=5,A1=2),"*","")
and use the fill handle to copy formula down from row 1

if you want to count the number of occurrences of a value
you can use COUNTIF.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Flip" <FlipatKonitechPuntnl wrote in message
...
Hi NG,
Here is a small example of a problem I cannot solve:
3 columns
a b c
x 2
y 1
z 4
x 5 *
p 1
q 6
p 5 *
r 2
I let Excel put a * in column c if a value in column b equals 5 so this
happens in c4 and c8

Now I want Excel to also put a * in c1 and c5 this is because I need
all
occurences of once they are marked (even only once).
What should I do?
Flip

Hi Flip

glad it worked, i must admit the 'why' hadn't even crossed my mind :) i
guess it's because i seem to spend all day telling my 3yo that we can
generally answer "what" and "how" questions but most of the time we'll never
know the "why" ... :)

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Flip" <FlipatKonitechPuntnl wrote in message
...
Hello JulieD,
That is exactly what I mean.
Thank you for this great work of code.

In case you wonder 'what on earth is this guy doing with this'
I am trying to trace customer activity. If there is some form of activity
(sales) in a recent period, this customer is ruled out entirely from
(this)
company-action which is actually searching for customers who seem to stop
working with us.
In my example "x" is a customer, "5" is a code for a specific month, "*"
means this customer will be deleted from this (historical sales)list. You
can imagine who remain in the list...

Anyway, thanks again
Flip



JulieD schreef in berichtnieuws
...
Hi

do you mean that because row 4 had a 5 in column B and in column A there
was
an "x" then all other instances of "x" have to have an * too in column C.

if so, then the only way i can think to do this is by using code ... e.g.
---
Sub star()
For Each c In Range("B1:B8")
If c.Value = 5 Then
For Each cl In Range("A1:A8")
If cl.Value = c.Offset(0, -1).Value Then
cl.Offset(0, 2).Value = "*"
End If
Next
End If
Next
End Sub
---
to use this code, right mouse click on the sheet tab and choose view code
to
display the vbe window, choose insert / module from the menu and copy &
paste this code in there
then switch back to your workbook using alt & f11
choose tools / macro / macros
find star
and press RUN

NOTE: this code will also put the original * against the 5s for you too.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway
"Flip" <FlipatKonitechPuntnl wrote in message
...
Hi NG,
Here is a small example of a problem I cannot solve:
3 columns
a b c
x 2
y 1
z 4
x 5 *
p 1
q 6
p 5 *
r 2
I let Excel put a * in column c if a value in column b equals 5 so this
happens in c4 and c8

Now I want Excel to also put a * in c1 and c5 this is because I need
all
occurences of once they are marked (even only once).
What should I do?
Flip