Unstack Data that is Separated by Bold Text: Challenge w/Kevin Lehrbass - Formulas, VBA, Power Query

Problem: a tall column of data that needs to be unstacked and converted into neat columns and rows.
Twist: the distinction where the list needs to be divided is by bold font.

(Download the workbook:

Parents' names are bold. Childrens' names are regular font.

How does this list get unstacked with the children next to the right parent?

Kevin Lehrbass and I are here to offer several solutions. I open with Excel's Get and Transform (Power Query). Kevin takes over and shows a formula-based solution and a VBA solution!

Three whole solutions for you! Cleansing this data and getting this stuff unstacked!

Check out Kevin's channel:

My book: Guerrilla Data Analysis 2nd Edition

[excelisfun]

Thanks Oz!! Thanks Kevin!! Thanks for the awesome Team Excel Fun!!!!
P.S. This is a great example of crazy things that Excel People do... Maybe I will have to do a video on this cool topic : )

[raymondjurado]

Thanks for sharing! Two awesome solutions to unstack and push the data horizontally for humans to read.

[excel-in-g]

Just discovered this old video. The wife is out with her friends, so what else to do then watch some great Oz?
Replace bold... Love that! One of the many tricks one forgets easily. Never would have thought about using that.

Kevin is one of the scary people 🤣🤣🤣

[eladiobardelli]

fantásticos tips, realmente inspiras a los que nos gusta hacer maravillas con Excel.

[lcorcoran]

This video is just what I was look for, had a problem with two kind of headers that I need to pull out in to their own columns one will a fill color and one with an underline, ended up with two formula "=SCAN("", MAP(F2&SEQUENCE(MATCH("*total*", INDIRECT(E2), 0)-MATCH("*Deliverables Details*", INDIRECT(E2), 0), 1, MATCH("*Deliverables Details*", INDIRECT(E2), 0), 1), LAMBDA(A, IF(INDIRECT(A)="", "", IF(CheckFillColor(INDIRECT(A))<$A$2, INDIRECT(A), "")))), LAMBDA(x, y, IF(y="", x, y)))" & =SCAN("", MAP(F2&SEQUENCE(MATCH("*total*", INDIRECT(E2), 0)-MATCH("*Deliverables Details*", INDIRECT(E2), 0), 1, MATCH("*Deliverables I wish powerquery could see cell formatting.

[BillSzysz]

Thanks Kevin and OZ... great contest :-))

[excelisfun]

OZ, How did you get the formula in E4:


to work!?!?

The formula element "E3+1" is “Text”+Number, which should yield an error? Did it error out for you?

Shouldn’t the formula in E4 really be:

?

[manishjain]

Thanks for sharing! One quick question I don't have fixed number of columns could you help me what needs to be done in that case

[jatinchhabra]

Can I also send you a situation? Although it's not as complicated as the one in this video.