Results 1 to 1 of 1
  1. #1
    Join Date
    Jan 2014
    Posts
    5

    Unanswered: Need help with formula

    I am using the following formula to unpivot my data

    Sub MG14Aug07
    Dim Rng As Range, Dn As Range
    Dim RngAc As Range
    Dim c
    c = 2
    Set Rng = Range(Range("A2"), Range("A" & Rows.count).End(xlUp))
    For Each Dn In Rng
    Set RngAc = Range(Range("B" & Dn.Row), Cells(Dn.Row, Columns.count).End(xlToLeft))
    With Sheets("Sheet2")
    .Range("A1").Resize(, 3) = Array("Item", "Date", "Qty")
    .Cells(c, 1).Resize(RngAc.count) = Dn
    .Cells(c, 2).Resize(RngAc.count) = Application.Transpose(Rng(1).Offset(-1, 1).Resize(, RngAc.count).Value)
    .Cells(c, 3).Resize(RngAc.count) = Application.Transpose(RngAc.Value)
    c = c + RngAc.count
    End With
    Next Dn
    End Sub

    It works great however sometimes my column Price is empty and I do not want to unpivot this record, since I don't want to have rows with Price empty in my output listing. Below is my input and output example. Basically I want to omint the records with F1 like 01753 k and 01709 p since there is not price. Input file is saved as attachment. Thanks
    F1 Code Price
    01753 r 28.8
    01709 us 8
    01753 k
    01709 p
    01709 ud 4.8
    01709 bu 4.4
    01709 r 4.8
    01709 b 4.8
    01709 p 4.8
    01753 cd 123.24
    01753 k6 123.24
    01753 k5 132.72
    01753 k4 143.78
    07530 k3 164.32
    01753 c 104.28
    01753 k2 160.21
    01753 k1 164.32
    01753 t1 166.37
    01753 t2 184.86
    01753 t3 205.4
    Attached Thumbnails Attached Thumbnails inputimage.png  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •