STDEV(i),
Excellent! Works like a charm. If I would like to add a formula to the
total column, that would only pick up only the Order Qty amount, how would I
do that? (the order number is a six digit number and adds to the total) I
would use =SUMIF(A$25:Z$25,"Order Qty",A26:Z26) after the fact, but how
would I change the VBA code to always do that?
I really appreciate the time you spend teaching others. Thank you.
John
On Mon, Jun 13, 2011 at 1:30 AM, STDEV(i) <setiyowati.d...@gmail.com> wrote:
> 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
>
--
----------------------------------------------------------------------------------
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
