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 ?
Pivot Table in a row of aall data of a document no.
-
- 3StarLounger
- Posts: 354
- Joined: 27 Oct 2013, 15:11
- Location: Gurgaon INDIA
Pivot Table in a row of aall data of a document no.
Regards
Pradeep Kumar Gupta
INDIA
Pradeep Kumar Gupta
INDIA
-
- 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.
Is there is no solution of my question ?
Regards
Pradeep Kumar Gupta
INDIA
Pradeep Kumar Gupta
INDIA
-
- Administrator
- Posts: 12619
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Pivot Table in a row of aall data of a document no.
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.
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
-
- Administrator
- Posts: 78587
- 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.
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
Hans
-
- 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.
Hansv sir,
Thanks for the help.
Almost code is ok but red highlighted font is not coming in the row.
See the attchment file.
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
Pradeep Kumar Gupta
INDIA
-
- Administrator
- Posts: 78587
- 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.
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
Hans
-
- 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.
Yes,it is perfect.
Thanks Hans Sir for awesome cooperation.
I salute to you sir for everytime.
Thanks Hans Sir for awesome cooperation.
I salute to you sir for everytime.
Regards
Pradeep Kumar Gupta
INDIA
Pradeep Kumar Gupta
INDIA