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

>

sous2817 · Jul 6, 2011

Yep..try this one:

Code:

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)),D2)"
Sheet1.Range("N2:N" & lr).Formula = "=IF(OR(M2=""CTR 1004001"",M2=""CTR 1004003"",M2=""CTR 1004004""),M2,IF(AND(RIGHT(D2,5)>=""12475"",RIGHT(D2,5)<=""12739""),D2,""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
Application.ScreenUpdating = True
End Sub

Wasn't sure if you needed the headers put in or not for columns M and N, I put them in.

tc88 · Jul 6, 2011

sous2817 said:

Yep..try this one:

Code:

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)),D2)"
Sheet1.Range("N2:N" & lr).Formula = "=IF(OR(M2=""CTR 1004001"",M2=""CTR 1004003"",M2=""CTR 1004004""),M2,IF(AND(RIGHT(D2,5)>=""12475"",RIGHT(D2,5)<=""12739""),D2,""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
Application.ScreenUpdating = True
End Sub

Wasn't sure if you needed the headers put in or not for columns M and N, I put them in.

Worked like a charm, thank you again for all of your help!!!

One more question: each month this data is pulled out of SAP. As far as running the macro and cleansing the data, would the best way be to create a macro workbook to copy/paste the data in? AFAIK they copy/paste the cleansed data into another worksheet, so it could be copied from the macro workbook.

sous2817 · Jul 6, 2011

Yes, I think that having a macro-enabled workbook w/ a single sheet named "SAP Output" would be a perfectly fine way to do it.

tc88 · Jul 6, 2011

sous2817 said:
Yes, I think that having a macro-enabled workbook w/ a single sheet named "SAP Output" would be a perfectly fine way to do it.

I have just worked with the person who receives the data from me, is it possible once the "IRRELEVANT" data has been deleted to cut and paste the remaining values into column D? Thanks again, you've been a huge help.

sous2817 · Jul 6, 2011

BA-BOOM!

Code:

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)),D2)"
Sheet1.Range("N2:N" & lr).Formula = "=IF(OR(M2=""CTR 1004001"",M2=""CTR 1004003"",M2=""CTR 1004004""),M2,IF(AND(RIGHT(D2,5)>=""12475"",RIGHT(D2,5)<=""12739""),D2,""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
Application.ScreenUpdating = True
End Sub

tc88 · Jul 7, 2011

sous2817 said:

BA-BOOM!

Code:

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)),D2)"
Sheet1.Range("N2:N" & lr).Formula = "=IF(OR(M2=""CTR 1004001"",M2=""CTR 1004003"",M2=""CTR 1004004""),M2,IF(AND(RIGHT(D2,5)>=""12475"",RIGHT(D2,5)<=""12739""),D2,""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
Application.ScreenUpdating = True
End Sub

OK, I ended up needing to change the formula around as it wasn't catching everything that is related to our facility based on the raw data.

The new formula for column M is

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)),IF(OR(LEFT(C2,6)="100404",LEFT(C2,6)="100403"),CONCATENATE("CTR ",LEFT(C2,5),"0",MID(C2,6,1)),D2))

Column N is the same

Code:

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

I tried using your macro to change the code as follows:

Code:

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(AND(RIGHT(D2,5)>=""12475"",RIGHT(D2,5)<=""12739""),D2,""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
Application.ScreenUpdating = True
End Sub

However, it comes up as a red "X" error under Microsoft Visual Basic for Applications and has the error message "400" and nothing else.

sous2817 · Jul 7, 2011

Interesting...I don't see anything offhand that would cause an error. Did my macro work before you made any changes? And when the error comes up, do you have the option to "debug"?

sous2817 · Jul 7, 2011

Also, you were missing a closing ) on your first if formula. This ran without issue on my machine using the test data you posted in your first post:

Code:

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(AND(RIGHT(D2,5)>=""12475"",RIGHT(D2,5)<=""12739""),D2,""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
Application.ScreenUpdating = True
End Sub

tc88 · Jul 7, 2011

sous2817 said:
Interesting...I don't see anything offhand that would cause an error. Did my macro work before you made any changes? And when the error comes up, do you have the option to "debug"?

I didn't get to try it yesterday before I left the office, but I didn't have the option to debug either.

It looks like macro in the post above me works and got the same row count as when my co-worker completed the process manually. I just need to verify the totals are correct for column J and I should be set for the meeting this afternoon. Thanks again for all of your help, I really appreciate it.

sous2817 · Jul 7, 2011

Great, glad I could help! Feel free to post back should you need anything else.

Raw data--cleansing macro?

tc88

Board Regular

sous2817

Well-known Member

Excel Facts

tc88

Board Regular

sous2817

Well-known Member

tc88

Board Regular

sous2817

Well-known Member

tc88

Board Regular

sous2817

Well-known Member

sous2817

Well-known Member

tc88

Board Regular

sous2817

Well-known Member

Forum statistics

Share this page

Raw data--cleansing macro?

Board Regular

Well-known Member

Excel Facts

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

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