Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    My first work with PIVOT TABLE... (2000 sr 1)

    Is possible to show in the column A of the pivot table the name present in the column AP plus ther related value? one example is in A20.
    final result is here:

    I.T.M. S.R.L. (65501942)-(14)

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: My first work with PIVOT TABLE... (2000 sr 1)

    The easiest way (if you want it all in col A) would be to create in the source data that info (for example in AW2):
    =trim(AP2)&" ("& AQ2&")-("&AT2&")"

    and copy this down the column

    The use this column in the pivot table.

    If you can keep them as 3 columns, just add those additional columns to the pivot rows and they will all be listed (you can delete the rows with totals if desired by right one of each and selecting "delete")

    Steve

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: My first work with PIVOT TABLE... (2000 sr 1)

    Good idea...
    But i want also the parentesis...

  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: My first work with PIVOT TABLE... (2000 sr 1)

    ok... solved for problem "parentesis"

    But not is possible to automatize, with a mcro, this process to recopy the new value stared from AW2, do until in the column AP is not present a value?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: My first work with PIVOT TABLE... (2000 sr 1)

    Perhaps like this?
    <pre>Option Explicit
    Sub CreateNome2()
    Dim wks As Worksheet
    Dim rng As Range
    Dim rCell As Range
    Set wks = Worksheets("POS_-_ANALITICO_MENSILE")
    With wks
    .Columns("AW").ClearContents
    .Range("Aw1") = "NOME2"
    Set rng = .Range(.Range("Aw2"), _
    .Range("Av65536").End(xlUp).Offset(0, 1))
    For Each rCell In rng
    rCell.Value = Trim(rCell.Offset(0, -7)) & _
    " (" & rCell.Offset(0, -6) & ")-(" & _
    rCell.Offset(0, -3) & ")"
    Next
    End With
    End Sub</pre>


    Steve

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: My first work with PIVOT TABLE... (2000 sr 1)

    Great code...
    Only a little condition:
    Make the recopy of the new vale in column AW, if in the column AP are present a filled cells...
    I attach a new file with a blank cells in AP
    Tks.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: My first work with PIVOT TABLE... (2000 sr 1)

    I don't understand.

    What do you want to do if column AP has a blank?

    Steve

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: My first work with PIVOT TABLE... (2000 sr 1)

    ... no copy the value in the column AW
    i have modified the wbook with the final result... see the line AW14 to AW17
    Tks for patience... and sorry for my English

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: My first work with PIVOT TABLE... (2000 sr 1)

    You want to test if the cell in column AP is blank (or contains only spaces). So you can insert a test to check this:

    Sub CreateNome2()
    Dim wks As Worksheet
    Dim rng As Range
    Dim rCell As Range

    Set wks = Worksheets("POS_-_ANALITICO_MENSILE")
    With wks
    .Columns("AW").ClearContents
    .Range("AW1") = "NOME2"
    Set rng = .Range(.Range("AW2"), _
    .Range("Av65536").End(xlUp).Offset(0, 1))
    For Each rCell In rng
    If Trim(rCell.Offset(0, -7)) = "" Then
    rCell.Value = ""
    Else
    rCell.Value = Trim(rCell.Offset(0, -7)) & _
    " (" & rCell.Offset(0, -6) & ")-(" & _
    rCell.Offset(0, -3) & ")"
    End If
    Next rCell
    End With
    End Sub

Posting Permissions

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