comparing or matching corresponding value

  • Thread starter Thread starter mary
  • Start date Start date
M

mary

Hello all.
Is is possible to have a formula or macro that whenever a
specific word is entered into any cell in a given column
it will copy its corresponding cell to another cell?
Eg.
I want anytime john or any selected value is entered in
column B for it to record in column C. D1 is always blank
until a value is add. I am look for all corresponding
value for john and May
A1 B1 C1
John 70 70
PAUL 10
MAY 20 20
KELVIN 10
JOHN 20 20
CHRIST 10
MAY 5 5
JOHN 10 10

I tried this formula but no luck... Please help... thanks
 
Mary,

It's unclear exactly what you're after. Here's my interpretation.

Right click the Worksheet tab, View Code. Paste this code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Select Case LCase(Target.Offset(0, -1).Value)
Case "john", "may": Target.Offset(0, 1).Value = Target.Value
End Select
End If
End Sub

Rob
 
I want column C:C to look of any match in colum A:A and
report its corresponding value from B:B on column C:C.
Example: in column C, I would like to report all the
corresponding value of john to column C. column C make a
match in column A and return all the value in C.
Thanks

mry,
 
Mary,

Sorry - I'm really having a hard time following you...

Here's how I understand it:
You want values in column C to equal column B, but only if column A = "John"

That's what the code I supplied in my previous post does.

If you want a worksheet formula, enter this to C1:
=IF(LOWER(A1)="john",B1,"")


Rob
 
Thanks Rod. that is what i wanted. I appreciate your
help. is it possible to write me the code. Actually, i
will like to sub total all value for john. really do not
need the each cell value. Just the corresponding sub
total.
Thanks again
 
Mary,

You can use an array formula:

=SUM(IF(LOWER(A1:A500)="john",B1:B500,0))
when you enter this formula, press CTRL+SHIFT+ENTER

Or you may want to investigate the SubTotals feature of Excel.
From the data menu, choose SubTotals. (ensure your list is sorted by name
first)

Rob
 
Actually the array formula is a terrible idea for this simple task.
There's a better reply by David to your other e-mail regarding the use of
=SUMIF

Rob
 
Back
Top