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

EDIT: In additional to #1 - is it possible to have my criteria match on a partial value? Example: ItemType1 data could contain, "Telescopic" "Televisions", "Telescope" and I would like to be able to match it using the equivalent of a "begins with" Tele.

[Franz Verga]

scoobz wrote:
> 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]

scoobz wrote:
> 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 not have enough knowledge to string together a coherrent formula.

I was hoping someone could help with a specific formula.

Thanx

[Franz Verga]

scoobz wrote:
> 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 is clear!

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

[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
--
Regards

Roger Govier


"scoobz" <[email protected]> wrote in
message news:[email protected]...
>
> can anyone still help with this?
>
>
> --
> scoobz
> ------------------------------------------------------------------------
> scoobz's Profile:
> http://www.excelforum.com/member.php...o&userid=12170
> View this thread:
> http://www.excelforum.com/showthread...hreadid=561063
>

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