traverse a column and change data based on result

[jcalvacca]

A B

Tom no

Bill no

Mike no

Joe no

Jim no

Matt no

Jerry no


If I want to search if Jerry is in Column A, then change its corresponding line to "yes".


Ex: Search Jerry, change it to yes. If I search Philip, nothing would be done.


Im running Excel 2003


Thanks in advance.

 

[Swaroop]

I have uploaded a file that can help


http://rapidshare.com/files/431453290/Test_SS.xlsm


Summary of what I did:

Updated a private sub function in worksheet 1 (what ever worksheet you use - NOT module)

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$1" Then
Call Test

End If
End Sub
and now insert a module and copy paste the below - Change the references accordingly if needed


Sub Test()
Dim a As String

a = Range("D1").Value

If a = "" Then
Exit Sub
Else
Columns("A:A").Select 'Select the column in which you have data
Selection.Find(What:=a, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1) = "Yes"

End If
End Sub

[/pre]
You have a cell for which data validation is set as a list of your names (Tom, Mike etc)

so you change the drop down and it searches the name and fills the next cell as "Yes"

 

[jcalvacca]

looks impressive, but way too much information I believe.


Basically i have a list of about 14000 items and I have a current list of about 4000 items. i want to "activate" the 4000 items on the list of 14000. I don't need drop down bars or the like. Thanks though for the help Swaroop.

 

[Hui]

In B2 use

=IF(COUNTIF($A:$A,"="&A2)>1,"Yes","No")

Copy down

 

[jcalvacca]

Close, the big list is in column A, the "yes" and "no" list is in column B, and the list that i want to activate is in column C. So it needs to look at C, and see if the instance exists in A and then changes the corresponding data in B. Maybe just change it to....


=IF(COUNTIF($C:$C,"="&A2)>1,"Yes","No")


Would this work if this goes in column B?


Can I make the "yes" and "no" permanent then? In other words, If I remove Column C will the changed values still hold in Column B?


Thanks.

 

[Hui]

Use

B2: =IF(COUNTIF($C:$C,"="&A2)>0,"Yes","No")

If the value in A2 is in Column C put a Yes and if not put a No


When asking questions please fully explain what you want as the initial question didn't have a column C !

 

[jcalvacca]

Thanks and yes I should have mentioned 3 columns This will make "yes" and "no" permanent? (i am not near the file or I would test it myself!)

 

[jcalvacca]

having trouble with this and I don't know why....


HUI can you help?


http://www.2shared.com/document/1MkqILty/jcalvaccasample.html


This is the way the actual data is laid out and what I am trying to search.

 

[Hui]

I don't understand your problem?


The formula above or =IF(COUNTIF($C:$C,"="&A1)>0,"Yes","No") when pasted into B1 and copied down works perfectly.


Please explain what is wrong?

 

[jcalvacca]

Strange. It works when I click on the cell and "copy". It doesn't work if i click on the cell and type in the formula. Wonder why that is...


Just a second thought HUI (Thanks again for your help as always). Can I make a D column to verify that the "yes" recorded correctly. I manually put alot of these item codes and want to make sure they were entered correctly.


So D column will have the logic to check if the "yes" was done on the original A<->B relationship.


Ex:

A B C D


Jerry no Mike true

Mike yes Patr false (because of typo)

Bill no Jim true

Pat yes

Jim yes


After completion I will know its accurate when the D column all reads true.


Thanks again.

 

[Luke M]

=COUNTIF(A:A,C2)>0


Then, to get fancy:

=IF(COUNTIF(D:D,FALSE)=0,"All done!","Keep working")

 

[Hui]

Are you using Excel or something else ?

Is Calculation set to Automatic or Manual ?