quantities of numbers listed in column

bobmc

Board Regular
Joined
Mar 13, 2002
Messages
142
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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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)),"")
 

Forum statistics

Threads
1,147,510
Messages
5,741,574
Members
423,668
Latest member
Audorin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top