Pivot Table in a row of aall data of a document no.

User avatar
PRADEEPB270
3StarLounger
Posts: 354
Joined: 27 Oct 2013, 15:11
Location: Gurgaon INDIA

Pivot Table in a row of aall data of a document no.

Post by PRADEEPB270 »

Please refer my attachment.

Actually,my data is in 4 columns but want to display as I have highlighed in yellow colour area.all data should be appear in a row against each doc.no.of column B.

Can it be possible through VBA codes ?
Regards

Pradeep Kumar Gupta
INDIA

User avatar
PRADEEPB270
3StarLounger
Posts: 354
Joined: 27 Oct 2013, 15:11
Location: Gurgaon INDIA

Re: Pivot Table in a row of aall data of a document no.

Post by PRADEEPB270 »

Is there is no solution of my question ?
Regards

Pradeep Kumar Gupta
INDIA

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Pivot Table in a row of aall data of a document no.

Post by StuartR »

It seems a bit impatient to post a second time 12 hours after the original request.

Have you tried to write this VBA yourself? Please show us how you are trying to solve your problem and someone might offer suggestions on how you can improve what you are doing.
StuartR


User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Pivot Table in a row of aall data of a document no.

Post by HansV »

Your example is not entirely clear to me. Perhaps this?

Code: Select all

Sub Test()
    Const col1 = 2 ' Doc No
    Const col2 = 3 ' Plant
    Const col3 = 5 ' BOE
    Const col4 = 6 ' Amount
    Const hrw = 4  ' Header row
    Dim lrw As Long
    Dim r As Long
    Dim r0 As Long
    Dim lcl As Long
    Dim c As Long
    Application.ScreenUpdating = False
    lrw = Cells(Rows.Count, col1).End(xlUp).Row
    lcl = Cells(hrw, Columns.Count).End(xlToLeft).Column
    r0 = hrw + 1
    c = lcl
    For r = hrw + 2 To lrw
        If Cells(r, col1).Value = Cells(r - 1, col1).Value Then
            Cells(hrw, c + 1).Value = "PLANT"
            Cells(hrw, c + 2).Value = "BOE"
            Cells(hrw, c + 3).Value = "AMOUNT"
            Cells(r0, c + 1).Value = Cells(r, col2).Value
            Cells(r0, c + 2).Value = Cells(r, col3).Value
            Cells(r0, c + 3).Value = Cells(r, col4).Value
            c = c + 3
        Else
            r0 = r
            c = lcl
        End If
    Next r
    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

User avatar
PRADEEPB270
3StarLounger
Posts: 354
Joined: 27 Oct 2013, 15:11
Location: Gurgaon INDIA

Re: Pivot Table in a row of aall data of a document no.

Post by PRADEEPB270 »

Hansv sir,

Thanks for the help.

Almost code is ok but red highlighted font is not coming in the row.

See the attchment file.
Regards

Pradeep Kumar Gupta
INDIA

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Pivot Table in a row of aall data of a document no.

Post by HansV »

I had assumed that you wanted the first row to stay in place. Here is a new version:

Code: Select all

Sub Test()
    Const col1 = 2 ' Doc No
    Const col2 = 3 ' Plant
    Const col3 = 5 ' BOE
    Const col4 = 6 ' Amount
    Const hrw = 4  ' Header row
    Dim lrw As Long
    Dim r As Long
    Dim r0 As Long
    Dim lcl As Long
    Dim c As Long
    Application.ScreenUpdating = False
    lrw = Cells(Rows.Count, col1).End(xlUp).Row
    lcl = Cells(hrw, Columns.Count).End(xlToLeft).Column
    r0 = hrw + 1
    c = lcl
    For r = hrw + 1 To lrw
        If Cells(r, col1).Value <> Cells(r - 1, col1).Value Then
            r0 = r
            c = lcl
        End If
        Cells(hrw, c + 1).Value = "PLANT"
        Cells(hrw, c + 2).Value = "BOE"
        Cells(hrw, c + 3).Value = "AMOUNT"
        Cells(r, col2).Copy Cells(r0, c + 1)
        Cells(r, col3).Copy Cells(r0, c + 2)
        Cells(r, col4).Copy Cells(r0, c + 3)
        c = c + 3
    Next r
    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

User avatar
PRADEEPB270
3StarLounger
Posts: 354
Joined: 27 Oct 2013, 15:11
Location: Gurgaon INDIA

Re: Pivot Table in a row of aall data of a document no.

Post by PRADEEPB270 »

Yes,it is perfect.

Thanks Hans Sir for awesome cooperation.

I salute to you sir for everytime.
Regards

Pradeep Kumar Gupta
INDIA