Rearranging data in Excel

Re: Rearranging data in Excel

Maybe it's late in the day and my brain has slowed to a crawl, but I'm really not sure I follow the logic of your sample result. I see what you're saying about reorganizing the data, but then the numbers you show for the result don't seem to have any relationship to the initial set of numbers. You might do best to attach a sample with slightly larger and more representative selection of real data.

Re: Rearranging data in Excel

Excel 2010 PivotTables
No formulas, no code needed.
With detailed notes and pictures.
http://c3017412.r12.cf0.rackcdn.com/04_22_11b.xlsx
If you get *.zip, don't unzip, just rename *.xlsx
http://www.mediafire.com/file/v4pmps7mhcccw9a/04_23_11.pdf

Re: Rearranging data in Excel

Since I had already worked out a solution by the time I saw Herbds7's post, I'll go ahead and put it up. This is one of those cases where it's just amazing to see how different the approaches to solution can be and still reach the same goal. Now you get to pick the one that fits your real life conditions best. Since you didn't post sample data, I assumed everything you said about your scenario was 100% literally true. So to make this work, all you have to do is have the sheet with your original data active, then run this code. The results will be on a new sheet, so your original data remains unchanged.

Assumes: 1) no blank lines or columns in your data; 2) header row is in row 1; 3) people labels(P1, P2, P3, etc...) are in column A.

Sub rearrange()


    Dim Data1
    Dim Data2(1 To 770, 1 To 16)
    Dim r As Long
    Dim c As Long
    Dim q As Long
    Dim v
    Dim p As Long
    Dim InSht As Worksheet
    Dim OutSht As Worksheet
    
    Set InSht = ActiveSheet
    Set OutSht = Sheets.Add
    
'assemble values for row headers
    r = 2
    For p = 1 To 70
        For Each v In Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K")
            OutSht.Cells(r, 1) = "P" & p & v
            r = r + 1
        Next v
    Next p
    
'column headers without leading character
    For q = 2 To 17
        OutSht.Cells(1, q) = Right(InSht.Cells(1, q), Len(InSht.Cells(1, q)) - 1)
    Next q
    
'load input data to variable array
    Data1 = InSht.Range("B2:FU71")
    
'rearrange the data bits into the output holding array
    p = 1
    q = 1
    For r = 1 To 770
        For c = 1 To 16
            Data2(r, c) = Data1(p, q)
            q = IIf(q = 176, 1, q + 1)
        Next c
        If q = 1 Then p = p + 1
    Next r
    
'plop the output array onto the output sheet
    OutSht.Range("B2:q771") = Data2


End Sub

Re: Rearranging data in Excel

Ulrikke,
As much as I appreciate your effort to be appreciative, I think I would have liked to know of your aversion to a coded solution before I went to the effort of working one out. Maybe next time you post a request for help you can include that detail.

Re: Rearranging data in Excel

Click on "Count of Rank" in the PivotTable and
change the setting to "Sum".
The same thing can be done in the PivotTable Field List
or by right clicking the PT and picking "Summarize Values by" "Sum".
The "1"s in Step 2 are OK.
Here is an automated version with VBA for the greater community:
http://c3017412.r12.cf0.rackcdn.com/04_23_11.xlsm
If you get *.zip, don't unzip, just rename *.xlsm

Re: Rearranging data in Excel

Barb,
Your effort was surely appreciated by many other community members.
I included your fine macro in my latest file:
http://c3017412.r12.cf0.rackcdn.com/04_23_11.xlsm
I count between 300 to 400 downloads a day.
Surely the browsers should be our main target.

Re: Rearranging data in Excel

Herbds7, Thank you - that was nice.

And Ulrikke, sorry if I was any snarky about it. As much as anything else, the failure was my own self-discipline - as I ignored my own real work because I found your little problem intriguing.