INDEX(LARGE( across worksheets

[Fin Fang Foom]

I would like to get this formula below to work across worksheets.


=INDEX(A$1:A$99,MATCH(LARGE(IF(RIGHT($A$2:$A$100,5)="Total",IF(LEFT($A
$2:$A$100,5)<>"Grand",$B$2:$B$100-ROW($B$2:$B$100)/10^5)),ROWS(D
$22)),$B$2:$B$100-ROW($B$2:$B$100)/10^5,0))


The formula above list names from column A that has the largest number
in column B. It also accounts for duplicates.

Any Ideas will be appreciated.

 

[Fin Fang Foom]

I would like to get this formula below to work across worksheets.

=INDEX(A$1:A$99,MATCH(LARGE(IF(RIGHT($A$2:$A$100,5)="Total",IF(LEFT($A
$2:$A$100,5)<>"Grand",$B$2:$B$100-ROW($B$2:$B$100)/10^5)),ROWS(D
$22)),$B$2:$B$100-ROW($B$2:$B$100)/10^5,0))

The formula above list names from column A that has the largest number
in column B. It also accounts for duplicates.

Any Ideas will be appreciated.

Bump!

 

[Herbert Seidenberg]

The method, functionality and syntax is correct.
Use Tools > Formula Auditing > Evaluate Formula
to find the simple error.

 

[Fin Fang Foom]

The method, functionality and syntax is correct.
Use Tools > Formula Auditing > Evaluate Formula
to find the simple error.

HI Herbert Seidenberg,


I don't think nonthing is wrong with the formula. I would like to do
is to have this formula to look through every worksheet in the
workbook.

 

[Domenic]

Use the same define names provided by Harlan, and add the following
defined names...

Col_A_N:

=N(INDIRECT("'"&XWSLST&"'!A"&(2+MOD(S,N))))

Col_A_T:

=T(INDIRECT("'"&XWSLST&"'!A"&(2+MOD(S,N))))

Then try the following formula...

=INDEX(Col_A_N,MATCH(LARGE(IF(RIGHT(Col_A_T,5)="Total",IF(LEFT(Col_A_T,5)
<>"Grand",Col_B-S/10^5)),ROWS(D$22)),Col_B-S/10^5,0)-1)

Note that I've assumed the value to return is a number, as per your
sample data in a previous thread.

Hope this helps!

 

[Fin Fang Foom]

Use the same define names provided by Harlan, and add the following
defined names...

Col_A_N:

=N(INDIRECT("'"&XWSLST&"'!A"&(2+MOD(S,N))))

Col_A_T:

=T(INDIRECT("'"&XWSLST&"'!A"&(2+MOD(S,N))))

Then try the following formula...

=INDEX(Col_A_N,MATCH(LARGE(IF(RIGHT(Col_A_T,5)="Total",IF(LEFT(Col_A_T,5)
<>"Grand",Col_B-S/10^5)),ROWS(D$22)),Col_B-S/10^5,0)-1)

Note that I've assumed the value to return is a number, as per your
sample data in a previous thread.

Hope this helps!




- Show quoted text -

Hi Domenic, ThankYou! very much.

Thank You for repyling. I was really closes on my end I was still
using the ROW Function but in your formula you didn't, that was the
key. Also I had to take out the -1 in the formula to get the correct
results. The value I'm returning is text.

Another question, I dont know this is possible.

When using the formula it returns the text value that has the highest
qty. But if the text value say part# 20-T it will come out maybe 3
times because that part number is in multiple worksheets. Is there a
way that the formula do a SUMIF(LARGE( and return the text value just
once?

Let me know I made it clear.

 

[Domenic]

Try...

=INDEX(Col_A,MATCH(LARGE(IF(RIGHT(Col_A,5)="Total",IF(LEFT(Col_A,5)<>"Gra
nd",IF(MATCH(Col_A,Col_A,0)=S+1,Col_B-S/10^5))),ROWS(D$22)),Col_B-S/10^
5,0))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Fin Fang Foom]

Try...

=INDEX(Col_A,MATCH(LARGE(IF(RIGHT(Col_A,5)="Total",IF(LEFT(Col_A,5)<>"Gra
nd",IF(MATCH(Col_A,Col_A,0)=S+1,Col_B-S/10^5))),ROWS(D$22)),Col_B-S/10^
5,0))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Hi Domenic sorry for the confusion.

The formula I'm after is to look through all the worksheets find all
the matchings in column A, then sums up all the matchings and the
formula should retrieve the highest matching first then second and so
on. Here is a formula below that works on one worksheet. But trying to
have it work on multiple worksheets might not be possible what do you
think?


=INDEX($A$2:$A$400,LARGE(IF($A$1:$A$399<>$A$2:$A$400,IF(RIGHT($A$2:$A
$400,5)<>"Total",
IF(LEFT($A$2:$A$400,5)<>"Grand",SUMIF($A$2:$A$400,$A$2:$A$400,$B$2:$B
$400)))),ROWS(D$22)))

 

[Fin Fang Foom]

Try...

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Hi Domenic sorry for the confusion.

The formula I'm after is to look through all the worksheets find all
the matchings in column A, then sums up all the matchings and the
formula should retrieve the highest matching first then second and so
on. Here is a formula below that works on one worksheet. But trying to
have it work on multiple worksheets might not be possible what do you
think?

=INDEX($A$2:$A$400,LARGE(IF($A$1:$A$399<>$A$2:$A$400,IF(RIGHT($A$2:$A
$400,5)<>"Total",
IF(LEFT($A$2:$A$400,5)<>"Grand",SUMIF($A$2:$A$400,$A$2:$A$400,$B$2:$B
$400)))),ROWS(D$22)))- Hide quoted text -

- Show quoted text -

Bump!

 

[Domenic]

Try the following formulas, which need to be confirmed with
CONTROL+SHIFT+ENTER...

D2, copied down:

=LARGE(IF(RIGHT(Col_A,5)="Total",IF(LEFT(Col_A,5)<>"Grand",IF(MATCH(Col_A
,Col_A,0)=S+1,Col_B))),ROWS($D$22))

E2, copied down:

=INDEX(Col_A,SMALL(IF(RIGHT(Col_A,5)="Total",IF(LEFT(Col_A,5)<>"Grand",IF
(MATCH(Col_A,Col_A,0)=S+1,IF(Col_B=D2,S+1)))),COUNTIF($D$22,D2)))

Hope this helps!

 

[Fin Fang Foom]

Try the following formulas, which need to be confirmed with
CONTROL+SHIFT+ENTER...

D2, copied down:

=LARGE(IF(RIGHT(Col_A,5)="Total",IF(LEFT(Col_A,5)<>"Grand",IF(MATCH(Col_A
,Col_A,0)=S+1,Col_B))),ROWS($D$22))

E2, copied down:

=INDEX(Col_A,SMALL(IF(RIGHT(Col_A,5)="Total",IF(LEFT(Col_A,5)<>"Grand",IF
(MATCH(Col_A,Col_A,0)=S+1,IF(Col_B=D2,S+1)))),COUNTIF($D$22,D2)))

Hope this helps!

Thank You for giving this formula this help greatly.


=LARGE(IF(MATCH(Col_A,Col_A,0)=S+1,MMULT((Col_A=TRANSPOSE(Col_A))
+0,Col_B)),ROWS($I$2:I2))

Thank You Domenic!