Lookup a column of values with the largest sum

Pille

New Member
Joined
Jun 6, 2017
Messages
15
Hi there,
Hope this post finds you well.
I need to lookup cells from one of four columns, and place them in a new column. The lookup columns are p/l columns and I'm looking for the column with the max sum. So if column b has the max sum I want all the cells from that column to show up in column x.

Kind Regards

Per
 
Ok checked and it works making a table with the above #3 including the empty cells.
I'm using the query editor to import my data. I can manually add a row to that table, but when I hit refresh the new last row will be deleted.

/Per

Let's separate data (input) and processing (output)...

data (name of the data sheet)

Row\Col
A​
B​
C​
D​
1​
X1X2X3X4
2​
1
16
4
9
3​
8
3
2
1
4​
11
5
9
6
5​

processing (the name of the processing sheet)

Row\Col
A​
1​
2​
2​
X2
3​
16​
4​
3​
5​
5​
6​

In A1 of processing control+shift+enter, not just enter:

=MATCH(MAX(MMULT(TRANSPOSE(data!A2:D4),ROW(data!A2:D4)^0)),MMULT(TRANSPOSE(data!A2:D4),ROW(data!A2:D4)^0),0)

In A2 of processing just enter:

=INDEX(data!A1:D1,A1)

In A3 of processing just enter and copy down:

=IFERROR(INDEX(data!$A$2:$D$4,ROWS($A$3:A3),$A$1),"")

If you get the problem when you do a new import, we can implement dynamic named ranges in order not to miss any change to the data area in the data sheet if you like...
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Thanks,
I guess it is a problem with formulas when you update the input data. The input range is not static. So when I import a data stream with more rows than before the formula columns does not print on the extra rows. Hmmm..
 
Upvote 0
Thanks,
I guess it is a problem with formulas when you update the input data. The input range is not static. So when I import a data stream with more rows than before the formula columns does not print on the extra rows. Hmmm..

The formulas process the ranges they are fed with. So the ranges must be up to date, hence dynamic named ranges.

What is the current range that the data occupies?
 
Upvote 0
Ok I get it. My ranges are from 500 to maybe 20 000 rows. Source is csv through query, works fine.
The one I build with is 1500 rows.
Kind Regards
Per
 
Upvote 0
Column B and C is text. But I can live without them if necessary. They only tell me what I'm working with. They are all equally filled, always.

/Per
 
Upvote 0
Column B and C is text. But I can live without them if necessary. They only tell me what I'm working with. They are all equally filled, always.

/Per

That's great. Is A, which is numeric, relevant for the processing? If relevant, are you able to have the text columns in A and B (instead of B and C) so that we only have numeric data area in C:I?
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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