Multiples Index/Match/Multiples Columns But to be concatenate in respective Rows

Hi Jindon,

The logic is to sum the value Columns to each Number Column that belong to it.
For example:
Number 98
have 35 Points (located in J3) so, #98 should be 1st Left

Number 120
Have 23 points (points located on B7, D3, F8, J8, L3)-> 4+7+4+4+4 =23)

Number 97
Have 22 points (points located on J4)

Number 101
Have 18 points (points located on B8, F5, H8, I7)->4+6+4+4 =18

So The list of Numbers with their points should be like :

Numbers -->98__120__97__101
Points -----> 35__23___22__18

I think is Index match but is using multiples columns..

Thanks Jindon

See the formula in my previous post...

I originally thought you only need up to 5 colums(A - J)

Seems you want all in the table
VBA change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Hi Jindon.

Yes i tried the code, the points is not indexed

And the Output is different, This is what i got:
Value 100 120 110 99 98 115 106 102 97 105 90 101 92 93 116
Points 10 19 11 8 35 10 6 4 22 4 13 14 9 12 9

It should be like :
Value 98 120 97 101 90 93 100 116 110 115 92 99 106 102 105
Points 35 23 22 18 17 16 14 13 10 10 9 8 6 4 4

Thanks

Did you try the formula?

Or simply a formula in O8

=SUMIF($A$3:$K$8,O7,$B$3:$L$8)

FDibbins

To be honest since i start to learn excel i read (quite few times) that index match is better than vlookup, So my ignorance guide me avoid it... (just kidding)
I'll start to read about Vlookup, Hlookup..

Thanks!!

Hi Jindon

Yes i tried
the formula index the Values, no the numbers, but now is easy to figure it out

Thanks !!!!

Try:

=SUMPRODUCT(($A$3:$K$8=O7)*MOD(COLUMN($A$3:$K$8),2),$B$3:$L$8)

If B3 is 100 then the result is 108

You are welcome and thanks for the rep.

As I mentioned, if vba this should create the result.

Please Login or Register  to view this content.

And your result is missing "98"...

Jindon.

The 98 is the first one left with 35 points

Am I missing something?

hi Phuocam

I dont understand why,

If B3 is 100 then the result is 108

Thanks

Jindon,

Yes, is missing the Number indexed with the values summed
The code display
Value 1,2,3,4,5__100__120__110__99__98__115__106__102__97__105__90__101__92__93__116
Points 1,2,3,4,5__14___23___11___8___35__10___6____4____22__4____17__18___9___16__13

But Indexed is supposed to look like :
Value 1,2,3,4,5__98__120__97__101__90__93__100__116__110__115__92__99__106__102__105
Points 1,2,3,4,5__35__23___22__18___17__16__14___13___10___10___9___8___6____4____4

Are you talking about the order?

Please Login or Register  to view this content.

Jindon

Yes!, Order
The number with more points going from lef to right (the right are the numbers with least points)
Its like Sort Numbers with most points.

I'm sorry my mistake, i don't know how to say it correct..

Your PM

The problem is that the code is not sorting the points, You added the sort Line
but not work,

Working for me...

Hi Jindon,

Is working on the sample file, wow... I just replaced the numbers and points

(i may did something wrong..)


Thank you Jindon

Hi Jindon,

As i mention.

I'm using the code in a WB with 2 sheet, Both sheet have the same data,

In One sheet display correct Output
(Values at Top, Points sorted at botton of each Value)

On the 2nd sheet
Values are at bottom, Points at top and is not sorted (order)


Thanks

OK,

Can you change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.