Puzzler

[tljenkin]

I have a record thus:


Name Tag Week 1 Week 2

Peter hsdk £5 £10

How do I transpose this automatically so that I get the following results:

Week Name Tag Amount

Week 1 Peter hsdk £5
Week 2 Peter hsdk £10

Thanks

 

[circledchicken]

Hi,

Try using a Pivot table to do this:

In Excel 2010:

• Drag Week 1 into the 'Values' area
• Drag Week 2 into the 'Values' area
• Drag Values from the 'Column Labels' area into the 'Row Labels' area
• Drag Name into the 'Row Labels' area (below the 'Values')
• Drag Tag into the 'Row Labels' area
• Click on the 'Design' tab at the top, Report Layout > Show in Tabular Form
• Click on the 'Design' tab at the top, Subtotals > Do not show subtotals
• Click on the 'Design' tab at the top, Grand Totals > Off for Rows and Columns
• Change/format the headings on the table however you want them

In earlier Excel versions it will be similar - you just need to drag the stuff into the right places and turn off subtotals etc.

 

[tljenkin]

Thanks but would your answer be the same if I had to do this with 300 weeks or columns which is exactly what I have to do...

 

[circledchicken]

Hi,

Perhaps run this macro instead:

Option Explicit
Option Base 1

Sub transpose()

Dim i As Long
Dim j As Long
Dim k As Long
Dim rData As Variant

With Sheet2
    ' add column headings
    .Cells(1, "A") = "Week"
    .Cells(1, "B") = "Name"
    .Cells(1, "C") = "Tag"
    .Cells(1, "D") = "Amount"
    
    ' add data
    rData = Sheet1.Range("[B]A1:L5[/B]") 'change this to your actual data range (including the headings)
    For i = 2 To UBound(rData, 1)
        For j = 3 To UBound(rData, 2)
            .Cells(j + k - 1, "A") = rData(1, j)
            .Cells(j + k - 1, "B") = rData(i, 1)
            .Cells(j + k - 1, "C") = rData(i, 2)
            .Cells(j + k - 1, "D") = rData(i, j)
        Next j
        k = k + UBound(rData, 2) - 2
    Next i
End With
    
End Sub

This will put the data in Sheet2 in the format you want. I'm not sure of your reasons for wanting the data in this structure though, because your repeating a lot of data so there might be better ways of organising this?

 

[tljenkin]

It works but it brings through the word amount in the week field, how do I stop this happening?

Never used ubound before so not sure what to adjust

Thanks

 

[circledchicken]

The word 'Amount' should only show up once in cell D1 as per your original post - I'm not clear how else you want it?

UBound returns the upper bound of an array. For example, in this case UBound(rData,1) would be the number of rows your data contains, and Ubound(rData,2) would be the number of columns.

 

[hiker95]

tljenkin,


Sample raw data in worksheet Sheet1:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Tag	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10
2	Peter	hsdk	$5	$10	$6	$12	$7	$14	$8	$16	$9	$18
3	tljenkin	Puzzler	$6	$12	$7	$14	$8	$16	$9	$18	$10	$20
4
Sheet1

After the macro in a new worksheet Results:[/n]

Excel Workbook
	A	B	C	D
1	Week	Name	Tag	Amont
2	Week 1	Peter	hsdk	$5
3	Week 2	Peter	hsdk	$10
4	Week 3	Peter	hsdk	$6
5	Week 4	Peter	hsdk	$12
6	Week 5	Peter	hsdk	$7
7	Week 6	Peter	hsdk	$14
8	Week 7	Peter	hsdk	$8
9	Week 8	Peter	hsdk	$16
10	Week 9	Peter	hsdk	$9
11	Week 10	Peter	hsdk	$18
12	Week 1	tljenkin	Puzzler	$6
13	Week 2	tljenkin	Puzzler	$12
14	Week 3	tljenkin	Puzzler	$7
15	Week 4	tljenkin	Puzzler	$14
16	Week 5	tljenkin	Puzzler	$8
17	Week 6	tljenkin	Puzzler	$16
18	Week 7	tljenkin	Puzzler	$9
19	Week 8	tljenkin	Puzzler	$18
20	Week 9	tljenkin	Puzzler	$10
21	Week 10	tljenkin	Puzzler	$20
Results

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Option Base 1
Sub ReorgData()
' hiker95, 09/25/2011
' http://www.mrexcel.com/forum/showthread.php?t=581001
Dim w1 As Worksheet, wR As Worksheet
Dim I(), O()
Dim LR As Long, LC As Long, r As Long, c As Long, n As Long
Set w1 = Worksheets("Sheet1")
LR = w1.Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
LC = w1.Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
I = w1.Range("A1").CurrentRegion.Resize(, LC).Value
ReDim O(1 To (LC - 2) * (LR - 1) + 1, 1 To 4)
O(1, 1) = "Week"
O(1, 2) = "Name"
O(1, 3) = "Tag"
O(1, 4) = "Amont"
n = 1
For r = 2 To UBound(I) Step 1
  For c = 3 To LC Step 1
    n = n + 1
    O(n, 1) = I(1, c)
    O(n, 2) = I(r, 1)
    O(n, 3) = I(r, 2)
    O(n, 4) = I(r, c)
  Next c
Next r
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
wR.Range("A1").Resize(UBound(O), 4).Value = O
wR.Range("D2").Resize(UBound(O)).NumberFormat = "$#,##0_);($#,##0)"
wR.UsedRange.Columns.AutoFit
wR.Activate
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ReorgData macro.

 

[hiker95]

tljenkin,


After testing the macro, you may want to remove the next line of code:

wR.Range("D2").Resize(UBound(O)).NumberFormat = "$#,##0_);($#,##0)"

 

[tljenkin]

circled chicken and hiker 95, you are missing the field "amount" from the original data or lets just say you are missing one field before the week fields.

So I guess my question is, if there were additional columns/ fields before the week fields, how can I tweak the code each time to reflect this so that it doesnt pull the field name into the week field?

Thanks

 

[hiker95]

tljenkin,

The only way we are going to get it right is if we can see your raw data.


You will generally get much more help (and faster) in this forum if you can post your small samples (sensitive data scrubbed/removed/changed) (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net, mark the workbook for sharing, and provide us with a link to your workbook.