How do I make my excel spreadsheet take an un-organized column and group it into several small lists with a formula?

[greyland]

So I'm trying to find a formula to make my Excel spreadsheet take a very un-ordered list(Column B), and sort of compress it into several ordered lists automatically.


Current Example Desired Outcome
A B A B
1: John - Apple 1: John - Apple
2: 2: - Orange
3: - Orange 3:
4: 4:
5: Nate 5: Nate - Apple
6: - Apple 6: - Orange
7: 7: - Berry
8: -Orange 8:
9: -Berry 9:


I need the things in Column A to stay where they are. And for column B to be ordered so that; from B1:B4 and B5:B9 etc. is in a list next to the A column values without spaces until the next list.

This is only one small piece of the whole spreadsheet and the cell locations and values are different than the example. And both of these columns are auto filled with formulas based on the rest of the spreadsheet, and data that is put in. If your interested the current formula in the B column looks like this: =IF(J3="","", IF(J3=0,D3, "")) That is how the info For Column B is generated. Now I just need it to order the information like the above example.

I also attached a picture of the portion of the actual spreadsheet i am trying to do this to if you would like to look at that for reference.

Any help would be greatly appreciated.

 

[greyland]

I cant edit the post but the examples I typed up did not post at all how I typed them, so its basicly useless. But the picture is there. And hopefully I explained myself well enough.

 

[Michael M]

Using VBA, could you have the blank rows removed
AND
The numbers in Column "L" filled in down the sheet ??
Would that help ?

 

[Peter_SSs]

the current formula in the B column looks like this: =IF(J3="","", IF(J3=0,D3, ""))

See if this is any use. It involves using a different formula in column B.
I have used column K as a helper column but you could use any column off to the right (& hide it if preferred).
Each formula copied down.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2
3	John	Apple		Apple						0	1
4		Orange									1
5				Orange						0	1
6											1
7	Nate	Apple		Pear						3	2
8		Orange									2
9		Berry		Apple						0	2
10				Grape						1	2
11				Orange						0	2
12				Berry						0	2
13											2
14											2
No blanks

Cell Formulas
Range	Formula
B3:B14	B3	=IFERROR(INDEX(D:D,AGGREGATE(15,6,ROW(D$1:D$30)/((J$1:J$30&""="0")*(K$1:K$30=K3)),ROW()-LOOKUP("ZZZ",A$1:A3,ROW(A$1:A3))+1)),"")
K3:K14	K3	=K2+(A3<>"")

 

[greyland]

See if this is any use. It involves using a different formula in column B.
I have used column K as a helper column but you could use any column off to the right (& hide it if preferred).
Each formula copied down.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2
3	John	Apple		Apple						0	1
4		Orange									1
5				Orange						0	1
6											1
7	Nate	Apple		Pear						3	2
8		Orange									2
9		Berry		Apple						0	2
10				Grape						1	2
11				Orange						0	2
12				Berry						0	2
13											2
14											2
No blanks

Cell Formulas
Range	Formula
B3:B14	B3	=IFERROR(INDEX(D:D,AGGREGATE(15,6,ROW(D$1:D$30)/((J$1:J$30&""="0")*(K$1:K$30=K3)),ROW()-LOOKUP("ZZZ",A$1:A3,ROW(A$1:A3))+1)),"")
K3:K14	K3	=K2+(A3<>"")

Thank you for helping me out! The formula works when I write out a test sheet the way you did. But when I try and implement it into my actual worksheet it just repeats the first item until the end. I posted a picture of it as well so that makes a little more sense. I changed the formula a bit so that the columns match. And I've been playing around with it this morning trying to get something to work. I highlighted the 3 columns I'm trying to do this to in yellow (P, Q, & R). I also uploaded a picture of the formula in column M.

 

[greyland]

=IFERROR(INDEX(P:P,AGGREGATE(15,6,ROW(P$1:P$32)/((T$1:T$32&""="0")*(S$1:S$32=S3)),ROW()-LOOKUP("ZZZ",L$1:L3,ROW(L$1:L3))+1)),"")

See if this is any use. It involves using a different formula in column B.
I have used column K as a helper column but you could use any column off to the right (& hide it if preferred).
Each formula copied down.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2
3	John	Apple		Apple						0	1
4		Orange									1
5				Orange						0	1
6											1
7	Nate	Apple		Pear						3	2
8		Orange									2
9		Berry		Apple						0	2
10				Grape						1	2
11				Orange						0	2
12				Berry						0	2
13											2
14											2
No blanks

Cell Formulas
Range	Formula
B3:B14	B3	=IFERROR(INDEX(D:D,AGGREGATE(15,6,ROW(D$1:D$30)/((J$1:J$30&""="0")*(K$1:K$30=K3)),ROW()-LOOKUP("ZZZ",A$1:A3,ROW(A$1:A3))+1)),"")
K3:K14	K3	=K2+(A3<>"")

 

[Peter_SSs]

I posted a picture of it

Instead of a picture, can you give it to us in a form that we can copy, so that we have exactly what you have? My signature block below has help with that.

 

[greyland]

Okay I finally figured out how to get it up there like that! Sorry that took me a while. I had a hard time figuring out how to get the add-on

So the first sheet I posted is what it currently looks like. With the formula from last week in columns M-O ( Highlighted blue). And the second one I posted is what I would like it to look like. (I just typed in the values) Everything from A-J works exactly like i would like it to.

P-R is automatically generated based on the info from A-J. It is a list of work orders and parts that will be needed to complete each one. And P-R is parts that we do not have enough of. If Column J has is 0 then that row is transferred to columns P-R. This is a spreadsheet I am making for work.

The part that I need help with is M-O (Highlighted in blue). If were comparing it to the names and fruit examples from last week then column A with the names is column L on mine (containing the letters SWO). and instead of fruits there is the names of parts. (cooler, bearing, sump cover, etc.)

I have been able to use what I have to create one list full of everything I will need to order. But I would like for other people to be able to open this and see the small list for each work order that contains all the parts we are short, right next to the work order.

I hope this is making sense. This is that last little piece I need to make it function exactly how I want and I'm stumped.

Also thank you so much for your help and patience!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	W/O	S/N	Status	Parts Required		Quantity Needed			Quantity In Stock
3																			0
4	SWO-1	123	In Progress									SWO-1							1
5																			1
6	SWO-2	124	In Progress	LH Cooler	145D1111-1	1	1	1	1	1		SWO-2	Drain Hose	145D1111-4	1				2
7				RH Cooler	145D1111-2	1	1	8	8	2			Drain Hose	145D1111-4	1				2
8				Comb. Cooler	145D1111-3	1	1	8	8	2			Drain Hose	145D1111-4	1				2
9				Drain Hose	145D1111-4	1	1	0	0	0			Drain Hose	145D1111-4	1	Drain Hose	145D1111-4	1	2
10													Drain Hose	145D1111-4	1				2
11	SWO-2	125	In Progress	LH Cooler	145D1111-1	1	2	0	1	0		SWO-2	LH Cooler	145D1111-1	1	LH Cooler	145D1111-1	1	3
12				RH Cooler	145D1111-2	1	2	7	8	2			LH Cooler	145D1111-1	1				3
13				Comb. Cooler	145D1111-3	1	2	7	8	2			LH Cooler	145D1111-1	1				3
14				Sump Cover	145D1111-4	1	2	-1	0	0			LH Cooler	145D1111-1	1	Sump Cover	145D1111-4	1	3
15													LH Cooler	145D1111-1	1				3
16	SWO-3	126	In Progress	LH Cooler	145D1111-1	1	3	-1	1	0		SWO-3	LH Cooler	145D1111-1	1	LH Cooler	145D1111-1	1	4
17				RH Cooler	145D1111-2	1	3	6	8	2			LH Cooler	145D1111-1	1				4
18				Comb. Cooler	145D1111-3	1	3	6	8	2			LH Cooler	145D1111-1	1				4
19				Bearings	145D1111-4	2	4	0	2	0			LH Cooler	145D1111-1	1	Bearings	145D1111-4	2	4
20				Cartridge	145D1111-5	1	1	0	0	0			LH Cooler	145D1111-1	1	Cartridge	145D1111-5	1	4
21				Tangwasher	145D1111-6	1	1	7	7	2			LH Cooler	145D1111-1	1				4
22				Drain Hose	145D1111-7	1	1	0	0	0			LH Cooler	145D1111-1	1	Drain Hose	145D1111-7	1	4
23													LH Cooler	145D1111-1	1				4
C-Boxes

Cell Formulas
Range	Formula
M3	M3	=IFERROR(INDEX(P:P,AGGREGATE(15,6,ROW(P$3:P$33)/((J$3:J$33&""="0")*(S$3:S$33=S3)),ROW()-LOOKUP("ZZZ",L$3:L4,ROW(L$3:L4))+1)),"")
P3, P4:P23	P3	=IF(J3="","", IF(J3=0,D3, ""))
Q3, Q4:Q23	Q3	=IF(J3="","", IF(J3=0,E3, ""))
R3, R4:R23	R3	=IF(Q3="","",IF(H3<1,F3,G3-H3))
S3, S4:S23	S3	=S2+(L3<>"")
G4:G23	G4	=IF(E4="","",SUMIF($E$4:E4,E4,$F$4:F4))
H4	H4	{=IF(F4="","",IF(ISERROR(I4-INDEX($G$4:$G$198,MATCH(10,1/($E2:$E$4=E4)))),I4,I4-INDEX($G$4:$G$198,MATCH(10,1/($E2:$E$4=E4)))))}
H5:H23	H5	{=IF(F5="","",IF(ISERROR(I5-INDEX($G$4:$G$198,MATCH(10,1/($E$4:$E4=E5)))),I5,I5-INDEX($G$4:$G$198,MATCH(10,1/($E$4:$E4=E5)))))}
L4:L23	L4	=IF(A4="","",A4)
M4:M23	M4	{=IFERROR(INDEX(P:P,AGGREGATE(15,6,ROW(P$4:P$33)/((J$4:J$33&""="0")*(S$4:S$33=S4)),ROW()-LOOKUP("ZZZ",L$4:L4,ROW(L$4:L4))+1)),"")}
N4:N23	N4	{=IFERROR(INDEX(Q:Q,AGGREGATE(15,6,ROW(Q$1:Q$33)/((J$1:J$33&""="0")*(S$1:S$33=S4)),ROW()-LOOKUP("ZZZ",L$1:L4,ROW(L$1:L4))+1)),"")}
O4:O23	O4	{=IFERROR(INDEX(R:R,AGGREGATE(15,6,ROW(R$1:R$33)/((J$1:J$33&""="0")*(S$1:S$33=S4)),ROW()-LOOKUP("ZZZ",L$1:L4,ROW(L$1:L4))+1)),"")}
J4:J23	J4	=IF(I4="","", IF(I4<G4,0,IF(I4>G4,2, IF(I4=G4, 1,))))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J4:J249	Other Type	Icon set		NO

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	W/O	S/N	Status	Parts Required		Quantity Needed			Quantity In Stock
3																			0
4	SWO-1	123	In Progress									SWO-1							1
5																			1
6	SWO-2	124	In Progress	LH Cooler	145D1111-1	1	1	1	1	1		SWO-2	Drain Hose	145D1111-4	1				2
7				RH Cooler	145D1111-2	1	1	8	8	2									2
8				Comb. Cooler	145D1111-3	1	1	8	8	2									2
9				Drain Hose	145D1111-4	1	1	0	0	0						Drain Hose	145D1111-4	1	2
10																			2
11	SWO-2	125	In Progress	LH Cooler	145D1111-1	1	2	0	1	0		SWO-2	LH Cooler	145D1111-1	1	LH Cooler	145D1111-1	1	3
12				RH Cooler	145D1111-2	1	2	7	8	2			Sump Cover	145D1111-4	1				3
13				Comb. Cooler	145D1111-3	1	2	7	8	2									3
14				Sump Cover	145D1111-4	1	2	-1	0	0						Sump Cover	145D1111-4	1	3
15																			3
16	SWO-3	126	In Progress	LH Cooler	145D1111-1	1	3	-1	1	0		SWO-3	LH Cooler	145D1111-1	1	LH Cooler	145D1111-1	1	4
17				RH Cooler	145D1111-2	1	3	6	8	2			Bearings	145D1111-4	2				4
18				Comb. Cooler	145D1111-3	1	3	6	8	2			Cartridge	145D1111-5	1				4
19				Bearings	145D1111-4	2	4	0	2	0			Drain Hose	145D1111-7	1	Bearings	145D1111-4	2	4
20				Cartridge	145D1111-5	1	1	0	0	0						Cartridge	145D1111-5	1	4
21				Tangwasher	145D1111-6	1	1	7	7	2									4
22				Drain Hose	145D1111-7	1	1	0	0	0						Drain Hose	145D1111-7	1	4
23																			4
C-Boxes

Cell Formulas
Range	Formula
M3	M3	=IFERROR(INDEX(P:P,AGGREGATE(15,6,ROW(P$3:P$33)/((J$3:J$33&""="0")*(S$3:S$33=S3)),ROW()-LOOKUP("ZZZ",L$3:L4,ROW(L$3:L4))+1)),"")
P3:P23	P3	=IF(J3="","", IF(J3=0,D3, ""))
Q3:Q23	Q3	=IF(J3="","", IF(J3=0,E3, ""))
R3:R23	R3	=IF(Q3="","",IF(H3<1,F3,G3-H3))
S3:S23	S3	=S2+(L3<>"")
G4:G23	G4	=IF(E4="","",SUMIF($E$4:E4,E4,$F$4:F4))
H4	H4	{=IF(F4="","",IF(ISERROR(I4-INDEX($G$4:$G$198,MATCH(10,1/($E2:$E$4=E4)))),I4,I4-INDEX($G$4:$G$198,MATCH(10,1/($E2:$E$4=E4)))))}
H5:H23	H5	{=IF(F5="","",IF(ISERROR(I5-INDEX($G$4:$G$198,MATCH(10,1/($E$4:$E4=E5)))),I5,I5-INDEX($G$4:$G$198,MATCH(10,1/($E$4:$E4=E5)))))}
J4:J23	J4	=IF(I4="","", IF(I4<G4,0,IF(I4>G4,2, IF(I4=G4, 1,))))
L4:L23	L4	=IF(A4="","",A4)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J4:J249	Other Type	Icon set		NO

 

[greyland]

Hey I got it figured out! The formula you gave me wasn't working because of a formula in one of the columns but I figured out a way around it! Thank you! I don't think I would have been able to get it without your help!

 

[Peter_SSs]

I hadn't had sufficient time yet to get back and have a good look at this but glad you got it sorted. Thanks for letting us know.