trying to create a dynamic range based on column A cell value...index match?

[dbq]

I have a worksheet that has 2 columns of information (simplified version).

Column A has a non-sorted, nonlinear, list of part numbers, descriptions and some empty cells.
It is divided into variable row sections by “header” characters. These characters are [1], [2], [3], etc. UP TO [20] (with the brackets)

Column B has the associated quantities of each part.

I would like a macro that will set up columns C,D,E,F,G,H,I,J,K,L so that I can move the part quantities under rows below [1] and above [2] to column C,part quantities under rows below [2] and above [3] to column D, and so on.

When complete, all quantities will be in the same row they originated in, but would shift to the column associated with the “header” character. ( Section [1] quantities in column C., Section [2] quantities in column D.,etc.)

I've tried to use MATCH to identify the row each "header" is in to create a dynamic range, but cant seam to use the result as part of a formula...
Im running out of ideas..., could really use some help on this.

Just for clarity, the source & result should look like this:
(thanks in advance)

sort.jpg

[GeneralDisarray]

Please post an example spreadsheet so this is easier to answer.

[dbq]

Here's a spreadsheet showing the before (sheet 1) and after (sheet 2)
thank you.

[dbq]

Is this even possible? Or is a better description required?

[oeldere]

I didin't try to find an answer yet, but I am curios what you want to achieve.

What is your exactly goal, after you put it in columns (instead of rows).

[GeneralDisarray]

You could do this - if you don't mind zeros showing up in the columns.

Make a row of headers like [1] [2] [3] [4] ... and then paste use this formula in the area under each header (see attached example, starts in column F).

=IFERROR((("[" & SUMPRODUCT((LEFT($A$1:$A4,1)="[")*1)&"]")=F$4)*$B5,0)


OR, if you want to not show the 0's use this: (starts in the M column)
=IF(IFERROR((("[" & SUMPRODUCT((LEFT($A$1:$A4,1)="[")*1)&"]")=M$4)*$B5,0)>0,$B5,"")


Which you use depends on what you would do afterwards - if you need each cell to work as a number afterwards, leave the 0's. BTW, Column D just shows what the sumproduct() part of the function does for you - you don't need that column there, I just left it for eductational purposes. :D

[dbq]

This is perfect, thank you.