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!

 

[kjv1611]

That's weird that you're getting an object error on Skip's code, as I didn't notice anything in there that should cause such an issue, regardless of your version of Excel.

I'll take a whack and editing the code to make it a little easier to run.... maybe?

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

My guess is that it's got to do with having just "Sub" and not "Private Sub" in this instance, since you just need to run it from the VB Editor. I did also change the object handling so you can change the Worksheet names if necessary... I have them set by NAME, not ObjectName. So, whatever you have named as "Sheet1" - you can change the "Sheet1" in the code to whatever your worksheet name is, and "Sheet2" to whatever your Sheet2 name would be... but make sure you leave the quotes in the code.

Post back to let us know if that fixes it.

 

[Topcat04]

Skip

Looks like I got the first part running. My sheets although called sheet1 was actually sheet7. Sorry - my learning curve is getting better though.

Once I complete the data - I will try the next step being the Transform

Getting there - sorry for my inexperience...good to learn though!

 

[Topcat04]

OK I got the data all in a list - now I have to apologise and say that Im lost again on the Transform

Where do I put this code?

Thanks so much for the help....its very much appreciated!!

 

[SkipVought]

Insert a new sheet.

Data > Import External Data > Other Sources > MS Query > Excel Files* ... drill down to your workbook. Open the SQL window and paste the SQL data.

Skip,

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

 

[Topcat04]

Sorry Skip
Not having much luck - I dont know why I get so many challenges.

When I follow that and link to the workbook, it comes up with the following 'This data source contains no visible tables.'

Clicking on OK takes me to a Query Wizard with blank boxes.

having completed the first section....id love to finish this...but feel that Im just not having something go right for me...have I got something setup wrong or missing?

 

[SkipVought]

In the Add Tables window that is empty, click OPTIONS and check all boxes.

Skip,

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

 

[Topcat04]

Skip

OMG! Its done and all works!

Thank you so much for your help and persistence! I truly appreciate it and have learnt a little about Code within Excel...now I can disect and understand in more detail over time.

Thanks again! Great to have such helpful people!