Organizing groups data with inconsistent row count

%3CLINGO-SUB%20id%3D%22lingo-sub-1756914%22%20slang%3D%22en-US%22%3EOrganizing%20groups%20data%20with%20inconsistent%20row%20count%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1756914%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20am%20not%20sure%20exactly%20how%20to%20put%20this%20into%20words%20so%20I%20was%20unable%20to%20search%20a%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20large%20list%20of%20items%20grouped%20into%20sections%20in%20a%20word%20document.%20I%20am%20trying%20to%20organize%20them%20into%20excel%20but%20am%20having%20trouble%20since%20the%20information%20is%20grouped%20in%20inconsistent%20sizes.%20I%20want%20to%20output%20them%20with%20the%20chapter%20next%20to%20the%20data%20so%20I%20can%20condense%20the%20information%20and%20easily%20sort%2Ffilter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20basically%20dumping%20the%20raw%20word%20doc%20data%20into%20one%20sheet%20as%20an%20input%2C%20and%20then%20on%20a%20second%20sheet%20trying%20to%20have%20it%20appear%20sorted.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eexample%20of%20how%20it%20appears%20when%20pasted%20into%20excel%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%2264%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%20height%3D%2220%22%3ETitle%20of%20database%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3Edescription%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3EChapter%3A%2015%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA132456789%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA789456126%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA321987654%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA456321987%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA213465489%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETitle%20of%20database%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3Edescription%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3EChapter%3A%2015%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA94567321%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA95431456%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA65048594%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETitle%20of%20database%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3Edescription%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3EChapter%3A%2024%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA94567321%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA95431456%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA65048594%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA65048594%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA94567321%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA95431456%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA65048594%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3Eand%20so%20on...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20list%20the%20Chapter%20next%20to%20each%20item%20like%20the%20example%20below%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22128%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%20height%3D%2220%22%3ETA132456789%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E15%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA789456126%3C%2FTD%3E%3CTD%3E15%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA321987654%3C%2FTD%3E%3CTD%3E15%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA456321987%3C%2FTD%3E%3CTD%3E15%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA213465489%3C%2FTD%3E%3CTD%3E15%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA94567321%3C%2FTD%3E%3CTD%3E15%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA95431456%3C%2FTD%3E%3CTD%3E15%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA65048594%3C%2FTD%3E%3CTD%3E15%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA94567321%3C%2FTD%3E%3CTD%3E24%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA95431456%3C%2FTD%3E%3CTD%3E24%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA65048594%3C%2FTD%3E%3CTD%3E24%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA65048594%3C%2FTD%3E%3CTD%3E24%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA94567321%3C%2FTD%3E%3CTD%3E24%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA95431456%3C%2FTD%3E%3CTD%3E24%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETA65048594%3C%2FTD%3E%3CTD%3E24%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1756914%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1757195%22%20slang%3D%22en-US%22%3ERe%3A%20Organizing%20groups%20data%20with%20inconsistent%20row%20count%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1757195%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F824275%22%20target%3D%22_blank%22%3E%40mattcyborg%3C%2FA%3E%26nbsp%3BEasiest%20done%20with%20Get%26amp%3BTransform%20Data%20(a.k.a.%20Power%20Query).%20The%20attached%20file%20contains%20an%20example%20on%20the%20basis%20of%20the%20data%20you%20provided.%26nbsp%3BIn%20summary%2C%20the%20steps%20are%20as%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20Connect%20to%20your%20list%3C%2FP%3E%3CP%3E2)%20Extract%20the%20chapter%20number%20into%20its%20own%20column%3C%2FP%3E%3CP%3E3)%20Get%20rid%20of%20the%20unnecessary%20rows%3C%2FP%3E%3CP%3E4)%20Sort%20the%20list%20as%20desired%3C%2FP%3E%3CP%3E5)%20Load%20back%20to%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20you%20are%20comfortable%20with%20this.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello, I am not sure exactly how to put this into words so I was unable to search a solution.

 

I have a large list of items grouped into sections in a word document. I am trying to organize them into excel but am having trouble since the information is grouped in inconsistent sizes. I want to output them with the chapter next to the data so I can condense the information and easily sort/filter.

 

I am basically dumping the raw word doc data into one sheet as an input, and then on a second sheet trying to have it appear sorted.

 

example of how it appears when pasted into excel

 

Title of database
description
Chapter: 15
 
TA132456789
TA789456126
TA321987654
TA456321987
TA213465489
 
Title of database
description
Chapter: 15
 
TA94567321
TA95431456
TA65048594
 
Title of database
description
Chapter: 24
 
TA94567321
TA95431456
TA65048594
TA65048594
TA94567321
TA95431456
TA65048594

and so on...

 

 

I want to list the Chapter next to each item like the example below

 

TA13245678915
TA78945612615
TA32198765415
TA45632198715
TA21346548915
TA9456732115
TA9543145615
TA6504859415
TA9456732124
TA9543145624
TA6504859424
TA6504859424
TA9456732124
TA9543145624
TA6504859424
3 Replies
Highlighted

@mattcyborg Easiest done with Get&Transform Data (a.k.a. Power Query). The attached file contains an example on the basis of the data you provided. In summary, the steps are as follows:

 

1) Connect to your list

2) Extract the chapter number into its own column

3) Get rid of the unnecessary rows

4) Sort the list as desired

5) Load back to Excel.

 

Let me know if you are comfortable with this.

Highlighted

@Riny_van_Eekelen aaahhh I think I got it. I had the chapters extracted but was stuck there with large blank row gaps between. I saw you used fill down and that got me to where I needed to be. Thanks that helped speed up this process significantly!

Highlighted