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

 

[tljenkin]

Actually ignore all above, here is screen shot 1 & 2

Screen shot 1

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
2	Seq	Unique	Type	Regime	Cohort	Heritage	Prod Area	Channel	Pro/Re	Direct/CMC	Week1	Week2
3	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	10	20
4	2	2	Budget	ICOBS	HBOS - Loans - Retail	HBOS	Loans	Retail	Proactive	Direct	30	40
Sheet1

Excel 2010

 

[tljenkin]

Screenshot 2

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
7	Week	Seq	Unique	Type	Regime	Cohort	Heritage	Prod Area	Channel	Pro/Re	Direct/CMC	Total Complaints
8	Week1	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	10
9	Week2	2	2	Budget	ICOBS	HBOS - Loans - Retail	HBOS	Loans	Retail	Proactive	Direct	20
Sheet1

Excel 2010

 

[hiker95]

tljenkin,


Are your titles in worksheet Sheet1, in row 2 like this?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	Seq	Unique	Type	Regime	Cohort	Heritage	Prod Area	Channel	Pro/Re	Direct/CMC	Week1	Week2
3	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	10	20
4	2	2	Budget	ICOBS	HBOS - Loans - Retail	HBOS	Loans	Retail	Proactive	Direct	30	40
5
Sheet1

Or, are your titles in worksheet Sheet1, in row 1 like this?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Seq	Unique	Type	Regime	Cohort	Heritage	Prod Area	Channel	Pro/Re	Direct/CMC	Week1	Week2
2	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	10	20
3	2	2	Budget	ICOBS	HBOS - Loans - Retail	HBOS	Loans	Retail	Proactive	Direct	30	40
4
Sheet1

 

[tljenkin]

In row 2, row 1 has some flags and is hidden. Thanks

 

[hiker95]

tljenkin,


Sample raw data in worksheet Sheet1:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2	Seq	Unique	Type	Regime	Cohort	Heritage	Prod Area	Channel	Pro/Re	Direct/CMC	Week1	Week2	Week3	Week4	Week5	Week6	Week7	Week8
3	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	10	20	15	25	10	20	15	25
4	2	2	Budget	ICOBS	HBOS - Loans - Retail	HBOS	Loans	Retail	Proactive	Direct	30	40	35	45	30	40	35	45
5
Sheet1

After the macro in a new worksheet Results:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Week	Seq	Unique	Type	Regime	Cohort	Heritage	Prod Area	Channel	Pro/Re	Direct/CMC	Total Complaints
2	Week1	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	10
3	Week2	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	20
4	Week3	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	15
5	Week4	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	25
6	Week5	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	10
7	Week6	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	20
8	Week7	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	15
9	Week8	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	25
10	Week1	2	2	Budget	ICOBS	HBOS - Loans - Retail	HBOS	Loans	Retail	Proactive	Direct	30
11	Week2	2	2	Budget	ICOBS	HBOS - Loans - Retail	HBOS	Loans	Retail	Proactive	Direct	40
12	Week3	2	2	Budget	ICOBS	HBOS - Loans - Retail	HBOS	Loans	Retail	Proactive	Direct	35
13	Week4	2	2	Budget	ICOBS	HBOS - Loans - Retail	HBOS	Loans	Retail	Proactive	Direct	45
14	Week5	2	2	Budget	ICOBS	HBOS - Loans - Retail	HBOS	Loans	Retail	Proactive	Direct	30
15	Week6	2	2	Budget	ICOBS	HBOS - Loans - Retail	HBOS	Loans	Retail	Proactive	Direct	40
16	Week7	2	2	Budget	ICOBS	HBOS - Loans - Retail	HBOS	Loans	Retail	Proactive	Direct	35
17	Week8	2	2	Budget	ICOBS	HBOS - Loans - Retail	HBOS	Loans	Retail	Proactive	Direct	45
18
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 ReorgDataV2()
' hiker95, 10/01/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("A2").CurrentRegion.Resize(, LC).Value
ReDim O(1 To (LC - 11 + 1) * (LR - 1) + 1, 1 To 12)
O(1, 1) = "Week"
O(1, 2) = "Seq"
O(1, 3) = "Unique"
O(1, 4) = "Type"
O(1, 5) = "Regime"
O(1, 6) = "Cohort"
O(1, 7) = "Heritage"
O(1, 8) = "Prod Area"
O(1, 9) = "Channel"
O(1, 10) = "Pro/Re"
O(1, 11) = "Direct/CMC"
O(1, 12) = "Total Complaints"
n = 1
For r = 2 To UBound(I) Step 1
  For c = 11 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, 3)
    O(n, 5) = I(r, 4)
    O(n, 6) = I(r, 5)
    O(n, 7) = I(r, 6)
    O(n, 8) = I(r, 7)
    O(n, 9) = I(r, 8)
    O(n, 10) = I(r, 9)
    O(n, 11) = I(r, 10)
    O(n, 12) = 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), 12).Value = O
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 ReorgDataV2 macro.

 

[tljenkin]

Many Thanks, will test out a little later and confirm ok

 

[tljenkin]

Here are the results:

One small problem: it copies "total Complaints received" into cell A2 and repeats this at the start of each cycle

Can you tweak slightly?

Many Thanks

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Week	Seq	Unique	Type	Regime	Cohort	Heritage	Prod Area	Channel	Pro/Re	Direct/CMC	Total Complaints
2	Total Complaints Received	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	0
3	04/05/2011	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	0
4	11/05/2011	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	0
5	18/05/2011	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	0
6	25/05/2011	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	0
7	01/06/2011	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	0
8	08/06/2011	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	0
9	15/06/2011	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	0
10	22/06/2011	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	0
Results

Excel 2007

 

[tljenkin]

Please remember that the titles or row headers for the original data start in row 2.

Thanks

 

[hiker95]

tljenkin,


Sample raw data in worksheet Sheet1 (row 1 hidden):

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
2	Seq	Unique	Type	Regime	Cohort	Heritage	Prod Area	Channel	Pro/Re	Direct/CMC	Week1	Week2
3	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	10	20
4	2	2	Budget	ICOBS	HBOS - Loans - Retail	HBOS	Loans	Retail	Proactive	Direct	30	40
5
Sheet1

After the macro in a new worksheet Results:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Week	Seq	Unique	Type	Regime	Cohort	Heritage	Prod Area	Channel	Pro/Re	Direct/CMC	Total Complaints
2	Week1	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	10
3	Week2	1	1	Budget	ICOBS	LTSB - Loans - Retail	LTSB	Loans	Retail	Proactive	Direct	20
4	Week1	2	2	Budget	ICOBS	HBOS - Loans - Retail	HBOS	Loans	Retail	Proactive	Direct	30
5	Week2	2	2	Budget	ICOBS	HBOS - Loans - Retail	HBOS	Loans	Retail	Proactive	Direct	40
6
Results

The only way we are going to resolve the issue you are having, is for you to send me your actual workbook.

See my Private Message to you (top right hand corner of MrExcel, Welcome, tljenkin., Private Messages:

 

[hiker95]

tljenkin,

When I try to send you a Private Message I get the following:

tljenkin has chosen not to receive private messages or may not be allowed to receive private messages. Therefore you may not send your message to him/her.

If you are trying to send this message to multiple recipients, remove tljenkin from the recipient list and send the message again.



The only other way to resolve the issue is:

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.