topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Thursday March 28, 2024, 1:31 pm
  • Proudly celebrating 15+ years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: search and replace in excel file  (Read 2988 times)

kalos

  • Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 1,823
    • View Profile
    • Donate to Member
search and replace in excel file
« on: December 28, 2010, 04:44 PM »
hello

there are some data for some people in rows in an excel file

i want to do this:
check each row, and if found the value in column C to be 150, then add 70 to the existing value of the cell in the column D of that row
if find the value in column C to be 0, then replace the cell in the column C of that row with 150

any idea?

thanks
« Last Edit: December 28, 2010, 07:06 PM by kalos »

AndyM

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 616
    • View Profile
    • Donate to Member
Re: search and replace in excel file
« Reply #1 on: December 28, 2010, 08:24 PM »
If you want to run through the entire table automatically,you will have to use a macro.

If not a macro, you could use an "if" formula temporarily in a "helper" column (copied down to each row) to check column C and return 70 if C=150 and 0 (zero) if it doesn't.  Then add the entire helper column to column D (a choice in the Paste Special dialog box - make a backup first).  That takes care of Column D if C is 150 (add 70 if it is, add nothing if it's not).

Then do a temporary helper column to check if C=0.  If it is, return 150, if it's not, return the value in C.  Then copy that column over C (paste values, not the If formulas, again using Paste Special), replacing the zeros with 150 and leaving other values unchanged.

This takes longer to explain than to do (one formula copied down and the column added, a second formula copied down and the column copied, then delete two columns).  I'm sure there's a more elegant way, but if I'm only doing something once or twice it takes more time to think of the elegant way than it does to just plow ahead.  I typed this as if I were plowing ahead, and I'm not even going to check to see if I got it right.  You can check, and if I got the right idea and you need help with the If formulas, let me know.

For a macro approach, you do a loop stepping thru each row.  In each loop you check C and either change C or D accordingly.

« Last Edit: December 28, 2010, 08:29 PM by AndyM »

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: search and replace in excel file
« Reply #2 on: December 28, 2010, 09:49 PM »
if I understood what you were trying to do then the macro code might looks something like this (I've taken the same approach as andy, ie no testing)

Sub xxx()
Dim rowno As Integer
rowno = 2

Do
    If Cells(rowno, 3) = 150 Then Cells(rowno, 4).Value = Cells(rowno, 4).Value + 70
    If Cells(rowno, 3).Value = 0 Then Cells(rowno, 3).Value = 150
    rowno = rowno + 1
Loop Until Cells(rowno, 3) = ""

End Sub

if you have a substantial number of records this might take a while

kalos

  • Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 1,823
    • View Profile
    • Donate to Member
Re: search and replace in excel file
« Reply #3 on: December 28, 2010, 10:24 PM »
thanks!

is it also possible to delete the rows that contain the exact same value/data in the cells of specific columns?

eg the rows 3 and 5 contain the data '45' in the C column and the data '47' in the E column

thanks

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: search and replace in excel file
« Reply #4 on: December 28, 2010, 11:03 PM »
the following should work - note that it will delete the entire row, not just the range containing the data

Sub xxx()
Dim rowno As Integer
rowno = 2

Do
    If Cells(rowno, 3) = 150 Then Cells(rowno, 4).Value = Cells(rowno, 4).Value + 70   
    If Cells(rowno, 3).Value = 0 Then Cells(rowno, 3).Value = 150

    If Cells(rowno, 3) = 45 and cells(rowno,5) = 47 Then Rows(rowno).Delete Else rowno = rowno + 1

Loop Until Cells(rowno, 3) = ""End Sub