Excel 2007 : Conditional Formatting: Multiple conditions

[mike_braden]

All,

I am new to the Forum and thank you all in advance for your help. I am fairly adequate with Excel, but Programming and macros are my ignorance.

I am trying to simplify my life and write a code that when I paste data from a report it will extrapolate out to the corresponding sheets based off of the data it needs. Example is as follows, all US State Based info:

I will paste data into "Master Leads" Worksheet, Any leads from states ID, MT, OR, & WA, the row associated with that state gets copied to Worksheet "Agent 1". Each Agent worksheet getting leads from their respective territory which i can add in myself, I just don't even know where to start.

Again, I appreciate all help and knowledge on the subject, if this is of no challenge to you that is great, I may have some VPN reporting questions in the near future.

Thanks to all!

[JBeaucaire]

I would add a column to the "master leads" and enter the "territory code" there. That can occur via a formula of some kind, or simply enter the agent. The entries in this column work best if they match the names of the other sheets exactly.

Then, here's a macro for parsing rows of data from one sheet to many sheets named for the same values in a specific column.

Sheet1 to Many Sheets

It not only can parse the rows, it can create the sheets if they are missing. There is a sample sheet there where you can test this out. Just edit the macro so the vCol variable points to the correct column where you put in the agent codes. Also edit the ws variable sheetname from "data" to "Master Leads".

It should work at that point.

[mike_braden]

Jerry,

Thank you for your prompt reply.

The state codes that will fall into column G: are by formula via the reporting software and are selected by a drop down on the website, so they will be static in compliance with USA State abbreviations, since they are already set, is there still a need for a territory code? Or are you suggesting that I implement a code for "Agent 1" and his states of say ID, OR, MT & WA are coded as "4" and therefore sent to his page based off of the code not the state abbreviation?

The pages are set with the Agents name in my actual workbook, one of which is a catchall for all states not covered by an agent. so there will only be the 7 pages, no more, no less.

I am incredibly green when it comes to entering Macro code and manipulating such codes, would you be so kind as to guide me how to enter the code in excel and which values in your incredible formula I should look at changing??

Please forgive my ignorance.

Thank you!

[JBeaucaire]

If you're not going to add an agent assignment column, then you'll need to add an Assignment sheet, at least. On this sheet create a two column list. Column A is state abbreviations, Column B is the agent assigned. Include your catchall here.

Do that and post up your modified sheet, I'll see about editing that macro for you to parse all the data out based on the data sheet and the assignment sheet.

[mike_braden]

Jerry,

Thanks again so much.

Here is the modified workbook with the Assignment sheet at the end.

Thank you for all of your help.

[JBeaucaire]

Here's the tweaked version of the macro.

Option Explicit

Sub ParseItems()
'Author:    Jerry Beaucaire
'Date:      11/11/2009
'Summary:   Based on selected column, data is filtered to individual sheets
'           Creates sheets and sorts sheets alphabetically in workbook
'           6/10/2010 - added check to abort if only one value in vCol
'           7/22/2010 - added ability to parse numeric values consistently
Dim LR As Long, Itm As Long, MyCount As Long, vCol As Long, stCol As Long
Dim ws As Worksheet, MyArr As Variant, vTitles As String

Application.ScreenUpdating = False

'Column to evaluate from, column A = 1, B = 2, etc.
   vCol = 27
 
'Sheet with data in it
   Set ws = Sheets("Master Leads")

'Range where titles are across top of data, as string, data MUST
'have titles in this row, edit to suit your titles locale
    vTitles = "A1:Z1"
   
'Spot bottom row of data
   LR = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

'Add key
    stCol = ws.Rows(1).Find("State", LookIn:=xlValues, LookAt:=xlWhole).Column
    With ws.Range("AA2:AA" & LR)
        .FormulaR1C1 = "=VLOOKUP(RC" & stCol & ", Assignment!C1:C2, 2, 0)"
        .Value = .Value
    End With

'Get a temporary list of unique values from column A
      ws.Columns(vCol).SpecialCells(xlConstants).AdvancedFilter _
        Action:=xlFilterCopy, CopyToRange:=ws.Range("EE1"), Unique:=True

'Sort the temporary list
    ws.Columns("EE:EE").Sort Key1:=ws.Range("EE2"), _
        Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

'Put list into an array for looping
'(values cannot be the result of formulas, must be constants)
    MyArr = Application.WorksheetFunction.Transpose(ws.Range("EE1:EE" _
        & Rows.Count).SpecialCells(xlCellTypeConstants))

'clear temporary worksheet list
    ws.Range("EE:EE").Clear

'Turn on the autofilter, one column only is all that is needed
    ws.Columns(vCol).AutoFilter

'Loop through list one value at a time
'The array includes the title cell, so we start at the second value in the array
'In case values are numerical, we convert them to text with ""
    For Itm = 2 To UBound(MyArr)
        ws.Columns(vCol).AutoFilter Field:=1, Criteria1:=MyArr(Itm) & ""
   
        If Not Evaluate("=ISREF('" & MyArr(Itm) & "'!A1)") Then    'create sheet if needed
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = MyArr(Itm) & ""
        Else                                                      'clear sheet if it exists
            Sheets(MyArr(Itm) & "").Move After:=Sheets(Sheets.Count)
            Sheets(MyArr(Itm) & "").Cells.Clear
        End If
   
        ws.Range("A" & Range(vTitles).Resize(1, 1) _
            .Row & ":N" & LR).Copy Sheets(MyArr(Itm) & "").Range("A1")
       
        ws.Columns(vCol).AutoFilter Field:=1
        MyCount = MyCount + Sheets(MyArr(Itm) & "") _
            .Range("A" & Rows.Count).End(xlUp).Row - 1
        Sheets(MyArr(Itm) & "").Columns.AutoFit
    Next Itm
   
'Cleanup
    ws.Activate
    ws.AutoFilterMode = False
    ws.Columns(vCol).ClearContents
    MsgBox "Rows with data: " & (LR - 1) & vbLf & "Rows copied to other sheets: " _
                & MyCount & vbLf & "Hope they match!!"

Application.ScreenUpdating = True
End Sub

I installed that into your workbook and hid the ASSIGNMENT sheet.

Remember, you don't actually have to make the agent sheets. Just set the STATE assignments on the assignments sheet and whatever names you put in column B will be used. The macro will create any sheets that it needs to.

[mike_braden]

Jerry,

Again, thank you so incredibly much. I will give it a go tomorrow morning when I run the report. You truly are a genius at excel. So in reality, I can change the "Agent" fields and the program will run new worksheets to whatever the states are associated with?

In the near future, I may be contacting you, with funding, to write a program that will access a vpn, pull this report, format it, extrapolate it, and email it.

Your help is so greatly appreciated, and your timely response.

I will give you an update tomorrow.