Forum Discussion

Rosanna985's avatar
Rosanna985
Copper Contributor
Oct 19, 2021
Solved

Decoupling rows

I have a file with unique person ID's in the first column and different products that they have purchased in another column. I want to change the product descriptions so that they are not bucketed un...
excel
  • bosinander's avatar
    bosinander
    Oct 19, 2021

    Rosanna985 Hi. It seems you have a static pivot table that you want to use as a merely normal data range? The empty cells have to be filled with the data/Person ID above..?

     

    eg L6 shows A6 but J7 has to show J6 since A6 is empty?

     

    J5 =IF(A5="",J4,A5)

    Copy the to J5:N20

    The value columns are not to be repeated and thus just fetching data in o5:Q20.

    o5 =F5

    If you want to get rid of the Total rows, there is an applied filter in S5

     

Deleted's avatar
Deleted
Oct 19, 2021
Hey, if the values are seperated by the same delimiter you can try to automatically seperate them into different columns using this:
https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7

Hope it helps.
Rosanna985's avatar
Rosanna985
Copper Contributor
Oct 19, 2021
Hey thanks for the response. This splits texts into different columns. I need to split ROWS that are bucketed under one person id . i.e. now there is one row under 1 person ID with 3 different products listed. I need to turn that into 3 rows with the same person ID each containing one product
  • Deleted's avatar
    Deleted
    Oct 19, 2021
    Hey, sorry for the misunderstanding. You could first split it into different columns, and then use the =TRANSPOSE() function to turn the different columns into rows. Third step would include copy&pasting in the transposed cells as values (copy, then right click and select the value paste option, see here: https://support.microsoft.com/en-us/office/paste-options-8ea795b0-87cd-46af-9b59-ed4d8b1669ad)
    and deleting the extra columns.
    • Rosanna985's avatar
      Rosanna985
      Copper Contributor
      Oct 19, 2021
      thanks for the additional response. This does not yield the desired outcome but I do thank you for responding
      • bosinander's avatar
        bosinander
        Iron Contributor
        Oct 19, 2021

        Rosanna985 Hi. It seems you have a static pivot table that you want to use as a merely normal data range? The empty cells have to be filled with the data/Person ID above..?

         

        eg L6 shows A6 but J7 has to show J6 since A6 is empty?

         

        J5 =IF(A5="",J4,A5)

        Copy the to J5:N20

        The value columns are not to be repeated and thus just fetching data in o5:Q20.

        o5 =F5

        If you want to get rid of the Total rows, there is an applied filter in S5

         

Resources