Raw data--cleansing macro?

[tc88]

Hello all-<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><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)

=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)

=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	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	CTR 12708
7	General	JCTE000204193	CTR 1004003	A/P AMCL	210036	156295	S	1000	16.67	05/04/2011	05/05/2011	CTR 1004003	CTR 1004003
8	General	JCTE000204193	CTR 1004003	A/P AMCL	210036	157010	S	1000	6.28	05/04/2011	05/05/2011	CTR 1004003	CTR 1004003
9	Other	104003_DSTDUMMYDATA				156430		1000	-2,215.65	05/31/2011	06/02/2011	CTR 1004003	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]

Welcome to MrExcel.

Can't you just AutoFilter your data for column N equals IRRELEVANT and delete the visible rows?

 

[tc88]

Welcome to MrExcel.

Can't you just AutoFilter your data for column N equals IRRELEVANT and delete the visible rows?

Thanks for your response. That occurred to me as well but they appear to want to automate things when possible, as this is just one task in a list of tasks for the end of month close process. If the time it takes to write a macro exceeds the benefit relative to just using AutoFilter, then I will make that case.

 

[sous2817]

Why not put the autofilter in a macro:

See post #4 here:

http://www.xtremevbtalk.com/showthread.php?t=300757

 

[tc88]

Why not put the autofilter in a macro:

See post #4 here:

http://www.xtremevbtalk.com/showthread.php?t=300757

As I said, I have no experience writing VBA so that might as well be Greek to me! I appreciate the link though, I am meeting with the local "Excel guru" early next week to discuss this further so maybe he will have an idea of how to tailor that to my specific needs.

If it is possible to put AutoFilter in a macro, could there be a macro that does the following basic steps?

1. Insert/autofill formula in column M to the last row
2. Insert/autofill formula in Column N to last row
3. Autofilter to show "IRRELEVANT" data
4. Delete all rows with "IRRELEVANT" in column N

 

[Andrew Poulsom]

You should be able to use the macro recorder to get most of that code. You will need to change the AutoFilter code to offset by one row and use SpecialCells(xlCellTypeVisible). That's covered in Colin Legg's code in the link that was posted.

 

[sous2817]

On a copy of your workbook, try this:

Sub Foo()

Dim lr As Long
Dim rng As Range

Application.ScreenUpdating = False

lr = Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
Set rng = 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

Also, I would qualify the range reference fully, meaning it should apply to a specific sheet and not just whatever range of the active sheet which is how it's currently set up. What's the sheet name where you'd like the macro to run?

 

[tc88]

On a copy of your workbook, try this:

Sub Foo()
 
Dim lr As Long
Dim rng As Range
 
Application.ScreenUpdating = False
 
lr = Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
Set rng = 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

Also, I would qualify the range reference fully, meaning it should apply to a specific sheet and not just whatever range of the active sheet which is how it's currently set up. What's the sheet name where you'd like the macro to run?

It's just a default "Sheet1" right now. I will try this out in a bit, thanks!

 

[sous2817]

OK, try this then:

Sub Foo()

Dim lr As Long
Dim rng As Range

Application.ScreenUpdating = False

lr = Sheet1.Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
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

 

[tc88]

OK, try this then:

Sub Foo()
 
Dim lr As Long
Dim rng As Range
 
Application.ScreenUpdating = False
 
lr = Sheet1.Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
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

That worked great! Thank you so much for your help.

Is it possible to have the macro automatically insert the formulas from Columns M and N before looking for the "IRRELEVANT" and deleting the rows?