transpose function

[kin]

Hi I have a series of data baut need to change how it appears in order to make my life easy for analysis.

In column A I have the name of the company, In B the products and in C the sales value
However A gives the name of one company while I d like A column to show the product descriptions. E.g. rows A6:A32 shows just tthe name of one company and rows B2:B32 the products for that company and then C2:c32 the sales for each product for that company.
As you understand the same happens for many other companies underneath and you know that this brings duplicates of products E.g product B appears 10 times because 10 customers purchased it.
I know want to change that so products appear in rows of A only one time each and companies in just one row like B5,C5 etc so there will be one sales value for one product for every company thta had this sale
Please help

 

[kin]

i forgot to mention that I do not care about the Total rows I will erase them

 

[hiker95]

kin,

Thanks for the screenshot.

Further screenshots that I need, will not work correctly with Excel Jeanie (because of the width of your data in column B).


Can you create a sample workbook with the data displayed on Sheet1, and manually create on Sheet2, what the results should be.

Then you can upload your sample workbook to www.box.net and provide us with a link to your workbook.

 

[kin]

Excel Workbook
	A	B	C
1	PRODUCT NAME	HARRY 2011	JAMES 2011
2	MR-566 4L 4 TEM. - (5-40-032)	250,91	26,06
3	C500 4L ...() 4 - (5-40-017)	210,00	0,00
4	F-320 4L . 4TEM. - (5-40-065)	327,00	0,00
5	-115 4L XP. ULTRA FRESH 4 TEM. - (5-40-528)	373,00	0,00
Sheet2

 

[kin]

this is the order I d like to see it for 2011 I don t care for 2010. I used a small sample so that it can be seen

 

[kin]

I don tknow if it is wise to break the code numbers in parentheses and send them to another column.

 

[hiker95]

kin,


Based on this raw data:

Excel Workbook
	A	B	C	D
1	CUSTOMER NAME	PRODUCT NAME	2010	2011
2	HARRY	VARIO EXTRA 10630 1 . - (5-31-088)	2.178
3		VARIO EXTRA KOYZIN 10 X 630 . 1 . - (5-31-088)		1.698
4		WC C 500 . . 12 - (5-30-067)	1.22
5		WC ECO 500. .12 65 - (5-30-077)	291	648
6		MR566 4L .. 4 - (5-40-032)	578
7		F320 4L FRESH .. 4 - (5-40-065)	569
8		COMBI. 115 4L ULTRA FRESH 4 - (5-40-528)	502
9		. 5055. 10KG 1 38TEM - (4-66-023)	459
10		C500 4L ...() 4 - (5-40-017)	450
11		ECONOMY LATEX MEDIUM 10X100 TEM 1 KIB. - (4-52-179)	269	129
12		VI- AS60-2 2000W 60L - (4-79-016)	383
13		-115 4L XP. ULTRA FRESH 4 TEM. - (5-40-528)		373
14		F-320 4L . 4TEM. - (5-40-065)		327
15		. 5055. 10 Kg. 1 38 . - (4-66-023)		311
16		MR-566 4L 4 TEM. - (5-40-032)		251
17		/ WC TORK 2(345080) - (4-34-020)	248
18		WC ECO KOPE 48 YK. 12500. - (5-30-067)		242
19		C-500 4L . ET 4 EM. - (5-40-017)		210
20		VI-. AS30-1000W 30L - (4-79-018)	184
21		/ 300.. . 10 - (5-60-316)	156
22		902 500ml / 18 - (5-40-719)	89
23		EXTRA SPECIAL HOTEL EXCL/VE 4090 - (5-30-525)	77
24		ANT/KO OY. 300P.OIK.(BACTERIAL) .AO 10TEM. - (5-60-316)		57
25		ECONOMY LATEX LARGE 10X100 TEM 1 KIB - (4-52-183)	33	24
26		S- 3 - (4-34-060)	56
27		ECONOMY . 18 - (4-52-320)	56
28		COMBITEX 3214 13 - (4-59-006)	52
29		LA-210 . 4L4 - (5-40-136)	41
30		. 4040 40 - (4-59-231)	33
31		VO 300 ECONOMY 4L 4 - (5-40-270)	29
32		11L 12 - (4-61-061)	25
33		YAOKAAP. INOX BAYERSAN 35EK. 1 10 TEM. - (4-55-002)	25
34		COMBITEX 3214 .13 - (4-59-006)		25
35		COMBITEX 3214 4 NEO - (4-59-020)	24
36		- SC - 3 - (4-78-136)	23
37		.. 1,30 10 - (5-58-042)	19
38		11L 12 - (4-61-060)	18
39		. 10152.5 -124 - (4-59-172)	17
40		.. .. 22 3 - (4-58-067)	15
41		. PROF. 1,30cm . 110 . - (5-58-042)		14
42		/ 35. 10 - (4-55-061)	13
43		KAAP. INOX BAYERSAN 25EK. 1 10 TEM. - (4-55-001)	11
44		ECONOMY . 18. 1 - (4-52-320)		11
45		(0119) 12 - (4-52-062)	10
46		35 10 - (4-55-052)	10
47		. 4060 14 3 - (4-75-061)	9
48		/ / 400 HIL 2/6 25TEM - (5-60-307)	9
49		ANT/KO OY. 400P.EYKO HIL 2/6 25 TEM. - (5-60-307)		8
50		. PROF.1,30 M . 20 - (5-58-028)	8
51		SUPERTEX 250 - DRY 1720 100 - (4-59-041)	6
52		/ 25. 10 - (4-55-060)	5
53		25. 10 - (4-55-051)	5
54		.4656 50 20 - (4-66-007)	2
55		TOTAL	8.206	4.329
56	JAMES	. FOOD PAPER EXTRA 210004 - (5-31-092)	1.327
57		FOOD PAPER EXTRA 2 1000 X 4 ( 4-39-040) - (5-31-092)		971
58		EXTRA SPECIAL HOTEL EXCL. 24200 - (5-30-530)	347
59		. 75105. 10 Kg. 1 12 . - (4-66-042)		241
60		. 75105 10KG 1 12 LDPE - (4-66-042)	103
61		DIS-100 4L . - (5-60-643)	74
62		. 5055. 10 Kg. 1 38 . - (4-66-023)	59
63		MR-566 4L 4 TEM. - (5-40-032)		26
64		/ WC TORK 2(345080) - (4-34-020)	24
65		WC ECO KOPE 48 YK. 12500. - (5-30-067)		19
66		C-500 4L . ET 4 EM. - (5-40-017)	17
67		VI-. AS30-1000W 30L - (4-79-018)	14
68		/ 300.. . 10 - (5-60-316)	13
69		902 500ml / 18 - (5-40-719)	13
70		/ 2525 2 48100 - (4-32-401)		13
71		/ . - . 200 23 . 1 KIB. - (5-33-045)		13
72		/ -.2002 23 - (5-33-045)	12
73		I 6 - (4-66-123)	8
74		NE 4850 - (4-66-068)	8
75		COMBI. 115 4L ULTRA FRESH 4 - (5-40-528)	6
76		/ 2 3333 24X100TEM - (5-32-110)	5
77		FP330 1L 12 - (5-40-062)	2
78		TOTAL	2.033	1.283
79
Sheet1

I get this manually?????

Excel Workbook
	A	B	C
1	PRODUCT NAME	HARRY 2011	JAMES 2011
2	VARIO EXTRA KOYZIN 10 X 630 . 1 . - (5-31-088)	1.698	0
3	WC ECO 500. .12 65 - (5-30-077)	648	0
4	ECONOMY LATEX MEDIUM 10X100 TEM 1 KIB. - (4-52-179)	129	0
5	-115 4L XP. ULTRA FRESH 4 TEM. - (5-40-528)	373	0
6	F-320 4L . 4TEM. - (5-40-065)	327	0
7	. 5055. 10 Kg. 1 38 . - (4-66-023)	311	0
8	MR-566 4L 4 TEM. - (5-40-032)	251	26
9	WC ECO KOPE 48 YK. 12500. - (5-30-067)	242	19
10	C-500 4L . ET 4 EM. - (5-40-017)	210	0
11	ANT/KO OY. 300P.OIK.(BACTERIAL) .AO 10TEM. - (5-60-316)	57	0
12	ECONOMY LATEX LARGE 10X100 TEM 1 KIB - (4-52-183)	24	0
13	COMBITEX 3214 .13 - (4-59-006)	25	0
14	. PROF. 1,30cm . 110 . - (5-58-042)	14	0
15	ECONOMY . 18. 1 - (4-52-320)	11	0
16	ANT/KO OY. 400P.EYKO HIL 2/6 25 TEM. - (5-60-307)	8	0
17	FOOD PAPER EXTRA 2 1000 X 4 ( 4-39-040) - (5-31-092)	0	971
18	. 75105. 10 Kg. 1 12 . - (4-66-042)	0	241
19	/ 2525 2 48100 - (4-32-401)	0	13
20	/ . - . 200 23 . 1 KIB. - (5-33-045)	0	13
21
Results_Testing

Is this correct?

 

[kin]

Yes it is correct.
is there code for this transformation?

 

[kin]

i want to be able to do crosstabulations and see patterns of relationships in sales values for my customers

thanks

 

[hiker95]

kin,

Same raw data as my last reply.

The macro will copy worksheet Sheet1 columns A and B, and the last used column in worksheet Sheet1 to worksheet Results, columns A, B, and C.


After the macro in a new worksheet Results:

Excel Workbook
	A	B	C
1	PRODUCT NAME	HARRY 2011	JAMES 2011
2	VARIO EXTRA KOYZIN 10 X 630 . 1 . - (5-31-088)	1.698	0
3	WC ECO 500. .12 65 - (5-30-077)	648	0
4	ECONOMY LATEX MEDIUM 10X100 TEM 1 KIB. - (4-52-179)	129	0
5	-115 4L XP. ULTRA FRESH 4 TEM. - (5-40-528)	373	0
6	F-320 4L . 4TEM. - (5-40-065)	327	0
7	. 5055. 10 Kg. 1 38 . - (4-66-023)	311	0
8	MR-566 4L 4 TEM. - (5-40-032)	251	26
9	WC ECO KOPE 48 YK. 12500. - (5-30-067)	242	19
10	C-500 4L . ET 4 EM. - (5-40-017)	210	0
11	ANT/KO OY. 300P.OIK.(BACTERIAL) .AO 10TEM. - (5-60-316)	57	0
12	ECONOMY LATEX LARGE 10X100 TEM 1 KIB - (4-52-183)	24	0
13	COMBITEX 3214 .13 - (4-59-006)	25	0
14	. PROF. 1,30cm . 110 . - (5-58-042)	14	0
15	ECONOMY . 18. 1 - (4-52-320)	11	0
16	ANT/KO OY. 400P.EYKO HIL 2/6 25 TEM. - (5-60-307)	8	0
17	FOOD PAPER EXTRA 2 1000 X 4 ( 4-39-040) - (5-31-092)	0	971
18	. 75105. 10 Kg. 1 12 . - (4-66-042)	0	241
19	/ 2525 2 48100 - (4-32-401)	0	13
20	/ . - . 200 23 . 1 KIB. - (5-33-045)	0	13
21	/ -.2002 23 - (5-33-045)	0	0
22	I 6 - (4-66-123)	0	0
23	NE 4850 - (4-66-068)	0	0
24	COMBI. 115 4L ULTRA FRESH 4 - (5-40-528)	0	0
25	/ 2 3333 24X100TEM - (5-32-110)	0	0
26	FP330 1L 12 - (5-40-062)	0	0
27
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, 05/31/2011
' http://www.mrexcel.com/forum/showthread.php?t=548485
Dim w1 As Worksheet, wR As Worksheet
Dim c As Range, firstaddress As String, T, tt As Long
Dim Area As Range, SR As Long, ER As Long, LC As Long, LR As Long, LR2 As Long, NC As Long, ColName As String
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
LC = w1.Cells(1, Columns.Count).End(xlToLeft).Column
w1.Columns("A:B").Copy wR.Range("A1")
w1.Columns(LC).Copy wR.Range("C1")
wR.Activate
tt = Application.CountIf(wR.Columns(2), "TOTAL")
ReDim T(1 To tt)
tt = 0
With Columns(2)
  Set c = .Find("TOTAL", LookIn:=xlValues, LookAt:=xlWhole)
  If Not c Is Nothing Then
    firstaddress = c.Address
    Do
      c.Interior.Pattern = xlNone
      Rows(c.Row + 1).Insert
      tt = tt + 1
      T(tt) = c.Row
      Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstaddress
  End If
End With
wR.Rows(2).Insert
For Each Area In Range("B3", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With Area
    SR = .Row
    ER = SR + .Rows.Count - 1
    Range("A" & SR & ":A" & ER).MergeCells = False
    Range("A" & SR).AutoFill Destination:=Range("A" & SR & ":A" & ER)
  End With
Next Area
wR.Rows(2).Delete
For tt = UBound(T) To LBound(T) Step -1
  wR.Rows(T(tt)).Delete
Next tt
On Error Resume Next
Range("C1", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
wR.Columns(2).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wR.Columns(5), Unique:=True
wR.Columns(1).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wR.Columns(6), Unique:=True
LR = wR.Cells(Rows.Count, 6).End(xlUp).Row
NC = 5
For tt = 2 To LR Step 1
  NC = NC + 1
  With Cells(1, NC)
    .Value = Cells(tt, 6) & " " & Cells(1, 3)
    .Font.Bold = True
  End With
Next tt
Range(Cells(2, 6), Cells(LR, 6)).Clear
LR = Cells(Rows.Count, 2).End(xlUp).Row
LR2 = Cells(Rows.Count, 5).End(xlUp).Row
Range("F2").Formula = "=SUMPRODUCT(--($A$2:$A$" & LR & "=LEFT(F$1,FIND("" "",F$1)-1)),--($B$2:$B$" & LR & "=$E2),--($C$2:$C$" & LR & "))"
Range("F2").AutoFill Destination:=Range("F2:F" & LR2)
LC = Cells(1, Columns.Count).End(xlToLeft).Column
If LC > 6 Then
  ColName = Replace(Cells(1, LC).Address(0, 0), 1, "")
  Range("F2:F" & LR2).AutoFill Destination:=Range("F2:" & ColName & LR2)
  With Range("F2:" & ColName & LR2)
    .Value = .Value
    .HorizontalAlignment = xlCenter
  End With
Else
  With Range("F2:F" & LR2)
    .Value = .Value
    .HorizontalAlignment = xlCenter
  End With
End If
Columns("A:D").Delete
wR.UsedRange.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

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

Then run the ReorgData macro.

 

[kin]

it say subscript out of range and stops in ReDim T(1 To tt)
I also don t see the name of the macro in the dialog box.
what is wrong?
thanks