Forum Discussion

zau73's avatar
zau73
Copper Contributor
Jul 15, 2024

How to re arrange data in excel?

Hi, Currently i have my parameters all in the same column. But i need to arrange them in rows and column. For example:

I want to have the Name and Count as column, that means i only want to 2 columns (Name and Count) and all the values under it. Please advice on how i can do this. 

Excel
Formulas and Functions
  • Harun24HR's avatar
    Harun24HR
    Jul 15, 2024

    zau73 You can use WRAPROWS() function

     

    =DROP(WRAPROWS(A1:A14,3,0),,-1)

     

     

    If you want to clean Name: and Count: strings then use SUBSTITUTE() function.

     

    =SUBSTITUTE(DROP(WRAPROWS(A1:A14,3,0),,-1),{"Name: ","Count: "},"")

     

     

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Jul 15, 2024

    zau73 You can use WRAPROWS() function

     

    =DROP(WRAPROWS(A1:A14,3,0),,-1)

     

     

    If you want to clean Name: and Count: strings then use SUBSTITUTE() function.

     

    =SUBSTITUTE(DROP(WRAPROWS(A1:A14,3,0),,-1),{"Name: ","Count: "},"")

     

     

  • djclements's avatar
    djclements
    Bronze Contributor
    Jul 15, 2024

    zau73 If you are using Excel for MS365 or Excel for the web, you could try the following:

     

    =WRAPROWS(TEXTAFTER(TOCOL(B:B, 1), ": "), 2)

     

    Adjust the column reference as needed (I assumed column B contains the data in this example).

     

    TOCOL removes the blank rows by setting the optional [ignore] argument to 1. TEXTAFTER returns only the values after the colons (": "), and WRAPROWS reorganizes the data into 2 columns.

     

    Also, the numeric values will initially be returned as text strings. To convert them to numeric values, use the following:

     

    =LET(arr, WRAPROWS(TEXTAFTER(TOCOL(B:B, 1), ": "), 2), IFERROR(--arr, arr))

     

    See attached...

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jul 15, 2024

    zau73 

    An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of a green table and right-click with the mouse and select refresh to update the green result table.

     

    The data layout in the attached file is for illustration. You can as well place the green result tables in another worksheet.

Resources