Produce a new list based on values in a range

[bj6264]

Hello,

I have a very long list of products and values and I need to produce another list that list products by a range of quantity. My list looks similar to this:-

[TABLE="width: 158"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Potatoes[/TD]
[TD="align: right"]199[/TD]
[/TR]
[TR]
[TD]Peas[/TD]
[TD="align: right"]152[/TD]
[/TR]
[TR]
[TD]Carrots[/TD]
[TD="align: right"]1901[/TD]
[/TR]
[TR]
[TD]Cabbage[/TD]
[TD="align: right"]576[/TD]
[/TR]
[TR]
[TD]Turnip[/TD]
[TD="align: right"]800[/TD]
[/TR]
[TR]
[TD]Cucumber[/TD]
[TD="align: right"]100
[/TD]
[/TR]
</tbody>[/TABLE]

I then need to produce a list that has headers of "less than 100", "101 to 200" etc and under each header the product would appear

[TABLE="width: 336"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Less than 100[/TD]
[TD]100-199[/TD]
[TD]200-299[/TD]
[TD]500 - 599[/TD]
[TD]etc[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Cucumber[/TD]
[TD][/TD]
[TD]Cabbage[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Peas[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Can I use a formula to do this? Thought of nested ifs but as my values go up to about 3000 not sure if this would fit. Think offset may work but not sure of how.

Hope this makes sense!

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G	H	I	J	K
1						Less than 100	100-199	200-299	300-399	400-499	500-599
2	Potatoes	199					Potatoes				Cabbage
3	Peas	152					Peas
4	Carrots	1901
5	Cabbage	576
6	Turnip	800
7	Cucumber	100
Lists

Cell Formulas
Range	Formula
F2	=IFERROR(INDEX($A$2:$A$100,AGGREGATE(15,6,(ROW($A$2:$A$100)-ROW($A$2)+1)/(($B$2:$B$100A1)*100)*($B$2:$B$100>=COLUMN(A1)*100-99)),ROWS(F$2:F2))),"")

Fill formula down & across

 

[bj6264]

How about

	A	B	C	D	E	F	G	H	I	J	K
Less than 100	100-199	200-299	300-399	400-499	500-599
Potatoes		Potatoes				Cabbage
Peas		Peas
Carrots
Cabbage
Turnip
Cucumber

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]199[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]152[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1901[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]576[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]800[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Lists​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IFERROR(INDEX($A$2:$A$100,AGGREGATE(15,6,(ROW($A$2:$A$100)-ROW($A$2)+1)/(($B$2:$B$100<COLUMN(A1)*100)*($B$2:$B$100>=COLUMN(A1)*100-99)),ROWS(F$2:F2))),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Fill formula down & across

Thank you Fluff you're a genius! You've helped me again. Many, many thanks. Bj

 

[Fluff]

You're wlecome & thanks for the feedback