Dear Mr. Smith, Please try and check this VBA Code, if it helps...
Sub AbnormalizeYourTabel()
' STDEV(i) / milis belajar-excel / 08 apr 2011
' retouched for another table-structure
' jakarta, Jun 12, 2011
'-----------------------------------------------
Dim Tbl As Range, NewTbl As Range
Dim n As Long, r As Long, i As Long, tR As Long
Dim c As Integer, u As Integer, TotQty As Double, ArtQty()
Dim StrItm As String, Itm As String
Set Tbl = Sheets("Data Transpose").Cells(1).CurrentRegion
tR = Tbl.Rows.Count
Set NewTbl = Tbl(tR + 6, 1)
' searching UniqItems
StrItm = "|"
Application.Calculation = -4135 '=manual
Application.ScreenUpdating = 0
For i = 2 To tR
Itm = Tbl(i, 1) & "|"
If InStr(1, StrItm, Tbl(i, 1), 1) = 0 Then
r = r + 1
StrItm = StrItm & Itm
Tbl(i, 1).Resize(1, 2).Copy
NewTbl(r, 1).PasteSpecial 12
End If
Next i
' Repost all datas in New Table-Structure
Application.CutCopyMode = False
Set NewTbl = NewTbl.CurrentRegion
ReDim ArtQty(1 To NewTbl.Rows.Count)
For n = 1 To NewTbl.Rows.Count
c = 0: TotQty = 0
For i = 2 To tR
If NewTbl(n, 1) = Tbl(i, 1) Then
c = c + 3
*TotQty = TotQty + Tbl(i, 3*)
Tbl(i, 3).Resize(1, 3).Copy
NewTbl(n, c).PasteSpecial 12
End If
Next i
*ArtQty(n) = TotQty*
Next n
Tbl.Resize(1, Tbl.Columns.Count - 2).Copy NewTbl(0, 1)
u = (NewTbl.CurrentRegion.Columns.Count - 1)
' Headings..
Tbl(1, 3).Resize(1, 3).Copy
For c = 3 To u Step 3
NewTbl(0, c).PasteSpecial xlAll
Next c
* ' new column (Total Qty)*
With NewTbl(0, c)
.Value = "TOTAL QTY"
.Font.Bold = True
.BorderAround Weight:=xlThin
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
End With
For n = 1 To NewTbl.Rows.Count
NewTbl(n, c) = ArtQty(n)
Next n
Application.CutCopyMode = False
Application.Calculation = -4105
Application.ScreenUpdating = 1
End Sub
On Mon, Jun 13, 2011 at 2:07 AM, John A. Smith <johnasmit...@gmail.com>wrote:
> Please, attached is an example of data which I need to transpose and a
> pivot table doesn't do it...
>
> Thank you for your help.
>
> John
>
>
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
ctv_Abnormalize Your Normal Tabel.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12
