Forum Discussion

DhritimanL's avatar
DhritimanL
Copper Contributor
Nov 12, 2020

Splitting Data in multiple rows of single column into different columns

Hi! Please can you look at the sample raw data and expected output here below which should help understand how the data is set up and the desired output. I would like to be able to split the rows a...
excel
Rajesh_Sinha's avatar
Rajesh_Sinha
Iron Contributor
Nov 12, 2020

DhritimanL 

 

Your data has no uniformity like name intervals are not common therefore any single formula or method will not work properly. The simplest I can suggest is TRANSPOSE. Which need to use block wise.

Like Copy Jack Reacher to Others, then Harry potter to Owls and so next.

 

Another is this, possibly help you.

 

How it works:

  1. Put Helper data as show in Screen Shot.
  2. Formula in cell D89, finish with Enter & fill across.

 

=IFERROR(IF($C89=1,INDEX($A$89:$A$94,COLUMN(A1)),IF($C89=2,INDEX($A$95:$A$99,COLUMN(A1)),IF($C89=3,INDEX($A$100:$A$108,COLUMN(A1)),""))),"")

 

 

  • Adjust cell references in the formula as needed.
DhritimanL's avatar
DhritimanL
Copper Contributor
Nov 12, 2020

Rajesh_Sinha Thank you for the response, however if you notice there is a repeat pattern occurring which is the line ending as X purchases, I was hoping if I can find a way to leverage that.

 

I am positive there must bea way to break the data at that level and move the balance into the next column, we just need to figure out a way.

 

I'll keeping looking out for solutions, meanwhile if this point trigger something to you, do please help.

 

Regards 

  • MindreVetande's avatar
    MindreVetande
    Iron Contributor
    Nov 12, 2020

    DhritimanL 

    If your serie starts in A1. Formulas from B1 to E1

    B1=IF(ISNUMBER(SEARCH(" purchase",A2)),A1,"")
    C1=IF(B1<>"",A2,"")
    D1=IF(C1<>"",A3,"")
    E1=IF(D1<>"",TEXTJOIN(" ",,OFFSET(A4,0,0,MATCH("* purchase*",A4:A10000,0)-2)),"")

     

    *****ED****

    E-column will be wrong for the last post. try something like:

    '=IF(D1<>"",TEXTJOIN(" ",,OFFSET(A4,0,0,IFERROR(MATCH("* purchase*",A3:A10000,0)-2,MATCH("*",A3:A10000,-1)))),"")

Resources