Splitting Data in multiple rows of single column into different columns

%3CLINGO-SUB%20id%3D%22lingo-sub-1879538%22%20slang%3D%22en-US%22%3ESplitting%20Data%20in%20multiple%20rows%20of%20single%20column%20into%20different%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1879538%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3CBR%20%2F%3EPlease%20can%20you%20look%20at%20the%20sample%20raw%20data%20and%20expected%20output%20here%20below%20which%20should%20help%20understand%20how%20the%20data%20is%20set%20up%20and%20the%20desired%20output.%3C%2FP%3E%3CP%3EI%20would%20like%20to%20be%20able%20to%20split%20the%20rows%20at%20every%20name%20(%20or%20any%20other%20suggestion%20)%26nbsp%3B%20at%20the%20rest%20of%20the%20data%20can%20move%20into%20adjacent%20column%3C%2FP%3E%3CP%3ECan%20i%20request%20help%20in%20getting%20this%20done%3F%3C%2FP%3E%3CP%3E(%20Would%20like%20to%20get%20this%20done%20in%20power%20query%20preferably%20)%3C%2FP%3E%3CP%3E%3CSPAN%3ESample%20raw%20data%20in%20notepad%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EJack%20Reacher%3C%2FP%3E%3CP%3E4%20purchases%3C%2FP%3E%3CP%3E3%20Days%20ago%3C%2FP%3E%3CP%3Eshoes%3C%2FP%3E%3CP%3Esocks%3C%2FP%3E%3CP%3Eothers%3C%2FP%3E%3CP%3Eharrypoter%3C%2FP%3E%3CP%3E2%20purchases%3C%2FP%3E%3CP%3E10%20Days%20ago%3C%2FP%3E%3CP%3Ewands%3C%2FP%3E%3CP%3Eowls%3C%2FP%3E%3CP%3EEthan%20Hunt%3C%2FP%3E%3CP%3E10%20Purchases%3C%2FP%3E%3CP%3E15%20Days%20ago%3C%2FP%3E%3CP%3Egun%3C%2FP%3E%3CP%3Emissile%3C%2FP%3E%3CP%3Esattelite%3C%2FP%3E%3CP%3Esyringes%3C%2FP%3E%3CP%3Eariplanes%3C%2FP%3E%3CP%3Eothers%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EExpected%20Output%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PQ.JPG%22%20style%3D%22width%3A%20569px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F233115iE5B24472C83B45B8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22PQ.JPG%22%20alt%3D%22PQ.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks%20%26amp%3B%20regards%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EDhritiman%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1879538%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1879975%22%20slang%3D%22en-US%22%3ERe%3A%20Splitting%20Data%20in%20multiple%20rows%20of%20single%20column%20into%20different%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1879975%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F733082%22%20target%3D%22_blank%22%3E%40DhritimanL%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20data%20has%20no%20uniformity%20like%20name%20intervals%20are%20not%20common%20therefore%20any%20single%20formula%20or%20method%20will%20not%20work%20properly.%20The%20simplest%20I%20can%20suggest%20is%20TRANSPOSE.%20Which%20need%20to%20use%20block%20wise.%3C%2FP%3E%3CP%3ELike%20Copy%20Jack%20Reacher%20to%20Others%2C%20then%20Harry%20potter%20to%20Owls%20and%20so%20next.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EAnother%20is%20this%2C%20possibly%20help%20you.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rajesh-S_0-1605178154060.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F233129iA5A6CC113F4BDADE%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Rajesh-S_0-1605178154060.png%22%20alt%3D%22Rajesh-S_0-1605178154060.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EHow%20it%20works%3A%3C%2FSTRONG%3E%3C%2FP%3E%3COL%3E%3CLI%3EPut%20Helper%20data%20as%20show%20in%20Screen%20Shot.%3C%2FLI%3E%3CLI%3EFormula%20in%20cell%20D98%2C%20finish%20with%20Enter%20%26amp%3B%20fill%20across.%3C%2FLI%3E%3C%2FOL%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIFERROR(IF(%24C89%3D1%2CINDEX(%24A%2489%3A%24A%2494%2CCOLUMN(A1))%2CIF(%24C89%3D2%2CINDEX(%24A%2495%3A%24A%2499%2CCOLUMN(A1))%2CIF(%24C89%3D3%2CINDEX(%24A%24100%3A%24A%24108%2CCOLUMN(A1))%2C%22%22)))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3E%3CEM%3EAdjust%20cell%20references%20in%20the%20formula%20as%20needed.%3C%2FEM%3E%3C%2FLI%3E%3C%2FUL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1880081%22%20slang%3D%22en-US%22%3ERe%3A%20Splitting%20Data%20in%20multiple%20rows%20of%20single%20column%20into%20different%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1880081%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F93699%22%20target%3D%22_blank%22%3E%40Rajesh-S%3C%2FA%3E%26nbsp%3BThank%20you%20for%20the%20response%2C%20however%20if%20you%20notice%20there%20is%20a%20repeat%20pattern%20occurring%20which%20is%20the%20line%20ending%20as%20X%20purchases%2C%20I%20was%20hoping%20if%20I%20can%20find%20a%20way%20to%20leverage%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20positive%20there%20must%20bea%20way%20to%20break%20the%20data%20at%20that%20level%20and%20move%20the%20balance%20into%20the%20next%20column%2C%20we%20just%20need%20to%20figure%20out%20a%20way.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'll%20keeping%20looking%20out%20for%20solutions%2C%20meanwhile%20if%20this%20point%20trigger%20something%20to%20you%2C%20do%20please%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1880171%22%20slang%3D%22en-US%22%3ERe%3A%20Splitting%20Data%20in%20multiple%20rows%20of%20single%20column%20into%20different%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1880171%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F733082%22%20target%3D%22_blank%22%3E%40DhritimanL%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20your%20serie%20starts%20in%20A1.%20Formulas%20from%20B1%20to%20E1%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3EB1%3C%2FFONT%3E%3DIF(ISNUMBER(SEARCH(%22%20purchase%22%2CA2))%2CA1%2C%22%22)%3CBR%20%2F%3E%3CFONT%20color%3D%22%23FF0000%22%3EC1%3C%2FFONT%3E%3DIF(B1%26lt%3B%26gt%3B%22%22%2CA2%2C%22%22)%3CBR%20%2F%3E%3CFONT%20color%3D%22%23FF0000%22%3ED1%3C%2FFONT%3E%3DIF(C1%26lt%3B%26gt%3B%22%22%2CA3%2C%22%22)%3CBR%20%2F%3E%3CFONT%20color%3D%22%23FF0000%22%3EE1%3C%2FFONT%3E%3DIF(D1%26lt%3B%26gt%3B%22%22%2CTEXTJOIN(%22%20%22%2C%2COFFSET(A4%2C0%2C0%2CMATCH(%22*%20purchase*%22%2CA4%3AA10000%2C0)-2))%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*****ED****%3C%2FP%3E%3CP%3EE-column%20will%20be%20wrong%20for%20the%20last%20post.%20try%20something%20like%3A%3C%2FP%3E%3CP%3E'%3DIF(D1%26lt%3B%26gt%3B%22%22%2CTEXTJOIN(%22%20%22%2C%2COFFSET(A4%2C0%2C0%2CIFERROR(MATCH(%22*%20purchase*%22%2CA3%3AA10000%2C0)-2%2CMATCH(%22*%22%2CA3%3AA10000%2C-1))))%2C%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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 at every name ( or any other suggestion )  at the rest of the data can move into adjacent column

Can i request help in getting this done?

( Would like to get this done in power query preferably )

Sample raw data in notepad

Jack Reacher

4 purchases

3 Days ago

shoes

socks

others

harrypoter

2 purchases

10 Days ago

wands

owls

Ethan Hunt

10 Purchases

15 Days ago

gun

missile

sattelite

syringes

ariplanes

others

 

Expected Output

PQ.JPG

Thanks & regards

Dhritiman

 

 

6 Replies
Highlighted

@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.

 

Rajesh-S_0-1605178154060.png

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.
Highlighted

@Rajesh-S 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 

Highlighted

@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)))),"")

Highlighted

@DhritimanL Since you explicitly asked for a PQ solution, I'm giving you just that. I suspect that your real data is much more complicated and you don't want a lot of manual interference with helper columns and complex formulae. Or do you just want to learn about PQ?

Just add to the blue table and refresh the green one, as long as you make sure that the Name is always  in the row directly above the row that includes the string "urchase". PQ is case sensitive. This way, you will capture all occurrences of "purchases", "Purchases", "purchase" and "Purchase"

Screenshot 2020-11-12 at 13.21.48.png

Highlighted

@Riny_van_Eekelen Thank you So much, yes indeed the actual data has other key words so i went ahead and edited the m-code and replaced "purchases" with the required words and a few other minor tweak to make it work like a charm for my data.

 

Much appreciated and grateful.

 

Thank you once again.

 

Kind regards

Dhritiman

 

Highlighted