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

[AndyJR]

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
[TABLE="width: 358"]
<tbody>[TR]
[TD]Num[/TD]
[TD]PO[/TD]
[TD]Num[/TD]
[TD]PO[/TD]
[TD]Num[/TD]
[TD]PO[/TD]
[TD]Num[/TD]
[TD]PO[/TD]
[TD]Num[/TD]
[TD]PO[/TD]
[TD]Num[/TD]
[TD]PO[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]34[/TD]
[TD]34[/TD]
[TD]33[/TD]
[TD]7[/TD]
[TD]33[/TD]
[TD]7[/TD]
[TD]33[/TD]
[TD]7[/TD]
[TD]03[/TD]
[TD]10[/TD]
[TD]34[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]24[/TD]
[TD]03[/TD]
[TD]7[/TD]
[TD]03[/TD]
[TD]7[/TD]
[TD]03[/TD]
[TD]7[/TD]
[TD]01[/TD]
[TD]9[/TD]
[TD]03[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]20[/TD]
[TD]18[/TD]
[TD]6[/TD]
[TD]18[/TD]
[TD]6[/TD]
[TD]18[/TD]
[TD]6[/TD]
[TD]08[/TD]
[TD]9[/TD]
[TD]30[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]19[/TD]
[TD]30[/TD]
[TD]6[/TD]
[TD]30[/TD]
[TD]6[/TD]
[TD]30[/TD]
[TD]6[/TD]
[TD]25[/TD]
[TD]8[/TD]
[TD]28[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]18[/TD]
[TD]21[/TD]
[TD]5[/TD]
[TD]21[/TD]
[TD]5[/TD]
[TD]21[/TD]
[TD]5[/TD]
[TD]09[/TD]
[TD]6[/TD]
[TD]24[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]16[/TD]
[TD]25[/TD]
[TD]5[/TD]
[TD]25[/TD]
[TD]5[/TD]
[TD]25[/TD]
[TD]5[/TD]
[TD]33[/TD]
[TD]6[/TD]
[TD]11[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]04[/TD]
[TD]16[/TD]
[TD]15[/TD]
[TD]4[/TD]
[TD]15[/TD]
[TD]4[/TD]
[TD]15[/TD]
[TD]4[/TD]
[TD]15[/TD]
[TD]6[/TD]
[TD]07[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]14[/TD]
[TD]31[/TD]
[TD]4[/TD]
[TD]31[/TD]
[TD]4[/TD]
[TD]12[/TD]
[TD]4[/TD]
[TD]06[/TD]
[TD]6[/TD]
[TD]20[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]10[/TD]
[TD]12[/TD]
[TD]4[/TD]
[TD]12[/TD]
[TD]4[/TD]
[TD]31[/TD]
[TD]4[/TD]
[TD]27[/TD]
[TD]5[/TD]
[TD]17[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 374"]
<tbody>[TR]
[TD]Numbers[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]Values sumed[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]


Thank you !!!!!

 

[C Moore]

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)),>