quantities of numbers listed in column

[bobmc]

As you can see by the example below, column G indicates different amounts of postage which are pulled from a lookup table. (This column actually extends ad infinitum... it has been cropped for brevity here.) There are 25 different postage amounts listed in the lookup table.

Column J needs to show the different amounts found in column G while column I needs to show how many occurences of each of those amount are found in column G.

I could list all 25 possible amounts in column J, but it would be nicer if column J simply showed the amounts that come up in column G. And wow, it'd be even better (tho not completely necessary) that those amounts in column J were sorted from smallest to largest.

I tried to wrap my little brain around this one and didn't have any luck. If someone could impart upon me the two formulas necessary to make columns I and J work, it'd be mucho appreciated!

Thanks!

bobmc

ZIP ZONES.xls
	A	B	C	D	E	F	G	H	I	J
1
2	Item	Qty	QtyDel	Weight/oz	ZipCode	Zone	Postage		Qty@	Postage
3	DVD	5	6	20	34443	8	5.75		4	3.85
4	VHS	4	5	24	98664	1	3.95		1	3.95
5	DVD	3	4	12	67878	6	3.85		2	5.75
6	DVD	2	2	8	27727	8	3.85		1	7.85
7	VHS	6	8	36	60012	7	7.85
8	DVD	4	5	16	55454	7	3.85
9	DVD	2	2	8	55432	7	3.85
10	DVD	5	6	20	22321	8	5.75
11
Sheet1

 

[Aladin Akyurek]

How did you get 4 for 5.85, given your sample?

 

[PA HS Teacher]

I'm still a little fuzzy on your problem, but here's what I would do:

Create an intermediate table:
On a separate Sheet List all 25 possible items.
Include columns for desired information. (Total Postage? Number of Parcels? etc.) Could use SumIF or SumProduct Functions.

Rank:
Add a column to rank these 25 possible items. (make sure this column is on the far left.) You can use the Rank function to acomplish this.

I recommend something like Rank(DesiredQuantity+rnd()/100000,Range of quantities to be ranked) (the + rnd() prevents duplicate rankings)


Vlookup:
I would then use a Vlookup to pull these values into your final table.
Something like Vlookup(Row(),IntermediateTable,Column())

Perhaps include and If(Iserror(...),"",(...)) so that only items that actually have something will show in the table.

Hope I'm making sense.

 

[Aladin Akyurek]

Disregard the question I posed. I apparently missed that you wanted to count postage occurrences...

Keywords. Extract a unique list. Rank-based. [ COUNTIF, INDEX, ISNUMBER, MATCH, MAX, N, RANK, ROW ]

aaExtractAscendingNumListAndCountIf bobmc.xls
	A	B	C	D	E	F	G	H	I	J	K
1											4
2	Item	Qty	QtyDel	Weight/oz	ZipCode	Zone	Postage	I-Rank	F-Rank	Qty@	Postage
3	DVD	5	6	20	34443			6	3	4	3.85
4	VHS	4	5	24	98664			5	2	1	3.95
5	DVD	3	4	12	67878			1	1	2	5.75
6	DVD	2	2	8	27727					1	7.85
7	VHS	6	8	36	60012			8	4
8	DVD	4	5	16	55454
9	DVD	2	2	8	55432
10	DVD	5	6	20	22321
11
Sheet1

Formulas...

H3, copied down:

=IF(ISNUMBER(MATCH(G3,$G$2:G2,0)),"",RANK(G3,$G$3:$G$10,1)+COUNTIF($G$3:G3,G3)-1)

I3, copied down:

=IF(N(H3),RANK(H3,$H$3:$H$10,1),"")

J3, copied down:

=IF(N(K3),COUNTIF($G$3:$G$10,K3),"")

K1:

=MAX(I:I)

K3, copied down:

=IF(ROW()-ROW(K$3)+1<=$K$1,INDEX(G:G,MATCH(ROW()-ROW(K$3)+1,I:I,0)),"")