Find a value in a row then change another in same row

scrupul0us

Well-known Member
Joined
Sep 7, 2004
Messages
641
I need a basic replacement that will search column C for a string.. if it exists change a value in column L of that same row...

i have about 5 or 6 strings (full proper personell names) to look for and they can appear up to about 10-20 times

any help would be appreciated... thanks
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
My example changes occurances of "manual" within strings in col A to whatever I have in column E:

Formula in Col B: =SUBSTITUTE(A1:A3,"manual",E1:E3)

Evaluated with Ctrl+Shift+Enter, not just Enter. And copied down.
Book2
ABCDE
1jon - manualjon - autoauto
2jack - autojack - automanual
3marc - manualmarc - autoauto
Sheet2
 

scrupul0us

Well-known Member
Joined
Sep 7, 2004
Messages
641
not exactlw hat im looking for... this is what im delaing with

lets say in column A I have the name:

"smith, jon" and it appears 10 times

in column B is a dept value for that name... however he has 2 departments listed

i wanna be able to look for someones name in one column and manually set their department in another column

i have a few names like this that need to be looked for and have their departments changed (the departments arent the same)

even if its a snippet marco that i have to run multiple times for multiple names thats fine
 

dgpenny

New Member
Joined
Jan 1, 2004
Messages
46
It is always helpful to show what you want the results to be, not just describe them. Before and after is the best (for me anyway).
 

scrupul0us

Well-known Member
Joined
Sep 7, 2004
Messages
641

ADVERTISEMENT

_Column A_|_Column B_

Jon, Smith | 20
Jon, Smith | 20
Jon, Smith | 20
Jon, Smith | 20
Jon, Smith | 21
Jon, Smith | 22
Jon, Smith | 20
Doe, smith | 19
Doe, smith | 19
Doe, smith | 19
Doe, smith | 18
Doe, smith | 20

Now say I need all Jon, Smith's to be dept 22 and doe, smith's to be dept 16 instead of whats there.. i need to search for their name and then make the replacement in the dept column based on the new depts i know the are to be placed in

since im at home ATM i do not know the exact columns for name & dept but i do know they arent side by side

but out of my list of about 200 names i have 4 that need this kind of change
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Start by creating a small table on the side, listing the Names and Assigned Depts.

Now, If this is the full list and no more are to be added, then you can simply overwrite the current data in the department column with a vlookup formula.

Or you can add a helper column next to the current department column with the vlookup formula. Then you can simply hide the old column from view.

Formula: =VLOOKUP(A1,$E$1:$F$2,2,0) where A1 is the name to lookup, E1:F2 is the table range (you can replace this with a named reference, such as Table), the 2 is the column number in the table to extract the info from and the 0 looks for an exact match.
 

scrupul0us

Well-known Member
Joined
Sep 7, 2004
Messages
641

ADVERTISEMENT

unfotunately that isnt the kind of solution that i require... while that does work it needs to be a small macro solution todo this as the sheet length nears about 55,000 lines... cannot use helper columns, pull down formulas etc since it will bog down the overall macro way too much...

i know someone like nateo can help me out here

thanks for the assitance thus far NBVC
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Wondering if an automated filter, after prompting for the name to key on, then use SpecialCells(xlCellTypeVisible) might be a quick method.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> foo()
<SPAN style="color:#00007F">Dim</SPAN> Tgt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, RepWith
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
Tgt = Application.InputBox("Enter Target")
RepWith = Application.InputBox("Enter replacement value")
<SPAN style="color:#00007F">If</SPAN> [C:C].AutoFilter = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN>
    [C:C].AutoFilter
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
[C:C].AutoFilter Field:=1, Criteria1:=Tgt
<SPAN style="color:#00007F">With</SPAN> Range("L2:L" & Cells(Rows.Count, "L").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    .Value = RepWith
<SPAN style="color:#00007F">End</SPAN> With
[C:C].AutoFilter
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

UNTESTED...
 

Watch MrExcel Video

Forum statistics

Threads
1,118,167
Messages
5,570,640
Members
412,334
Latest member
ExcelForLifeDontHate
Top