Excel formula Q

[scoobz]

OK here goes:

In a data sheet (sheet 1) I have the following columns:

A: Qty
B: ItemType1
C: ItemType2
D: ItemType3

My end objective is to have a formula that will lookup the highes
quantity based on a given criteria of Item Types. (It should also allo
me to select the no2 position, no3 position etc...)

Through searching on line, I have managed to piece together the belo
array formula, which gives me most things, but I would like to add
coule more.

Formula is located on Sheet2 for example:

=IF(SUM(('Sheet1'!B2:B300=D3)*('Sheet1'!C2:C300>=E3))>=1,LARGE(IF('Sheet1'!B2:B300=D3,IF('Sheet1'!C2:C300>=D3,'Sheet1'!A2:A300)),1),"")


Additions on the above array formula:

1. To include a third criteria (as the above only allows two to b
defined: in cells D3 and E3).

2. To include a flexible amount of rows to be included in the range. A
the rows of data will change, I need something that can compensate fo
this. (I used a countif: =COUNTIF(B:B,"<>" to retrieve the number o
rows with data, but I'm not sure how I can put this in the formula. I
take the total column (A:A) than I get an error in the formula as th
array has empty cells.

3 To have a seperate lookup formula, that I can use like a 'vlookup
on the row in which the qty is matched. Example: The 2nd highest qt
matched is 500, so I would like it to look up across this and provid
me with the ItemTypes that it was matched to.

Any help is much appreciated

 

[Franz Verga]

OK here goes:

In a data sheet (sheet 1) I have the following columns:

A: Qty
B: ItemType1
C: ItemType2
D: ItemType3

My end objective is to have a formula that will lookup the highest
quantity based on a given criteria of Item Types. (It should also
allow me to select the no2 position, no3 position etc...)

Through searching on line, I have managed to piece together the below
array formula, which gives me most things, but I would like to add a
coule more.

Formula is located on Sheet2 for example:

=IF(SUM(('Sheet1'!B2:B300=D3)*('Sheet1'!C2:C300>=E3))>=1,LARGE(IF('Sheet1'!B2:B300=D3,IF('Sheet1'!C2:C300>=D3,'Sheet1'!A2:A300)),1),"")


Additions on the above array formula:

1. To include a third criteria (as the above only allows two to be
defined: in cells D3 and E3).

2. To include a flexible amount of rows to be included in the range.
As the rows of data will change, I need something that can compensate
for this. (I used a countif: =COUNTIF(B:B,"<>" to retrieve the number
of rows with data, but I'm not sure how I can put this in the
formula. If take the total column (A:A) than I get an error in the
formula as the array has empty cells.

3 To have a seperate lookup formula, that I can use like a 'vlookup'
on the row in which the qty is matched. Example: The 2nd highest qty
matched is 500, so I would like it to look up across this and provide
me with the ItemTypes that it was matched to.

Any help is much appreciated!

I think you could use a SUMPRODUCT formula.

You would find very interesting this page from Bob Phillips site:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html


If you need more help, it could be better if you could upload an example
file to www.savefile.com

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Franz Verga]

OK here goes:

In a data sheet (sheet 1) I have the following columns:

A: Qty
B: ItemType1
C: ItemType2
D: ItemType3

My end objective is to have a formula that will lookup the highest
quantity based on a given criteria of Item Types. (It should also
allow me to select the no2 position, no3 position etc...)

Through searching on line, I have managed to piece together the below
array formula, which gives me most things, but I would like to add a
coule more.

Formula is located on Sheet2 for example:

=IF(SUM(('Sheet1'!B2:B300=D3)*('Sheet1'!C2:C300>=E3))>=1,LARGE(IF('Sheet1'!B2:B300=D3,IF('Sheet1'!C2:C300>=D3,'Sheet1'!A2:A300)),1),"")


Additions on the above array formula:

1. To include a third criteria (as the above only allows two to be
defined: in cells D3 and E3).

2. To include a flexible amount of rows to be included in the range.
As the rows of data will change, I need something that can compensate
for this. (I used a countif: =COUNTIF(B:B,"<>" to retrieve the number
of rows with data, but I'm not sure how I can put this in the
formula. If take the total column (A:A) than I get an error in the
formula as the array has empty cells.

3 To have a seperate lookup formula, that I can use like a 'vlookup'
on the row in which the qty is matched. Example: The 2nd highest qty
matched is 500, so I would like it to look up across this and provide
me with the ItemTypes that it was matched to.

Any help is much appreciated!

For Dynamic Ranges, you can see this page from Chip Pearson's site:

http://www.cpearson.com/excel/excelF.htm#DynamicRanges

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[scoobz]

Sorry - I've tried looking through this information already, and do no
have enough knowledge to string together a coherrent formula.

I was hoping someone could help with a specific formula.

Than

 

[Franz Verga]

Sorry - I've tried looking through this information already, and do
not have enough knowledge to string together a coherrent formula.

I was hoping someone could help with a specific formula.

Thanx

Maybe if you could upload an example file to www.savefile.com, it would be
simpler to produce a specific formula...


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[scoobz]

Thanks for your time! I've upload an example/request which I hope i
clear!

http://www.savefile.com/files/878018

 

[scoobz]

can anyone still help with this?

 

[Roger Govier]

Hi
Why not just apply Autofilter to your data in Sheet 1
Use dropdown on column A and select HY1
Use dropdown on column D and select A1
Use dropdown on Column C>Custom>Begins with>Enter whatever letters you
want

 

[scoobz]

Thanks for your reply.

The sheet needs to be flexible. Basically, I would like to be able to
change the criteria values, and the formulas automatically retrieve the
new data based on that information.