Organizing groups data with inconsistent row count

[mattcyborg]

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

TA132456789	15
TA789456126	15
TA321987654	15
TA456321987	15
TA213465489	15
TA94567321	15
TA95431456	15
TA65048594	15
TA94567321	24
TA95431456	24
TA65048594	24
TA65048594	24
TA94567321	24
TA95431456	24
TA65048594	24

[#2]

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

[#3]

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

[#4]

@mattcyborg Great!