# quantities of numbers listed in column

#### bobmc

##### Board Regular
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
ABCDEFGHIJ
1
2ItemQtyQtyDelWeight/ozZipCodeZonePostageQty@Postage
3DVD56203444385.7543.85
4VHS45249866413.9513.95
5DVD34126787863.8525.75
6DVD2282772783.8517.85
7VHS68366001277.85
8DVD45165545473.85
9DVD2285543273.85
10DVD56202232185.75
11
Sheet1

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
How did you get 4 for 5.85, given your sample?

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.

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
ABCDEFGHIJK
14
2ItemQtyQtyDelWeight/ozZipCodeZonePostageI-RankF-RankQty@Postage
3DVD5620344436343.85
4VHS4524986645213.95
5DVD3412678781125.75
6DVD22827727  17.85
7VHS68366001284
8DVD451655454
9DVD22855432
10DVD562022321
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)),"")

Replies
1
Views
426
Replies
2
Views
674
Replies
21
Views
1K
Replies
6
Views
213
Replies
6
Views
568

1,218,539
Messages
6,143,092
Members
450,461
Latest member
Bosavon

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back