Thanks:  0
Likes:  0

# Thread: Lookup a column of values with the largest sum

1. ## Re: Lookup a column of values with the largest sum

Originally Posted by Pille
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 X1 X2 X3 X4 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...

2. ## Re: Lookup a column of values with the largest sum

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..

3. ## Re: Lookup a column of values with the largest sum

Originally Posted by Pille
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?

4. ## Re: Lookup a column of values with the largest sum

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

5. ## Re: Lookup a column of values with the largest sum

Originally Posted by Pille
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
I'm asking for the exact current range...

6. ## Re: Lookup a column of values with the largest sum

Ok,
It's A2:I1098
/Per

7. ## Re: Lookup a column of values with the largest sum

Originally Posted by Pille
Ok,
It's A2:I1098
/Per
Are the particular ranges in the columns A:I always equally filled in with numeric data?

8. ## Re: Lookup a column of values with the largest sum

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

9. ## Re: Lookup a column of values with the largest sum

Originally Posted by Pille
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?

10. ## Re: Lookup a column of values with the largest sum

No, A is just counting rows.
/Per

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•