Data extraction and formatting using macro

Hi 4gurus

Your profile shows Excel 2007 & 2010 yet you posted .xls Files. What Versions of Excel must the Code accommodate?

Hi 4gurus

The Code in the attached has been tested in Excel 2003 and 2007 and appears to do as you require. I'm not certain of the Sort Sequence as I'm not able to follow your description.

CTRL + z will fire the Code and Output will be written to the same Folder where the Sample File lives..

Hi 4gurus

Replace the Module1 Code with this...still not certain of your Sort Requirements

Public myArray() As Variant
Public Arr As Variant
Option Explicit

Sub DoStuff()
    Dim WB As Workbook
    Dim ws As Worksheet
    Dim LR As Long, x As Long
    Dim Rng As Range

    Application.ScreenUpdating = False
    If Not Evaluate("ISREF(Lists!A1)") Then
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Lists"
        Sheets("Lists").Range("C1").Value = "Owners"
    Else
        Sheets("Lists").Cells.ClearContents
        Sheets("Lists").Range("C1").Value = "Owners"
    End If

    Set ws = Sheets("RawData")
    With ws
        LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row
        .Range("A1:A" & LR).AdvancedFilter Action:=xlFilterCopy, _
                CopyToRange:=Sheets("Lists").Range("A1"), Unique:=True
        ActiveWorkbook.Names.Add Name:="Owners", RefersTo:= _
                "=OFFSET(Lists!$A$2,0,0,(COUNTA(Lists!$A:$A)-1),1)"

        UserForm1.Show

        Sheets("Lists").Range("C2").Resize(UBound(Arr, 1)) = WorksheetFunction.Transpose(Arr)
        ActiveWorkbook.Names.Add Name:="MyOwners", RefersTo:= _
                "=OFFSET(Lists!$C$1,0,0,(COUNTA(Lists!$C:$C)),1)"

        .Range("A1:A" & LR).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
                Sheets("Lists").Range("MyOwners"), Unique:=False

        Set Rng = .Range("A1:H" & LR)
        Application.SheetsInNewWorkbook = 1
        Set WB = Workbooks.Add
        Rng.SpecialCells(xlCellTypeVisible).Copy WB.Worksheets(1).Range("A1")
        With WB
            With Sheets("Sheet1")
                .Name = "Extracted Data"
                .Range("E:F").NumberFormat = "$#,##0"
                .Columns(4).EntireColumn.Delete
                .Columns(1).EntireColumn.Delete
                LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious).Row


                .Range("A1:F" & LR).AutoFilter Field:=5, Criteria1:="NA"
                Set Rng = .AutoFilter.Range
                x = Rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
                If x >= 1 Then
                    .Range("A2:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                End If
                .AutoFilterMode = False

                .Range("A:F").Sort Key1:=Range("C2"), Order1:=xlDescending, Key2:=Range("D2") _
                        , Order2:=xlDescending, Header:= _
                        xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
            End With
            Application.DisplayAlerts = False
            .SaveAs ThisWorkbook.Path & "\ Output" & ".xls", FileFormat:=xlNormal
            Application.DisplayAlerts = True
            .Close
        End With
        .ShowAllData
    End With
    Application.DisplayAlerts = False
    Sheets("Lists").Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub



Sub CreateFilter()
    Dim i As Long, n As Long

    ReDim myArray(UserForm1.ListBox1.ListCount)
    If UserForm1.ListBox1.ListIndex = -1 Then
        MsgBox "You didn't select any Names"
    End If
    For i = 0 To UserForm1.ListBox1.ListCount - 1
        If UserForm1.ListBox1.Selected(i) = True Then
            myArray(n) = UserForm1.ListBox1.List(i)
            n = n + 1
        End If
    Next i
    ReDim Preserve myArray(n)
    Arr = myArray
    Unload UserForm1
End Sub

Hi 4gurus

Why don't you SHOW me the Final Product sorted so I don't have to guess. Leave the Owners Column in the Final Sorted Product, I'll delete it later.

I'll need to look at this

make a form where I can pick and choose the columns one would like to have in output file

Hi Jaslake,

I am extremely sorry for not being clear on what Macro should do, but now I have clear understanding of what macro should do..

Macro Conditions:

First macro should give user form to choose columns, then user form to choose Category(IT&Telecom), then user form for Region, then Priority(Low high medium), and then user form for Owners.

Please note that these column's could be anywhere in excel sheet for instance category and Region column could be in column AA and Aj respectively in real-time environment and same with other columns

Formatting condition

First sort owners A to Z then Sort Price (Largest to smallest) by going to sort by color > custom sort > add level> then price usd.

Note: this Price column could be anywhere in excel sheet.

Hope I am clear this time. If not I will try to come up with better examples...Sorry again

Hi 4gurus

What version(s) of Excel must the Code accommodate?

Are Column Headers ACTUALLY in the First Row in your "real-time environment" Workbook?

Does the Workbook reflect the ACTUAL Structure of your "real-time environment" Workbook (knowing that the Column Headers may not be in the same order)?

Are these the ACTUAL Column Names...
Category
Region
Priority
Owners
Price USD
Final Price USD

Regarding this...are these the Columns the User wishes to SEE in the Final Report?

give user form to choose columns

If so, it's my understanding the User will ALWAYS SEE Category, Region, Priority, Owners, Price USD and Final Price USD in addition to the Columns the User chooses.

Please clarify...

Hi 4gurus

...Sorry...duplicate post...

Hi 4gurus

I don't know...you tell me...

Hi Jaslake,

Looks great and works like a charm but few things has to be addressed and tweaked

Firstly, In columns list(userform) I observed that the last column was not captured in the list so when I entered a dummy column beside "Upload" header it showed "Upload" in the column list.

Secondly, In category when I select any of the items the user form list should update itself in region, priority, and owners list

example: Let's say I choose Capital in category list first the user form region list should update and show only APAC as it is only present in APAC and Priority list will update to just high and medum, and the owner list will have just Yuan's name

Similary If I were to click Latam in Region list "first" it should update Category, Priority and Owners list (userform)
Same goes with Owners if I select them first or anything in Priority list.

Thirdly, Let's say I don't select anything in any of the list that is Column, Category, Region, Priority Owners
and click "GO" it should take raw data and have it in the output file.

Hope I am clear this time and once again wonderful macro work Jaslake.

Thanks!

4gurus

Hi 4gurus

Perhaps another can write the Code to allow the User to select any Field at random and update the respective Lists.

What you're suggesting could possibly be done with Dependant ComboBoxes (ComboBoxes are Single Select...not Multi Select). I have neither the desire nor the talent to pursue this approach.

If you wish to take the approach whereby the User selects in a predefined order let me know...I'll work with it.

Hi 4gurus

This has been fixed

Firstly, In columns list(userform) I observed that the last column was not captured in the list so when I entered a dummy column beside "Upload" header it showed "Upload" in the column list.

A Toggle Button has been added to the UserForm for this

add "select all" buttion in the user form option for "Owners list"

Hi 4gurus

Give me a list of all the Column Names you wish to be formatted as Currency (or does it not matter except for the two you mentioned).

Hi Jaslake

I have the pasted the column names below which should be formatted to currency. So when ever I select any of these columns below in the column list (userform) and select other required options as required the output file should have the columns I selected (any of the below ones) formatted to currency.

Please note: Most of the columns below don't have numbers in the samplecopy v5 I shared with you; but they are the ones to be formatted when I select them in

Current Year Spend (USD) Current Year Save (USD)

Annualized BCS Spend (USD) Annualized BCS Save (USD) Current Year BCS Spend (USD) Current Year BCS Save (USD)
Annualized HERO Spend (USD) Annualized HERO Save (USD) Current Year HERO Spend (USD) Current Year HERO Save (USD)
Annualized HMT Spend (USD) Annualized HMT Save (USD) Current Year HMT Spend (USD) Current Year HMT Save (USD)
Annualized PS Spend (USD) Annualized PS Save (USD) Current Year PS Spend (USD) Current Year PS Save (USD)
Annualized Forp Spend (USD) Annualized Forp Save (USD) Current Year Forp Spend (USD) Current Year Forp Save (USD)
Annualized AMERICAS Spend (USD) Annualized AMERICAS Save (USD) Current Year AMERICAS Spend (USD) Current Year AMERICAS Save (USD)
Annualized EMEA Spend (USD) Annualized EMEA Save (USD) Current Year EMEA Spend (USD) Current Year EMEA Save (USD)
Annualized APAC Spend (USD) Annualized APAC Save (USD) Current Year APAC Spend (USD) Current Year APAC Save (USD)

Thanks!

4gurus

Hi 4gurus

So...tell me about the Sort...I have never been clear.

Based on what you've written I see the Sort being this:

First Sort............. Project Owner in Alphabetic Order (A - Z)
Second Sort......... Annualized Spend (USD) (High to Low)
Third Sort............ Annualized Save (USD) (High to Low)

Is this correct?

Hi 4gurus

I'll have something for you in a short while.

Hi 4gurus

Try the Code in the attached...

Hi Jaslake,

This one worked beautifully, but I had to change the column name For Deck to MOR Deck in the actual file as per the request which threw up debug error and the formatting to currency failed for USD columns; I think because of this change I am trying to make.

Could you please change the name of this column to "MOR Deck" and send it back to me. I think we are very very close to finishing this.

In fact I could not change the column name (For Deck) in the excel sheet it said The user has restricted value that cannot be entered

Thanks!

4gurus

Hi 4gurus

I ran this through several times...seems to work. Let me know.

Hi 4gurus

You did what?

Hi 4gurus
This is exactly what's happening...I'm looking for a workaround

I realized what is wrong when I am copying the those numbers from the column I think they are in the text format which is is why there is a small triangle displayed beside those numbers giving option to convert to numbers.

Hi 4gurus

See if the attached works for you...

You're welcome...glad I could help...thanks for the Blessings...need 'em.

If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.