Change adjacent cell where a cell matches a certain value

Question

If I have the following table:

------------------
| X | helloworld |
------------------
| X | random1234 |
------------------
| X | random5678 |
------------------
| X | helloworld |
------------------
| X | random9123 |
------------------

How can I set the value of the first cell to the left of each occurence of helloworld to Y?

After processing, I would expect the above example to be:

------------------
| Y | helloworld |
------------------
| X | random1234 |
------------------
| X | random5678 |
------------------
| Y | helloworld |
------------------
| X | random9123 |
------------------

To clarify, my spreadsheet contains thousands of these occurrences, I'm looking for a bulk operation, in pseudocode:

for every row:
    if column_B = 'helloworld':
        column_A = 'X'
    if column_B = 'random':
        column_A = ...

If the only values in first column are X & Y you can put a simple IF function in that column checking value of adjacent cell. =IF(B1="helloworld","Y","X"). Put this formula in cell A1 and drag (copy) it down all the way — patkim, Jul 29 '16 at 09:40
@pat2015 actually that was just an example, the spreadsheet has thousands of rows, and the first column has ~50 possible values (they're actually words, not characters). Your solution would likely work but it sounds ill-suited to write an if statement for ~50 values in those cells. It may be the only way though. — user2018084, Jul 29 '16 at 09:42
You can copy column A to column C and use C as helper column. Formula in A1 then becomes =IF(B1="helloworld","Y",C1) — patkim, Jul 29 '16 at 09:44

score 1 · Accepted Answer · edited Mar 20 '17 at 10:17

This VBa does it.

There is no option to undo, so take a back up first!

Sub WalkThePlank()

Dim updateColumn As String
updateColumn = "A"           'update this ye filthy seadog if needed! This be the first column

Dim contentColumn As String
contentColumn = "B"         'aye, scrub the deck and update if the "helloworld" isn't in column B

Dim startRow As Integer
startRow = 1                  'enter the start row or be fed to the sharks  

Do While (Range(contentColumn & startRow).Value <> "")

    Dim val As String
    val = Range(contentColumn & startRow).Value

    Select Case val

    Case "helloworld"
        Range(updateColumn & startRow).Value = "Y"

    Case Else
        Range(updateColumn & startRow).Value = "X" ' This is the "default value

    End Select        

    startRow = startRow + 1

Loop    

End Sub

To add another case, such as if the search word was Goodbyeworld, update the code to

    Select Case val

    Case "helloworld"
        Range(updateColumn & startRow).Value = "Y"

    Case "Goodbyeworld" ' CASE SENSITIVE!!!!
        Range(updateColumn & startRow).Value = "A"

    Case Else
        Range(updateColumn & startRow).Value = "X" ' This is the "default value

    End Select

How do I add VBA in MS Office?

Before

After

Change adjacent cell where a cell matches a certain value

1 Answers1