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

 

[hiker95]

kin,

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (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

 

[MickG]

Try this:-
Results start "D1".
NB:- If does not achieve what you want, please show brief example of sheet layout and example of expected results.

[COLOR="Navy"]Sub[/COLOR] MG07May12
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & rows.Count).End(xlUp))
        ReDim ray(1 To Rng.Count, 1 To Columns.Count)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.value) [COLOR="Navy"]Then[/COLOR]
        n = n + 1
        ray(n, 1) = Dn.Offset(, -1): ray(n, 2) = Dn.Offset(, 1)
        .Add Dn.value, Array(ray, 2, n)
    [COLOR="Navy"]Else[/COLOR]
        Q = .Item(Dn.value)
            Q(1) = Q(1) + 1
            Q(0)(Q(2), Q(1)) = Dn.Offset(, -1)
            Q(1) = Q(1) + 1
            Q(0)(Q(2), Q(1)) = Dn.Offset(, 1)
        .Item(Dn.value) = Q
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
  [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    c = c + 1
    Cells(c, 4) = K
        [COLOR="Navy"]For[/COLOR] Ac = 1 To Q(1)
            Cells(c, Ac + 4) = .Item(K)(0)(c, Ac)
        [COLOR="Navy"]Next[/COLOR] Ac
  [COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[kin]

thanks for the help I use 2007 and I ll try to upload a sample of data

 

[kin]

i installed jeanie now what am I doing?

 

[hiker95]

kin,

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


Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net

 

[kin]

I have excel 2007 but i dont see jeanie in add ins.

 

[Dazzawm]

I have excel 2007 but i dont see jeanie in add ins.

Look here:

http://www.excel-jeanie-html.de/html/addin_07_en.php

 

[kin]

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.220
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
Sheet1

 

[kin]

well I made it. I copied the data. Now please read the first post because I want to transpose these data in a normal order that is the customer names in columns the Unique product names in rows without counting double and triple euro values. The codes in parentheses are for each product and as for this I will ask you some other time what i want to do to them