Raw data--cleansing macro?

tc88 · Jul 6, 2011

Hello all-<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
This is my first post here, but I have been browsing for the past couple of weeks at my new job. I am an intern in the accounting/finance department at a publicly traded company here in the US and we are currently working on reducing waste in business processes. One of those processes revolves around exporting data from SAP into Excel, fixing some of the data, and then removing irrelevant data. I am using Excel 2010 on Windows XP SP 3.
The first step I have set up is to check if any of the data needs to be reformatted (it is missing a “0” in the middle of the number). I have the formula as follows, and it works correctly (Column M in dummy data below)

Code:

=IF(OR(LEFT(C2,6)="104001",LEFT(C2,6)="104003",LEFT(C2,6)="104004"),CONCATENATE("CTR ",LEFT(C2,2),"0",MID(C2,3,4)),D2)

<o

></o

>
From there, I use a nested IF based on the column for the above formula. That formula is as follows (Column N in dummy data below)

Code:

=IF(OR(M2="CTR 1004001",M2="CTR 1004003",M2="CTR 1004004"),M2,IF(AND(RIGHT(D2,5)>="12475",RIGHT(D2,5)<="12739"),D2,"IRRELEVANT"))

<o

></o

>
I have that column conditionally formatted to show “IRRELEVANT” with a red fill, and up to this point I have absolutely no problems and the formulas appear to work correctly. However, from there, I must manually delete all “IRRELEVANT” rows to get out the data for our specific facility. If this were a few rows it wouldn’t be a big deal but the raw data is ~2000 rows and my manually cleansed data is ~600 rows, so as you can see I am manually clearing out quite a bit of data.<o

></o

>
This is a process that is done monthly, and while I believe a macro is the best way to do this, I don’t have any experience writing macros/using VBA. I consider myself a strong Excel user, but I am not an expert.<o

></o

>

Below is some dummy data so you have a better idea of what I am talking about:

Excel Workbook

B

C

D

E

F

G

H

I

J

K

L

M

N

1

Cost Element

Name

AuxAcctAs1

Name of offsetting account

Offst.acct

Order

OffAct

CoCd

Val.in rep.cur.

Postg Date

Created on

Formatting Fix

Relevant?

2

Facilities

10357E EN DUMMY DATA

139670

1000

9,956.00

05/31/2011

06/02/2011

IRRELEVANT

3

Facilities

104004-PRJ DUMMY DATA

156295

1000

2,645.00

05/31/2011

06/01/2011

CTR 1004004

4

Fringe

10384 BD DUMMY DATA

115746

1000

21.56

05/31/2011

06/02/2011

IRRELEVANT

5

Fringe

10436 RD DUMMY DATA

115746

1000

545.21

05/31/2011

06/02/2011

IRRELEVANT

6

General

JCTE000204193

CTR 12708

A/P AMCL

210036

154710

S

1000

5.89

05/04/2011

05/05/2011

CTR 12708

7

General

JCTE000204193

CTR 1004003

A/P AMCL

210036

156295

S

1000

16.67

05/04/2011

05/05/2011

CTR 1004003

8

General

JCTE000204193

CTR 1004003

A/P AMCL

210036

157010

S

1000

6.28

05/04/2011

05/05/2011

CTR 1004003

9

Other

104003_DSTDUMMYDATA

156430

1000

-2,215.65

05/31/2011

06/02/2011

CTR 1004003

10

Other

12335_DST DISTRDUMMY DATA

156950

1000

-312.45

05/31/2011

06/02/2011

IRRELEVANT

Sheet1

Excel 2010

Cell Formulas
Range	Formula
M2	=IF(OR(LEFT(C2,6)="104001",LEFT(C2,6)="104003",LEFT(C2,6)="104004"),CONCATENATE("CTR ",LEFT(C2,2),"0",MID(C2,3,4)),D2)
N2	=IF(OR(M2="CTR 1004001",M2="CTR 1004003",M2="CTR 1004004"),M2,IF(AND(RIGHT(D2,5)>="12475",RIGHT(D2,5)<="12739"),D2,"IRRELEVANT"))

Thanks in advance for any assistance!
<o

></o

>

Andrew Poulsom · Jul 12, 2011

sous2817 said:
I'm aware that the "Sheet1.Range("N2").Select" isn't the best practice, but for the life of me, I can't figure out how to apply conditional formatting reliably without selecting the first cell.

If anyone else is still following this thread and can chime in with a non-Sheet1.Range("N2").Select solution, I'd appreciate it for my own education.

You can avoid selecting the cell if you use R1C1 reference style for the formula.

sous2817 · Jul 12, 2011

ARGH...that's what I thought. Thanks Andrew! I'll keep that bit in my back pocket for future reference.

tc88 · Jul 12, 2011

sous2817 said:

OK, try this:

Code:

Option Explicit
 
Sub Foo()
 
Dim lr As Long
Dim rng As Range
 
Application.ScreenUpdating = False
 
lr = Sheet1.Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
Sheet1.Range("M1").Value = "Formatting Fix"
Sheet1.Range("N1").Value = "Relevant"
Sheet1.Range("M2:M" & lr).Formula = "=IF(OR(LEFT(C2,6)=""104001"",LEFT(C2,6)=""104003"",LEFT(C2,6)=""104004""),CONCATENATE(""CTR "",LEFT(C2,2),""0"",MID(C2,3,4)),IF(OR(LEFT(C2,6)=""100404"",LEFT(C2,6)=""100403""),CONCATENATE(""CTR "",LEFT(C2,5),""0"",MID(C2,6,1)),D2))"
Sheet1.Range("N2:N" & lr).Formula = "=IF(OR(M2=""CTR 1004001"",M2=""CTR 1004003"",M2=""CTR 1004004""),M2,IF(OR(RIGHT(D2,7)=""1004001"",RIGHT(D2,7)=""1004003"",RIGHT(D2,7)=""1004004""),D2,IF(AND(RIGHT(D2,5)>=""12475"",RIGHT(D2,5)<=""12739""),D2,IF(OR(A2=""5050"",RIGHT(C2,7)=""charges""),""Check CTR"",""IRRELEVANT""))))"
 
 
Set rng = Sheet1.Range("B1:N" & lr)
 
With rng
    .AutoFilter Field:=13, Criteria1:= _
        "IRRELEVANT"
            On Error Resume Next
    .Offset(1, 0).Resize(.Rows.Count - 1, 1). _
            SpecialCells(xlCellTypeVisible).EntireRow.Delete
        On Error GoTo 0
 
    .AutoFilter
End With
lr = Sheet1.Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
 
Sheet1.Range("D2:D" & lr).Value = Sheet1.Range("M2:M" & lr).Value
 
Sheet1.Cells.FormatConditions.Delete
Sheet1.Range("N2").Select
Sheet1.Range("$N$2:$N$" & lr).FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=IF($N2=""Check CTR"",TRUE)"
Sheet1.Range("$N2:$N" & lr).FormatConditions(Sheet1.Range("$N2:$N" & lr).FormatConditions.Count).SetFirstPriority
    With Sheet1.Range("$N2:$N" & lr).FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
Sheet1.Range("$N2:$N" & lr).FormatConditions(1).StopIfTrue = False
 
Application.ScreenUpdating = True
End Sub

I'm aware that the "Sheet1.Range("N2").Select" isn't the best practice, but for the life of me, I can't figure out how to apply conditional formatting reliably without selecting the first cell.

If anyone else is still following this thread and can chime in with a non-Sheet1.Range("N2").Select solution, I'd appreciate it for my own education.

That works! There are some nuances in the data that are just going to require some human data cleansing, but this will make the process MUCH faster!

sous2817 · Jul 12, 2011

HURRAY!! Glad everything worked out. Good luck with the rest of your project and feel free to post back should you need anything else!

tc88 · Aug 3, 2011

sous2817 said:
HURRAY!! Glad everything worked out. Good luck with the rest of your project and feel free to post back should you need anything else!

Bringing this thread back for a quick question-

Each month, the raw data that this macro runs on is exported out of SAP into a temporary Excel file which is then saved as an appropriately named file. After it is cleansed (manually before this macro), the clean data is then copied into a master file.

Since I won't be here next month when they run this macro on their own, what's the best way to store this macro to make it simple and quick for them to use it?

sous2817 · Aug 3, 2011

Typically I have an "Instructions" tab with a "Press to run code" type of button. Then I have a "raw data" where the end-user copies the data and an "Output" tab. A similar approach may work for you...

tc88 · Aug 3, 2011

sous2817 said:
Typically I have an "Instructions" tab with a "Press to run code" type of button. Then I have a "raw data" where the end-user copies the data and an "Output" tab. A similar approach may work for you...

Is it possible to modify the most recent macro to keep the raw data on its own sheet, and then create a new worksheet with the cleansed data?

sous2817 · Aug 3, 2011

sure, not tested but this should work:

Code:

Option Explicit
 
Sub Foo()
 
Dim lr As Long
Dim rng As Range
 
Application.ScreenUpdating = False

Sheet2.Cells.Copy Destination:=Sheet1.Range("A1")

lr = Sheet1.Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
Sheet1.Range("M1").Value = "Formatting Fix"
Sheet1.Range("N1").Value = "Relevant"
Sheet1.Range("M2:M" & lr).Formula = "=IF(OR(LEFT(C2,6)=""104001"",LEFT(C2,6)=""104003"",LEFT(C2,6)=""104004""),CONCATENATE(""CTR "",LEFT(C2,2),""0"",MID(C2,3,4)),IF(OR(LEFT(C2,6)=""100404"",LEFT(C2,6)=""100403""),CONCATENATE(""CTR "",LEFT(C2,5),""0"",MID(C2,6,1)),D2))"
Sheet1.Range("N2:N" & lr).Formula = "=IF(OR(M2=""CTR 1004001"",M2=""CTR 1004003"",M2=""CTR 1004004""),M2,IF(OR(RIGHT(D2,7)=""1004001"",RIGHT(D2,7)=""1004003"",RIGHT(D2,7)=""1004004""),D2,IF(AND(RIGHT(D2,5)>=""12475"",RIGHT(D2,5)<=""12739""),D2,IF(OR(A2=""5050"",RIGHT(C2,7)=""charges""),""Check CTR"",""IRRELEVANT""))))"
 
 
Set rng = Sheet1.Range("B1:N" & lr)
 
With rng
    .AutoFilter Field:=13, Criteria1:= _
        "IRRELEVANT"
            On Error Resume Next
    .Offset(1, 0).Resize(.Rows.Count - 1, 1). _
            SpecialCells(xlCellTypeVisible).EntireRow.Delete
        On Error GoTo 0
 
    .AutoFilter
End With
lr = Sheet1.Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
 
Sheet1.Range("D2:D" & lr).Value = Sheet1.Range("M2:M" & lr).Value
 
Sheet1.Cells.FormatConditions.Delete
Sheet1.Range("N2").Select
Sheet1.Range("$N$2:$N$" & lr).FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=IF($N2=""Check CTR"",TRUE)"
Sheet1.Range("$N2:$N" & lr).FormatConditions(Sheet1.Range("$N2:$N" & lr).FormatConditions.Count).SetFirstPriority
    With Sheet1.Range("$N2:$N" & lr).FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
Sheet1.Range("$N2:$N" & lr).FormatConditions(1).StopIfTrue = False
 
Application.ScreenUpdating = True
End Sub

This assumes that your raw data is being put in Sheet2 and Sheet1 being where you want the end product to be.

tc88 · Aug 3, 2011

sous2817 said:

sure, not tested but this should work:

Code:

Option Explicit
 
Sub Foo()
 
Dim lr As Long
Dim rng As Range
 
Application.ScreenUpdating = False
 
Sheet2.Cells.Copy Destination:=Sheet1.Range("A1")
 
lr = Sheet1.Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
Sheet1.Range("M1").Value = "Formatting Fix"
Sheet1.Range("N1").Value = "Relevant"
Sheet1.Range("M2:M" & lr).Formula = "=IF(OR(LEFT(C2,6)=""104001"",LEFT(C2,6)=""104003"",LEFT(C2,6)=""104004""),CONCATENATE(""CTR "",LEFT(C2,2),""0"",MID(C2,3,4)),IF(OR(LEFT(C2,6)=""100404"",LEFT(C2,6)=""100403""),CONCATENATE(""CTR "",LEFT(C2,5),""0"",MID(C2,6,1)),D2))"
Sheet1.Range("N2:N" & lr).Formula = "=IF(OR(M2=""CTR 1004001"",M2=""CTR 1004003"",M2=""CTR 1004004""),M2,IF(OR(RIGHT(D2,7)=""1004001"",RIGHT(D2,7)=""1004003"",RIGHT(D2,7)=""1004004""),D2,IF(AND(RIGHT(D2,5)>=""12475"",RIGHT(D2,5)<=""12739""),D2,IF(OR(A2=""5050"",RIGHT(C2,7)=""charges""),""Check CTR"",""IRRELEVANT""))))"
 
 
Set rng = Sheet1.Range("B1:N" & lr)
 
With rng
    .AutoFilter Field:=13, Criteria1:= _
        "IRRELEVANT"
            On Error Resume Next
    .Offset(1, 0).Resize(.Rows.Count - 1, 1). _
            SpecialCells(xlCellTypeVisible).EntireRow.Delete
        On Error GoTo 0
 
    .AutoFilter
End With
lr = Sheet1.Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
 
Sheet1.Range("D2:D" & lr).Value = Sheet1.Range("M2:M" & lr).Value
 
Sheet1.Cells.FormatConditions.Delete
Sheet1.Range("N2").Select
Sheet1.Range("$N$2:$N$" & lr).FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=IF($N2=""Check CTR"",TRUE)"
Sheet1.Range("$N2:$N" & lr).FormatConditions(Sheet1.Range("$N2:$N" & lr).FormatConditions.Count).SetFirstPriority
    With Sheet1.Range("$N2:$N" & lr).FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
Sheet1.Range("$N2:$N" & lr).FormatConditions(1).StopIfTrue = False
 
Application.ScreenUpdating = True
End Sub

This assumes that your raw data is being put in Sheet2 and Sheet1 being where you want the end product to be.

What sheet should this macro be placed under in VBA?

Thanks again for all your help, I'm just lost when it comes to VBA.

sous2817 · Aug 3, 2011

No problem. I wouldn't put it in a sheet, I'd put it in a module.

And the code you'd put on the button click event would be:

Call Foo

Raw data--cleansing macro?

tc88

Board Regular

Andrew Poulsom

MrExcel MVP

Excel Facts

sous2817

Well-known Member

tc88

Board Regular

sous2817

Well-known Member

tc88

Board Regular

sous2817

Well-known Member

tc88

Board Regular

sous2817

Well-known Member

tc88

Board Regular

sous2817

Well-known Member

Forum statistics

Share this page

Raw data--cleansing macro?

Board Regular

MrExcel MVP

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock