need help for converting the data

  • I need to covert data as below:


    Help me with macro.

    Input
    TEXT1 TEXT2 TEXT3
    001-00043 7113 2102
    001-00043 7113 20000
    001-00043 7114 2103
    Converted Output
    TEXT1 TEXT2 TEXT3
    001-00043 7113,7114 2102,20000,2103
  • Hello,


    If you are using Excel 2016, you can have Text in your Pivot Table data area ...( and a DAX formula )


    see attached test file


    Hope this will help

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • To automate the Pivot refresh put this in the Worksheet Object Module for the sheet that contains the data Table (the pivot table can be moved to another sheet if required). Any change to the data table data will refresh the pivot table.

    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Not Intersect(Target, [Table1]) Is Nothing Then ThisWorkbook.RefreshAll
    3. End Sub

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • I need to add one column (TEXT22) and show values in pivot table


    input

    TEXT1 TEXT2 TEXT22 TEXT3
    001-00043 7113 desc1 2102
    001-00043 7113 desc1 20000
    001-00043 7114 desc1 2103
    001-00044 7114 desc3 2103
    001-00045 7114 desc4 2103
    001-00046 7114 desc5 2103
    001-00047 7114 desc6 2103

    output


    TEXT1 TEXT2 TEXT3 text22
    001-00043 7113, 7114 2102, 20000, 2103 desc1
    001-00044 7114 2103 desc3
    001-00045 7114 2103 desc4
    001-00046 7114 2103 desc5
    001-00047 7114 2103 desc6
  • Hello,


    As long as your Input Table contains all your records ... the Pivot Table Refresh will produce your expected result ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)