Excel formula Q
 

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=E 3))=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!


--
scoobz
------------------------------------------------------------------------
scoobz's Profile: http://www.excelforum.com/member.php...o&userid=12170
View this thread: http://www.excelforum.com/showthread...hreadid=561063

Excel formula Q
 

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=E 3))=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

Excel formula Q
 

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=E 3))=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

Excel formula Q
 

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


--
scoobz
------------------------------------------------------------------------
scoobz's Profile: http://www.excelforum.com/member.php...o&userid=12170
View this thread: http://www.excelforum.com/showthread...hreadid=561063

Excel formula Q
 

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

Excel formula Q
 

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

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


--
scoobz
------------------------------------------------------------------------
scoobz's Profile: http://www.excelforum.com/member.php...o&userid=12170
View this thread: http://www.excelforum.com/showthread...hreadid=561063

Excel formula Q
 

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

Excel formula Q
 

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 CCustomBegins withEnter whatever letters you
want
--
Regards

Roger Govier


"scoobz" wrote in
message ...

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

Excel formula Q
 

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.


--
scoobz
------------------------------------------------------------------------
scoobz's Profile: http://www.excelforum.com/member.php...o&userid=12170
View this thread: http://www.excelforum.com/showthread...hreadid=561063