Looking for row # for highest value AND blank cell in other column

[Guest]

I'm trying to build a formula across tabs on the same spreadsheet that gives
me the number for row that has the highest balance in column I AND a blank
cell in column A. It is for a work project that involves working through a
list of customer accounts and marking which we have completed.

Right now I have (I am in a tab, and pulling from another tab titled
"Bucket"):

A2: ISBLANK(Bucket!A1)
B2: =DMAX(DataJB,Bucket!I1,A1:A2)
C2: =MATCH(B2,Bucket!I2:I65536,0)

The problem that I am getting is that if there are multiple matching B2
values, it will only match the first B2 value in the Bucket!I column,
disregarding whether or not that row ALSO has a blank cell in the
corresponding row in the Bucket!A column. Please help with any ideas!

 

[SteveG]

Do you mean the highest balance in column I if the corresponding value
in column A is blank? If A5=Blank and I5 is the max balance then
return the value in I5?

Try,

=MAX(IF(Bucket!A2:A65535="",Bucket!I2:I65535,1))

which is an array formula so commit with Ctrl-Shift-Enter.

HTH

Steve

 

[Guest]

Steve,

Thanks for the quick reply and introduction to "array" formulae. However,
your formula doesn't seem to address the probelm I was having when I try it.
Let me try again and explain what I was trying to do:

In a seperate tab, find all the row data for the row that has the highest
value in the I column AND no value in the A column.

So, for the data that belongs in the B column, I came up with this.
However, it continues to result in "Empty". Please let me know if you see
any problems with what I am trying.

=IF(AND(Bucket!A2:A65535="",Bucket!I2:I65535=MAX(IF(Bucket!A2:A65535="",Bucket!I2:I65535,1))),Bucket!B2:B65535,"Empty")

If a certain cell(X) within Bucket's A-column range is blank AND the value
in the corresponding row is equal to the MAX value of that column, then the
corresponding value in Bucket's B column goes into the certain cell(X). If
not, then print "Empty".

Thanks again for your help!

Aaron

 

[SteveG]

Aaron,

Try,

=IF(SUMPRODUCT((Bucket!A1:A65000="")*(Bucket!I1:I65000=MAX(Bucket!I1:I65000))*(Bucket!B1:B65000))>0,SUMPRODUCT((Bucket!A1:A65000="")*(Bucket!I1:I65000=MAX(Bucket!I1:I65000))*(Bucket!B1:B65000)),"Empty")

This is not an array formula. All of the ranges must be the same size
for this to work. Also, if there is more than one occurrence where A
is blank and I is the max value of the column it will sum the values in
B.

Does that help?

Steve

 

[Guest]

Steve,

That formula gave me a #VALUE! error, perhaps because I am dealing with
strings as well as numbers.

The real difficulty has been to specify the row that has BOTH a blank in A
and the highest balance in I. I figured out a way to do that - to find the
rows with blanks, and search for the highest balance - BUT if there are
multiple rows with the SAME high balance, then it will give the first row in
descending order with that balance REGARDLESS of whether or not A is blank.

I can't ensure that the columns will be sorted, which is another
restriction. Do you know of a way to ensure that the tables get sorted a
specific way everytime the worksheet is opened?

Aaron