Replacing Data Macro

[kyter21]

I have a monthly report with over 2,000 entries per column and it is dynamic (number changes monthly). I'm trying to replace individual department names with "department" but keep certain ones the same. For instance- A,B,C,D stay the same but E-Z need to be changed to "department". I first coded an If then macro but was unable to get it to loop and after researching looping realized that it may not be the best option especially with large datasets. Is an advanced filter macro the best option where I filter NOT A,B,C,D and then change the results to department? Or are there other suggestions?

I'm very new to macros (first day) with no programming experience at all. Thanks in advance!

 

[mirabeau]

hi kyter21,

Welcome to the MrExcel board, and hope that your problem is adequately addressed.

To get a clearly focussed answer it's a good idea to be as specific as possible about your problem. Often posting a shot of a sample worksheet is more informative than verbal descriptions. (There's plenty of instructions on the board about how to post such screenshots).

From reading your post I can't really see just what it is that you are looking for ...

 

[kyter21]

In Column G (2000+ entries), I need to change Dept E, Dept F, Dept G...to just Department and keep Dept A, Dept B, Dept C, Dept D the same. I hope this helps.

 

[tornhair]

Hi Kyter,

If it is not strictly necessary to do that with a macro then you can enter the following formula in column H and fill down to the end. Then you can copy the contents of column H and PasteSpecial/Values onto columnG.

Formula is:

=IF(OR(G1="Dept A",G1="Dept B",G1="Dept C", G1="Dept D"),G1,"Department")

Hope that works.

 

[mirabeau]

kyter21,

if you do want a macro, you can try this

Sub test()
Dim lstrow As Long
lstrow = Range("G" & Rows.Count).End(xlUp).Row
Range("G1:G" & lstrow).Name = "g"
Range("g") = Evaluate("if(right(g,1)>char(68),""Dept"",g)")
End Sub

This could also be used as a formula if you like.

 

[kyter21]

Thanks tornhair and mirabeau. I need a macro because it is just one step in a process of cleaning up data from a report to then analyze. I tested the macro given and it changed everything to Dept. except for the longer named departments.

To be clear, my screenshot did not contain the actual data. For example, Dept. A = Marketing, Dept. B = Advertising, etc., Dept. E = Gov Relations, Dept. F = Management so Dept. A would need to remain Marketing, Dept. B needs to remain Advertising but Dept. E (& F, G, H...) need to be changed to just Department.

Thanks in advance.

 

[mirabeau]

Thanks tornhair and mirabeau. I need a macro because it is just one step in a process of cleaning up data from a report to then analyze. I tested the macro given and it changed everything to Dept. except for the longer named departments.

To be clear, my screenshot did not contain the actual data. For example, Dept. A = Marketing, Dept. B = Advertising, etc., Dept. E = Gov Relations, Dept. F = Management so Dept. A would need to remain Marketing, Dept. B needs to remain Advertising but Dept. E (& F, G, H...) need to be changed to just Department.

Thanks in advance.

kyter21,

The macro I posted was tested on your screenshot data and it did just as you requested. Should it have done otherwise?

It you have different data setup then it's likely you need a somewhat different macro.

Could you post another screenshot showing datatype that is fully and fairly representative of the data you want processed by the macro?

 

[kyter21]

Just note that there are several thousand entries so not all departments are listed. However, I need PO Bureau, Marketing, Human Resources, and Admin to remain the same but all others to change to Department. So CEO, Security, Government Relations, etc. need to be changed to Department and Marketing = Marketing, Human Resources = Human Resources...

My original IF statement was:
=IF(OR(G2="Marketing",G2="PO BUREAU",G2="Admin",G2="Human Resources"), G2, "DEPARTMENT") and I would copy it down in Column H.

My first try at doing the macro I thought looping the IF statement down would be the best option but it takes a while to complete it. I was looking to see if there was a more efficient way to code a Macro to do the same task (advanced autofilter NOT for Marketing, PO Bureau...then copying "Department" down; a different way to copy & paste the IF function; a different function all together that accomplishes the same result).

Keep in mind the column is dynamic.

Couldn't get the picture to post. here's the link.
http://www.flickr.com/photos/20566110@N00/6818933774/

 

[mirabeau]

Try this one

Option Explicit
Sub test()
Dim Arr, cl As Range, a, flg As Byte
Dim i As Long, Lstrowg As Long

Arr = Array("PO Bureau", "Marketing", "Human Resources", "Admin")
Lstrowg = Range("G" & Rows.Count).End(xlUp).Row
Set cl = Range("G1:G" & Lstrowg)

For i = 1 To Lstrowg
    flg = 0
    For Each a In Arr
        If InStr(cl(i, 1), a) > 0 Then flg = 1: Exit For
    Next a
    If flg = 0 Then cl(i, 1) = "Department"
Next i

End Sub

 

[mirabeau]

or, if you want it somewhat faster:

Sub test2()

Dim Arr, cl, a, flg As Byte
Dim i As Long, Lstrowg As Long

Arr = Array("PO Bureau", "Marketing", "Human Resources", "Admin")
Lstrowg = Range("G" & Rows.Count).End(xlUp).Row
cl = Range("G1:G" & Lstrowg)

For i = 1 To Lstrowg
    flg = 0
    For Each a In Arr
        If InStr(cl(i, 1), a) > 0 Then flg = 1: Exit For
    Next a
    If flg = 0 Then cl(i, 1) = "Dept"
Next i

Range("G1:G" & Lstrowg) = cl

End Sub