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,


Sample raw data:

Excel Workbook
	A	B	C
1	CUSTOMER NAME	PRODUCT NAME	2011
2	???S??F???S ???????S ??	VARIO EXTRA ???????S 10?630 G? 1 S?S?. - (5-31-088)
3		VARIO EXTRA KOYZIN?S 10 X 630 G?. 1 S?S?. - (5-31-088)	1,698
4		WC ? C ? 500 G?. G??F?? S?S?. 12 ????? - (5-30-067)
5		WC ECO 500G?. G??F?? S?S?.12 ???? F65 - (5-30-077)	648
6		COMB.MR566 4L ??T??.????. 4 ??? - (5-40-032)
7		COMBIMAT F320 4L FRESH ???S.???.??? 4? - (5-40-065)
8		COMBI. ?115 4L ??O????? ULTRA FRESH 4? - (5-40-528)
9		S??????? ???????. ????? 50?55??. 10KG 1???? 38TEM - (4-66-023)
10		COMBIMAT C500 4L G??.??.???.(????S?) 4? - (5-40-017)
11		G????? ECONOMY LATEX ????? MEDIUM 10X100 TEM 1 KIB. - (4-52-179)	129
12		VI-????????? S????? AS60-2 2000W 60L - (4-79-016)
13		?-115 4L X?OP. ULTRA FRESH 4 TEM. - (5-40-528)	373
14		F-320 4L ???S. ????O? ??? 4TEM. - (5-40-065)	327
15		S?????? ???????. ????? 50?55??. 10 Kg. 1 ???? 38 ???. - (4-66-023)	311
16		MR-566 4L ?????O? 4 TEM. - (5-40-032)	251
17		???/?? ????? WC TORK ????? 2???(345080) - (4-34-020)
18		WC ECO GKOFPE F48 SYSK. 12?500??. - (5-30-067)	242
19		C-500 4L G??. ???S?S ???ET??? 4 ?EM. - (5-40-017)	210
20		VI-?????.S????? AS30-1000W 30L - (4-79-018)
21		???/?? SF??G 300G?.???.???? ????? ??. ??S? 10? - (5-60-316)
22		COMBIMAT ?902 500ml ?F???/?? ????S 18?? - (5-40-719)
23		????? EXTRA SPECIAL HOTEL EXCL/VE 40?90 - (5-30-525)
24		ANT/KO SFOYG. 300GP.OIK.????(????BACTERIAL) ????.?ASO 10TEM. - (5-60-316)	57
25		G????? ECONOMY LATEX ????? LARGE 10X100 TEM 1 KIB - (4-52-183)	24
26		S??????T??? S-??? ????? 3??? - (4-34-060)
27		S????? ECONOMY ???.??S? 18??? - (4-52-320)
28		COMBITEX ????? ??????? 32?14? 1?3???? - (4-59-006)
29		LA-210 ?F???????? ????O? G?? ??T.???S? 4L?4??? - (5-40-136)
30		?????F????? G??.???S?S 40?40 ???? 40??? - (4-59-231)
31		COMB.VO 300 ECONOMY 4L ?G?? ????O? 4??? - (5-40-270)
32		?????S ?? S??????? 11L ???????S 12??? - (4-61-061)
33		YA?OKATAP. INOX BAYERSAN 35EK. 1 ? 10 TEM. - (4-55-002)
34		COMBITEX ???? ??????? 32?14? S?S.1?3 ???? - (4-59-006)	25
35		COMBITEX ???? ??????? 32?14? S?S 4???? NEO - (4-59-020)
36		S?????? ????????? ????? - ???? SC - 3? - (4-78-136)
37		??????? ?????.????.??S? 1,30? ???? 10??? - (5-58-042)
38		?????S ?? S??????? 11L ???? 12??? - (4-61-060)
39		SF??G.???? ?? F????? 10?15?2.5 -1?24??? - (4-59-172)
40		??????? ?????.???S. ??.SF?. 2?2 3? - (4-58-067)
41		??????? ?????. PROF. 1,30cm ?? ????. ??S? ???? 1?10 ???. - (5-58-042)	14
42		G?????? ???/?? ????? 35??. 10??? - (4-55-061)
43		????KATAP. INOX BAYERSAN 25EK. 1 ? 10 TEM. - (4-55-001)
44		S????? ECONOMY ???. ??S? 18???. 1 ??? - (4-52-320)	11
45		?????? ??????????? (0119) 12??? - (4-52-062)
46		??S? ??G?????? ???S?OS 35?? 10??? - (4-55-052)
47		?????. ?????S??? ????? 40?60?? 14?? 3??? - (4-75-061)
48		???/?? SF??G/??S 400G? ????? HIL 2/6 S?S 25TEM - (5-60-307)
49		ANT/KO SFOYG. 400GP.?EYKO HIL 2/6 25 TEM. - (5-60-307)	8
50		??????? ??????. PROF.1,30 M ???.??S? 20??? - (5-58-028)
51		SUPERTEX 250 - DRY 17?20 ???? 100 ??? - (4-59-041)
52		G?????? ???/?? ????? 25??. 10??? - (4-55-060)
53		??S? ??G?????? ???S?OS 25??. 10??? - (4-55-051)
54		S?????? G??F.46?56 S?S 50???? ?20??? ????? - (4-66-007)
55		TOTAL	4,329
Sheet1

After the macro in a new worksheet Results:

Excel Workbook
	A	B	C
1	PRODUCT NAME	???S??F???S ???????S ?? 2011	?????????? . ??????? ??? 2011
2	VARIO EXTRA KOYZIN?S 10 X 630 G?. 1 S?S?. - (5-31-088)	1698.32	0
3	WC ECO 500G?. G??F?? S?S?.12 ???? F65 - (5-30-077)	648.44	0
4	G????? ECONOMY LATEX ????? MEDIUM 10X100 TEM 1 KIB. - (4-52-179)	128.87	0
5	?-115 4L X?OP. ULTRA FRESH 4 TEM. - (5-40-528)	372.96	0
6	F-320 4L ???S. ????O? ??? 4TEM. - (5-40-065)	327.06	0
7	S?????? ???????. ????? 50?55??. 10 Kg. 1 ???? 38 ???. - (4-66-023)	311.47	0
8	MR-566 4L ?????O? 4 TEM. - (5-40-032)	250.91	0
9	WC ECO GKOFPE F48 SYSK. 12?500??. - (5-30-067)	242.2	0
10	C-500 4L G??. ???S?S ???ET??? 4 ?EM. - (5-40-017)	209.85	0
11	ANT/KO SFOYG. 300GP.OIK.????(????BACTERIAL) ????.?ASO 10TEM. - (5-60-316)	57.1	0
12	G????? ECONOMY LATEX ????? LARGE 10X100 TEM 1 KIB - (4-52-183)	24.16	0
13	COMBITEX ???? ??????? 32?14? S?S.1?3 ???? - (4-59-006)	24.61	0
14	??????? ?????. PROF. 1,30cm ?? ????. ??S? ???? 1?10 ???. - (5-58-042)	13.94	0
15	S????? ECONOMY ???. ??S? 18???. 1 ??? - (4-52-320)	10.77	0
16	ANT/KO SFOYG. 400GP.?EYKO HIL 2/6 25 TEM. - (5-60-307)	8.44	0
17	???? ???????S FOOD PAPER EXTRA 2 ? 1000 X 4 (G?? T??? 4-39-040) - (5-31-092)	0	971.1
18	S?????? ???????. ???? 75?105??. 10 Kg. 1 ???? 12 ???. - (4-66-042)	0	240.77
19	FP-330 4L ???S. ????O? ?EYKO 4 TEM. - (5-40-063)	0	26.06
20	U-520 4L G??. ???S?S ???????? 4 TEM. - (5-40-002)	0	19.2
21	???/?? 25?25 2F ????? 48?100 - (4-32-401)	0	13
22	????/?? ????? ???S. - ?????. 200F 23 ????. 1 KIB. - (5-33-045)	0	12.75
23	XEIPO???S??? 1F EXTRA PLUS 23 - 7000 - 15?467 ???. - (5-31-005)	0	0
24	SAKOY?A ???????. MAYPH 105X120EK. 10 Kg. 1 ???? 8-9 ???. - (4-66-020)	0	0
25	WC EXTRA 200 GP. GKOFPE SYSK. 30 PO?A - (5-30-013)	0	0
26	S?????? ???????. ??????? 75?105??. 5kg. 1 ???? 12-13 ???. - (4-66-142)	0	0
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, 06/01/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("A1", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
On Error Resume Next
Range("B1", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
On Error Resume Next
Range("C1", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
LR = Cells(Rows.Count, 1).End(xlUp).Row
For tt = LR To 2 Step -1
  If Cells(tt, 3).Value = 0 Or Cells(tt, 3) = "" Then Rows(tt).Delete
Next tt
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,LEN(F$1)-5)),--($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("F1:" & ColName & LR2)
    .Value = .Value
    .HorizontalAlignment = xlCenter
  End With
Else
  With Range("F1:F" & LR2)
    .Value = .Value
    .HorizontalAlignment = xlCenter
  End With
End If
Columns("A:D").Delete
wR.UsedRange.Columns.AutoFit
wR.Rows(1).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 ReorgDataV2 macro.

 

[hiker95]

kin,

The above screenshots do not display the data correctly because of the language difference.

Just try the macro.

 

[kin]

is it true that excel when is about to average it takes also the zeros and counts them as values in the division?

 

[hiker95]

kin,

is it true that excel when is about to average it takes also the zeros and counts them as values in the division?

For something other than your original request, you need to create a New Post with this question.


Did the macro work correctly?

 

[kin]

hiker95 thanks for the macro it is working fine