Pull Index/Match/offset from a Range data then Sort Numbers with Points

AndyJR

Board Regular
Joined
Jun 20, 2015
Messages
90
Hi,

I'm tryng to Index Match from a Dynamic Range of 6 pairs of columns, The column work as:
Col A= Numbers, Col B=Values
Col C= Numbers, Col D=Values
Col E= Numbers, Col F=Values
Col G= Numbers, Col H=Values
Col I= Numbers, Col J=Values
Col K= Numbers, Col L=Values

Notes:
-.Numbers on each columns are from 1 to 100
-.Values are between 0 to 45
-.Each column are using 100 rows plus 2 rows (heading)

__A___B___C___D___E___F___G___H___I____J___K___L
NumPONumPONumPONumPONumPONumPO
343433733733703103441
26240370370370190334
20201861861860893025
10193063063062582824
24182152152150962416
13162552552553361114
04161541541541560711
02143143141240662011
0110124124314275176

<tbody>
</tbody>

What I need is to pull the Numbers from Range A3:K6 (Columns A,C,E,G,I,K)
And sum each point belong to each Number,
Number sorted by most values to least (left to right)

Example of desired Otput of numbers sorted by values

Numbers343302628332010181825
Values sumed756543242421201918998

<tbody>
</tbody>


Thank you !!!!!:eek:
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
I can get you most of the way there, but the POs are in order left to right instead of the sums. I had to input a blank column in at the front to accommodate a simple shift. I'm not sure how you decided what to limit your range to, so you will have to do some manipulation to a named range. Specifically, I used All_Data to encompass all of your data from B3 to M11 (remember shifted 1 column to start).

First was to pull a unique list of POs.
C15 =SUMPRODUCT(LARGE(((MOD(COLUMN(All_Data),2)=1)+0)*All_Data,COLUMN()-2)), array-entered *Notes: MOD helped me separate Num or PO values, Large pulled the overall largest PO, and COLUMN()-2 was to create a counter that started at 1 since my formula was in column C
D15 =SUMPRODUCT(LARGE(((MOD(COLUMN(All_Data),2)=1)+0)*All_Data*(All_Data[less than]C15),1))<c15),1)), array="" entered="" *using="" <c15="" 'removes'="" duplicates,="" drag="" right="" for="" however="" many="" unique="" pos="" you="" can="" have,="" in="" this="" case="" 17
*Notes: [less than] means the symbol, reply script doesn't like it, used to 'exclude' duplicates, drag right to account for unique POs (17 for this dataset)

<c15),1))
Sum matching POs.
C16 =SUMPRODUCT(((MOD(COLUMN(All_Data),2)=1)+0)*OFFSET(All_Data,0,-1,ROWS(All_Data),COLUMNS(All_Data)),(All_Data=C15)+0), array-entered then dragged over underneath each unique PO

Edited since it didn't like less than symbol</c15),1))
</c15),1)),>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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
Back
Top