Find "1969" in any column and replace cell directly to the left with "Completed"

[DC01]

Hi, I have a huge spreadsheet report. It comes with erroneous data that I need to fix. Always the same, a date that is from 1969. The date column is deleted, but before I can do that, I have to find every instance of "1969" and replace the cell to the left of it with "Completed"

I know Excel at a 6 out of 10 skill user. I know Find Replace. I'm not very good with formulas or Vlook up, etc. I cannot figure out how to search all columns, find "1969" and replace the value in the cell to the left with "Completed"

Kind Regards, and thank you for any assistance.
DC.

	Email address	Topic 1		Topic 2
Name 1	Email 1	Not completed		Not completed
Name 2	Email 2	Completed	Friday, 2 December 2022, 6:34 PM	Not completed
Name 3	Email 3	Completed	Wednesday, 27 July 2022, 1:07 PM	Completed	Wednesday, 27 July 2022, 1:36 PM
Name 4	Email 4	Completed	Thursday, 28 April 2022, 10:19 AM	Not completed	Wednesday, 31 December 1969, 7:00 PM
Name 5	Email 5	Not completed		Not completed
Name 6	Email 6	Completed	Tuesday, 26 April 2022, 1:58 PM	Completed	Tuesday, 26 April 2022, 2:32 PM
Name 7	Email 7	Not completed		Not completed	Wednesday, 31 December 1969, 7:00 PM
Name 8	Email 8	Not completed		Not completed
Name 9	Email 9	Completed	Wednesday, 4 January 2023, 10:37 AM	Completed	Wednesday, 4 January 2023, 10:53 AM
Name 10	Email 10	Completed	Tuesday, 27 December 2022, 3:07 PM	Completed	Tuesday, 27 December 2022, 7:54 PM
Name 11	Email 11	Completed	Tuesday, 26 April 2022, 1:26 PM	Completed	Saturday, 7 January 2023, 8:09 AM

 

[DanteAmor]

Hi and welcome to MrExcel!

Try this macro:

Sub replacedata()
  Dim r As Range, f As Range, cell As String
  Set f = Cells.Find("1969", , xlValues, xlPart)
  If Not f Is Nothing Then
    cell = f.Address
    Do
      If f.Column > 1 Then f.Offset(, -1).Value = "Completed"
      Set f = Cells.FindNext(f)
    Loop While Not f Is Nothing And f.Address <> cell
  End If
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (replacedata) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Micron]

Why would you look in all columns when many columns will never have 1969 in them? Like Name or email columns for example. Wouldn't the search be on column F, or whatever the rightmost column above is?

 

[DC01]

Hi and welcome to MrExcel!

Try this macro:

Sub replacedata()
  Dim r As Range, f As Range, cell As String
  Set f = Cells.Find("1969", , xlValues, xlPart)
  If Not f Is Nothing Then
    cell = f.Address
    Do
      If f.Column > 1 Then f.Offset(, -1).Value = "Completed"
      Set f = Cells.FindNext(f)
    Loop While Not f Is Nothing And f.Address <> cell
  End If
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (replacedata) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Dante! That worked perfectly. I get a new report every morning and I have to do this find, paste Completed into cell to the left of it. Can be 100 of them. Its crazy. Thanks so much for your time!

 

[DC01]

Why would you look in all columns when many columns will never have 1969 in them? Like Name or email columns for example. Wouldn't the search be on column F, or whatever the rightmost column above is?H

Hi there, I need/do this because there are 10+ columns with potential "1969" values where the cell to the left needs to be set to completed. One of those things where I don't control the output that I start to work from. I have to deal with what is provided to me and clean it up before I can use it. So it is not just Column F. it is every other column X 10.

 

[Peter_SSs]

Welcome to the MrExcel board!

I have a huge spreadsheet report.

How big is your "huge"?

The date column is deleted,

Do you mean date columns are deleted (that is the 4th & 6th column in your sample above)?

If so, with a big file, especially if there are quite a few 1969 dates, you might find this faster.

Sub Completed1969()
  With ActiveSheet.UsedRange
    .Replace What:="* 1969,*", Replacement:="#N/A"
    .SpecialCells(xlConstants, xlErrors).Offset(, -1).Value = "Completed"
  End With
End Sub

 

[DC01]

Welcome to the MrExcel board!


How big is your "huge"?



Do you mean date columns are deleted (that is the 4th & 6th column in your sample above)?

If so, with a big file, especially if there are quite a few 1969 dates, you might find this faster.

Sub Completed1969()
  With ActiveSheet.UsedRange
    .Replace What:="* 1969,*", Replacement:="#N/A"
    .SpecialCells(xlConstants, xlErrors).Offset(, -1).Value = "Completed"
  End With
End Sub

Hi and Thanks Peter.
Huge as in large xcel dump from our LMS (learning Mgmt System) that covers some 15+ modules and the dates of the test and the results to the left of the date for some hundreds of users at any time. So I was having to do a Ctrl+F to find and then move one cell to the left and change "Not Incomplete" to "Completed" Was a real nauseating process.

The code that I was first provided takes care of this in 2 seconds.
I can try yours as well. But it wont be any faster in that 1 second or 2 seconds wont make a difference to me. It was the 10 minutes down to 2 seconds that saved me!

Regardless, Thanks much for your additional support.

 

[Peter_SSs]

I can try yours as well. But it wont be any faster in that 1 second or 2 seconds wont make a difference to me.

Fair enough, but you might as well at least give it a try?