Multiple Data To Sort 1

[Topcat04]

Hi There
I have a challenge that I am sure someone can help me with.

I have 52 weeks of data by 9 different regions to sort by format.

Please see a sample of the data below. This represents 4 weeks of data by 2 regions - as noted above there is 52 weeks by 9 regions.

xxx Hardware xxx Hardware xxx Hardware xxx Hardware xxx Hardware xxx Hardware
Console Weekly (change) Console Weekly (change) Console Weekly (change) Console Weekly (change) Console Weekly (change) Console
DS 62,747 DS 40,262 DS 27,624 DS 25,783 DS 27,847 DS
Wii 41,961 Wii 25,094 Wii 19,875 Wii 18,753 Wii 18,664 Wii
PS3 26,239 X360 13,770 X360 11,857 PS3 10,652 PS3 10,400 X360
X360 25,105 PS3 12,851 PS3 10,272 X360 8,688 X360 8,916 PS3
PSP 13,931 PSP 9,363 PSP 4,524 PSP 4,100 PSP 3,932 PSP
PS2 2,985 PS2 2,638 PS2 2,361 PS2 2,108 PS2 1,927 PS2
Total 172,968 Total 103,978 Total 76,513 Total 70,084 Total 71,686 Total
yyy Hardware yyy Hardware yyy Hardware yyy Hardware yyy Hardware yyy Hardware
Console Weekly (change) Console Weekly (change) Console Weekly (change) Console Weekly (change) Console Weekly (change) Console
DS 56,686 DS 32,537 DS 22,799 DS 22,870 DS 25,845 DS
Wii 49,257 Wii 29,980 Wii 20,728 Wii 18,369 Wii 18,467 Wii
PS3 28,211 X360 12,360 X360 10,490 PS3 8,176 X360 8,434 X360
X360 26,206 PS3 11,208 PS3 8,020 X360 8,037 PS3 7,611 PS3
PSP 8,013 PSP 5,373 PS2 2,721 PSP 2,553 PSP 2,534 PSP
PS2 3,616 PS2 3,253 PSP 2,713 PS2 2,422 PS2 2,410 PS2

My goal and requirement is to have each data sorted A-Z (DS, PS2, PS3, PSP, Wii, X360) for each week for each region.

What is the solution, other than to painstakingly select format and data by week by region and sort A-Z?

Thank you for your help!

 

[SkipVought]

hi,

What version of Excel?

sort by format.

What does that mean?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Topcat04]

Hi Skip

Excel 2010

Sort by Format (DS, PS2, PS3, PSP, Wii, X360) sorry wrong terminology for excel - slip of the tongue.

So currently I am selecting the data that contains the title and the data in the next column and sorting a-z on the title column to get the data in order, however with 468 sorts this is going to take some time and I wonder what the easiest way to get all the data sorted the same either on this sheet or an additional sheet?

Thanks for your help,

 

[SkipVought]

Record a macro.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Topcat04]

Hi Skip

How do I record a macro that will move along the different columns? My only experience with Macros in the past has been with manipulating the same data in the same columns each time?

sorry!

 

[SkipVought]

Is your objective to do '468 sorts' one after the other without interruption, or should some user action or some predictable action occur after each sort?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

Topcat04,

Is that what your data looks like in the data source? Why is it listed in such a manner? Is it from a website? If you need to do this for business purposes, then surely you can get to a better original source than this.

 

[Topcat04]

Hi KJV

This is the raw data that I have and yes it has been compiled from pulling data from a website. I am unable to obtain any better source data than this.

Skip, yes the objective is to do all the sorts one after another without interruption as after the data is sorted the data will be used for graphing.

Once this is done, going forward the data will be sorted each week, so this is just a one off to get the data in the correct format to start with.
Thanks for all the help - much appreciated.

 

[SkipVought]

Oh my!!!

I did not look carefully at this data structure.

DREADFUL!

You might consider completely restructuring your data into one table, from which a weekly report can be quite simple to generate.

Is this a one time deal, or something you get periodically?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Topcat04]

Hi Skip

This data in this format is a one time deal. I need to compile it so that all the DS data is on the same row by region - all the PS3 data is on the same row by region.

Once I have done that, I will be able to do the new incoming data relatively easily as you have mentioned in the past with a Macro. its just this initial sorting that I need to find a way to do with out manually doing the 400+ sorts.

Hope there is a solution out there....

 

[SkipVought]

I normalized the data from sheet1 to sheet2 using this macro

Sub BuildTable()
    Dim lRowIN As Long, lRowOUT As Long, iCol As Integer, iOff As Integer
    Dim sHardware As String
    
    lRowIN = 1
    lRowOUT = 2
    Do While Sheet1.Cells(lRowIN, 1) <> ""
        sHardware = Sheet1.Cells(lRowIN, 1)
        For iCol = 0 To 51
            For iOff = 2 To 7
                With Sheet2.Cells(lRowOUT, 1)
                    .Value = "Week" & iCol + 1
                    .Offset(0, 1) = sHardware
                    .Offset(0, 2) = Sheet1.Cells(lRowIN + iOff, iCol * 2 + 1)
                    .Offset(0, 3) = Sheet1.Cells(lRowIN + iOff, iCol * 2 + 2)
                End With
                lRowOUT = lRowOUT + 1
            Next
        Next
        lRowIN = lRowIN + 9
    Loop
End Sub

The Sheet2 data looks like this...
[tt]
Week Region Console Change
Week1 xxx Hardware DS 62747
Week1 xxx Hardware Wii 41961
Week1 xxx Hardware PS3 26239
Week1 xxx Hardware X360 25105
Week1 xxx Hardware PSP 13931
Week1 xxx Hardware PS2 2985
...
[/tt]
Then I ran a transform query

Transform SUM(Change)

SELECT Region, Console

FROM `Sheet2$`

Group By Region, Console

Pivot Week

and got this result for the data you posted...
[tt]
Region Console Week1 Week2 Week3 Week4 Week5
xxx Hardware DS 62747 40262 27624 25783 27847
xxx Hardware PS2 2985 2638 2361 2108 1927
xxx Hardware PS3 26239 12851 10272 10652 10400
xxx Hardware PSP 13931 9363 4524 4100 3932
xxx Hardware Wii 41961 25094 19875 18753 18664
xxx Hardware X360 25105 13770 11857 8688 8916
yyy Hardware DS 56686 32537 22799 22870 25845
yyy Hardware PS2 3616 3253 2721 2422 2410
yyy Hardware PS3 28211 11208 8020 8176 7611
yyy Hardware PSP 8013 5373 2713 2553 2534
yyy Hardware Wii 49257 29980 20728 18369 18467
yyy Hardware X360 26206 12360 10490 8037 8434


[/tt]
Is this what you're looking for?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Topcat04]

WOW Skip....holy cats!

This looks right - Im going to try and implement this in my sheet now and get back to you!

Thanks so much for the help - way beyond my scope!

 

[Topcat04]

Sorry Skip

I am unable to get the code to work - Im good with Macro - is there anyway I can send the sheet to you so that I can make sure that the data represented properly in the above posting?
Thanks for the amazing help,

 

[Topcat04]

OOPS!

QUOTE 'Im NOT good with Macros'

 

[SkipVought]

I am unable to get the code to work

This assumes a sheet named Sheet1 that contains your data starting in A1 and repeating region data every 9 rows, and repeating week data every other column.

It also assumes a Sheet2 into which the results are written. Headings to be entered in row 1 as indicated in the posted example above.

Have all the assumptions been met?

Where did you paste the macro code?



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Topcat04]

Skip

I pasted the data in VB editor under developer toolbar.

Data is in Sheet1 and there is a Sheet2 waiting.

Scandinavia Hardware
Console Weekly (change)
X360 12,381
Wii 10,604
DS 10,314
PS3 6,849
PS2 3,841
PSP 3,177
Total 47,166
Other Europe Hardware
Console Weekly (change)
DS 37,181
Wii 30,421
PS3 15,262
PSP 14,045
PS2 12,979
X360 12,640
Total 122,528

This is how the data is represented
Column A and B shown here duplicated through to column DB
Rows 1 through to 18 shown here (replicated through to row 81) just different countries represented as a header.

Hope that helps and really apologise for being a newbie with macros - thank you for your time and effort....

 

[SkipVought]

What happens when you RUN the macro?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Topcat04]

Object Required.

 

[SkipVought]

hit the DEBUG button and tell me what statement is selected.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Topcat04]

Skip

Wheres the debug button - there is just a dialog box that says Object Required with an OK button and help button.

heres the Help


Object required (Error 424)

References to properties and methods often require an explicit object qualifier. This error has the following causes and solutions:


You referred to an object property or method, but didn't provide a valid object qualifier.
Specify an object qualifier if you didn't provide one. For example, although you can omit an object qualifier when referencing a form property from within the form's own module, you must explicitly specify the qualifier when referencing the property from a standard module.

You supplied an object qualifier, but it isn't recognized as an object.
Check the spelling of the object qualifier and make sure the object is visible in the part of the program in which you are referencing it. In the case of Collection objects, check any occurrences of the Add method to be sure the syntax and spelling of all the elements are correct.

You supplied a valid object qualifier, but some other portion of the call contained an error.
An incorrect path as an argument to a host application's File Open command could cause the error. Check arguments.

You didn't use the Set statement in assigning an object reference.
If you assign the return value of a CreateObject call to a Variant variable, an error doesn't necessarily occur if the Set statement is omitted. In the following code example, an implicit instance of Microsoft Excel is created, and its default property (the string "Microsoft Excel") is returned and assigned to the Variant

RetVal

. A subsequent attempt to use
RetVal

as an object reference causes this error:

Dim RetVal ' Implicitly a Variant.
' Default property is assigned to Type 8 Variant RetVal.
RetVal = CreateObject("Excel.Application")
RetVal.Visible = True ' Error occurs here.


Use the Set statement when assigning an object reference.

In rare cases, this error occurs when you have a valid object but are attempting to perform an invalid action on the object. For example, you may receive this error if you try to assign a value to a read-only property.
Check the object's documentation and make sure the action you are trying to perform is valid.


Sorry!