extending selection

F

Flip

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
 
D

David McRitchie

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.
 
J

JulieD

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.
 
F

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
 
F

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 said:
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 news:[email protected]...
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
Click to expand...
Click to expand...
 
J

JulieD

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" ... :)
 
Top