Creating a Macro that will run functions in certain columns.

[mxc5425]

Hi Everyone!

I have 3 tabs “Instructions”, “Raw_Data”, and “Clean_Data”. In the Clean_Data tab I have the following formulas written directly on the calc spreadsheet.

In Column A: =IF(ISNUMBER($Raw_Data.A1)=1,$Raw_Data.A1,“WRONG!”)

In Column B: =IF(ISNUMBER($Raw_Data.B1)=1,$Raw_Data.B1,“WRONG!”)

In Column C: =IF(ISNUMBER($Raw_Data.C1)=1,$Raw_Data.C1,“WRONG!”)

In Column E: =$Raw_Data.B1

In Column F: =IF(ISNUMBER($Raw_Data.A1)=1,“I’m a number”,IF(LEFT($Raw_Data.A1)="*",MID($Raw_Data.A1,2,4),MID($Raw_Data.A1,1,4)))

In Column G: =IF(F453=“I’m a number”," “,IF(MID($Raw_Data.A1,6,4)=“DUPL”,” “,IF(MID($Raw_Data.A1,7,4)=“DUPL”,” “,IF(MID($Raw_Data.A1,6,1)=”/",MID($Raw_Data.A1,7,4),MID($Raw_Data.A1,6,4)))))

In Column H: =IF(MID($Raw_Data.A1,6,4)=“DUPL”,“DUPL”,IF(MID($Raw_Data.A1,7,4)=“DUPL”,“DUPL”,IF(MID($Raw_Data.A1,11,4)=“DUPL”,“DUPL”,IF(MID($Raw_Data.A1,12,4)=“DUPL”,“DUPL”," "))))

In Column I: =IF(LEFT($Raw_Data.A1)="",""," ")

Is there anyway I can enter these formulas into a macro and have them run through all the rows in the raw data sheet that contain text and the print the formula values on the clean data sheet? I’m trying to automate as much as possible and limit the chances for someone to be able to change the formulas by mistake.

Thanks for you help!

[JohnSUN]

Not sure if you can explain why you use such complex formulas to perform very simple checks. These formulas are difficult to read, not only to understand. For example, in formula for column H you are looking for the word “DUPL” in different places (position 6,7,11,12) in one string. Why don’t you just check the presence of this word in the string? The meaning of the formula for the column I no one can explain except you.

If you edit your question and attach a file with samples of raw data and the resulting clean data to it, it is possible that your question will receive a quick and accurate answer. Now this answer is very difficult to give - there is little information to think about.