Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Formula
I need some help and haven't had luck searching for it on here. I have a
large spreadsheet that has location info in column A. The rest of the columns contain the pricing for each location by supplier. My table looks like this: Sup 1 Sup 2 Sup 3 PIT 5.50 6.00 6.00 LAX 5.50 5.50 4.50 ORD 6.00 5.25 5.50 In Column E, I'd like the max value, and in Column F, the Supplier Name(s) with the max value for each location. I'm good with Column E (=MAX(B2:D2)), and getting "6", but I'm stuck on Column F. I found this formula: =INDEX($B$1:$D$1,MATCH(MAX($B2:$D2),$B2:$D2,0)), but it's only returning "Sup 2" where I'd like to see both Sup 2 & Sup 3. I have no problems listing each supplier in a different column, but when I drag it over to Column G, I still only get Sup 2. All help is greatly appreciated!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Formula
Hi,
In column F enter INDEX($B$1:$D$1,MATCH(E2,$B1:$D1)) "Cow Girl" wrote: I need some help and haven't had luck searching for it on here. I have a large spreadsheet that has location info in column A. The rest of the columns contain the pricing for each location by supplier. My table looks like this: Sup 1 Sup 2 Sup 3 PIT 5.50 6.00 6.00 LAX 5.50 5.50 4.50 ORD 6.00 5.25 5.50 In Column E, I'd like the max value, and in Column F, the Supplier Name(s) with the max value for each location. I'm good with Column E (=MAX(B2:D2)), and getting "6", but I'm stuck on Column F. I found this formula: =INDEX($B$1:$D$1,MATCH(MAX($B2:$D2),$B2:$D2,0)), but it's only returning "Sup 2" where I'd like to see both Sup 2 & Sup 3. I have no problems listing each supplier in a different column, but when I drag it over to Column G, I still only get Sup 2. All help is greatly appreciated!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Formula
=IF(B2=E2,$B$1&" ","")&IF(C2=E2,$C$1&" ","")&IF(D2=E2,$D$1,"")
best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Cow Girl" wrote in message ... I need some help and haven't had luck searching for it on here. I have a large spreadsheet that has location info in column A. The rest of the columns contain the pricing for each location by supplier. My table looks like this: Sup 1 Sup 2 Sup 3 PIT 5.50 6.00 6.00 LAX 5.50 5.50 4.50 ORD 6.00 5.25 5.50 In Column E, I'd like the max value, and in Column F, the Supplier Name(s) with the max value for each location. I'm good with Column E (=MAX(B2:D2)), and getting "6", but I'm stuck on Column F. I found this formula: =INDEX($B$1:$D$1,MATCH(MAX($B2:$D2),$B2:$D2,0)), but it's only returning "Sup 2" where I'd like to see both Sup 2 & Sup 3. I have no problems listing each supplier in a different column, but when I drag it over to Column G, I still only get Sup 2. All help is greatly appreciated!!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Formula
Eduardo - I could not get your formula to work. I'm not sure what I'm doing
wrong. Bernard - Your formula worked, although I failed to mention that my spreadsheet actually has about 50 suppliers, so I'm not sure if I could expand your IF statement out that far. I know that was my bad. Sorry. Any new ideas? Thanks, Cathy "Cow Girl" wrote: I need some help and haven't had luck searching for it on here. I have a large spreadsheet that has location info in column A. The rest of the columns contain the pricing for each location by supplier. My table looks like this: Sup 1 Sup 2 Sup 3 PIT 5.50 6.00 6.00 LAX 5.50 5.50 4.50 ORD 6.00 5.25 5.50 In Column E, I'd like the max value, and in Column F, the Supplier Name(s) with the max value for each location. I'm good with Column E (=MAX(B2:D2)), and getting "6", but I'm stuck on Column F. I found this formula: =INDEX($B$1:$D$1,MATCH(MAX($B2:$D2),$B2:$D2,0)), but it's only returning "Sup 2" where I'd like to see both Sup 2 & Sup 3. I have no problems listing each supplier in a different column, but when I drag it over to Column G, I still only get Sup 2. All help is greatly appreciated!!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Formula
Hi,
I need a little more information Your max # will be in column E What is in column A, (Pit, LAX etc) Are your suppliers one per column ?, please indicate columns "Cow Girl" wrote: Eduardo - I could not get your formula to work. I'm not sure what I'm doing wrong. Bernard - Your formula worked, although I failed to mention that my spreadsheet actually has about 50 suppliers, so I'm not sure if I could expand your IF statement out that far. I know that was my bad. Sorry. Any new ideas? Thanks, Cathy "Cow Girl" wrote: I need some help and haven't had luck searching for it on here. I have a large spreadsheet that has location info in column A. The rest of the columns contain the pricing for each location by supplier. My table looks like this: Sup 1 Sup 2 Sup 3 PIT 5.50 6.00 6.00 LAX 5.50 5.50 4.50 ORD 6.00 5.25 5.50 In Column E, I'd like the max value, and in Column F, the Supplier Name(s) with the max value for each location. I'm good with Column E (=MAX(B2:D2)), and getting "6", but I'm stuck on Column F. I found this formula: =INDEX($B$1:$D$1,MATCH(MAX($B2:$D2),$B2:$D2,0)), but it's only returning "Sup 2" where I'd like to see both Sup 2 & Sup 3. I have no problems listing each supplier in a different column, but when I drag it over to Column G, I still only get Sup 2. All help is greatly appreciated!!! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Formula
The spreadsheet is actually bigger than what I indicated. I scoped it down to
try to simplify, as I figured I could adjust the column references as needed. My max will actually be in column BI. Columns A-J are the details of each location (city, state, zip, etc.). The Suppliers (1 per column) are in columns K-BH. The maximum number of suppliers that have the same price is 4. (I did a countif statement in another column to determine this.) "Eduardo" wrote: Hi, I need a little more information Your max # will be in column E What is in column A, (Pit, LAX etc) Are your suppliers one per column ?, please indicate columns "Cow Girl" wrote: Eduardo - I could not get your formula to work. I'm not sure what I'm doing wrong. Bernard - Your formula worked, although I failed to mention that my spreadsheet actually has about 50 suppliers, so I'm not sure if I could expand your IF statement out that far. I know that was my bad. Sorry. Any new ideas? Thanks, Cathy "Cow Girl" wrote: I need some help and haven't had luck searching for it on here. I have a large spreadsheet that has location info in column A. The rest of the columns contain the pricing for each location by supplier. My table looks like this: Sup 1 Sup 2 Sup 3 PIT 5.50 6.00 6.00 LAX 5.50 5.50 4.50 ORD 6.00 5.25 5.50 In Column E, I'd like the max value, and in Column F, the Supplier Name(s) with the max value for each location. I'm good with Column E (=MAX(B2:D2)), and getting "6", but I'm stuck on Column F. I found this formula: =INDEX($B$1:$D$1,MATCH(MAX($B2:$D2),$B2:$D2,0)), but it's only returning "Sup 2" where I'd like to see both Sup 2 & Sup 3. I have no problems listing each supplier in a different column, but when I drag it over to Column G, I still only get Sup 2. All help is greatly appreciated!!! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Formula
Cow Girl;399142 Wrote: I need some help and haven't had luck searching for it on here. I have a large spreadsheet that has location info in column A. The rest of the columns contain the pricing for each location by supplier. My table looks like this: Sup 1 Sup 2 Sup 3 PIT 5.50 6.00 6.00 LAX 5.50 5.50 4.50 ORD 6.00 5.25 5.50 In Column E, I'd like the max value, and in Column F, the Supplier Name(s) with the max value for each location. I'm good with Column E (=MAX(B2:D2)), and getting "6", but I'm stuck on Column F. I found this formula: =INDEX($B$1:$D$1,MATCH(MAX($B2:$D2),$B2:$D2,0)), but it's only returning "Sup 2" where I'd like to see both Sup 2 & Sup 3. I have no problems listing each supplier in a different column, but when I drag it over to Column G, I still only get Sup 2. All help is greatly appreciated!!! Try in F2: Code: -------------------- =IF(COLUMNS($A$1:A$1)COUNTIF($B2:$D2,$E2),"",INDE X($B$1:$D$1,SMALL(IF($B2:$D2=$E2,COLUMN($B2:$D2)-COLUMN($B2)+1),COLUMNS($A$1:A$1)))) -------------------- this assumes data is in B2:D2, Max is in E2 and headers are in B1:D1... adjust ranges to suit.. and then confirm the formula with CTRL+SHIFT+ENTER not just enter... then copy across and down. -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111402 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Formula
Here is a UDF that will do the job.
With the data you have, call it on row 2 with =SupMax(B2:D2) to get "Sup 2 Sup 3" Function SupMax(myrange) suprow = 1 'This is the row with supplier names supcol = myrange(1).Column - 1 maxcost = WorksheetFunction.Max(myrange) For j = 1 To myrange.Count If myrange(j) = maxcost Then TempSup = TempSup & Cells(suprow, j + supcol) & " " End If Next j SupMax = TempSup End Function New to VBA? Then try these sites David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Cow Girl" wrote in message ... Eduardo - I could not get your formula to work. I'm not sure what I'm doing wrong. Bernard - Your formula worked, although I failed to mention that my spreadsheet actually has about 50 suppliers, so I'm not sure if I could expand your IF statement out that far. I know that was my bad. Sorry. Any new ideas? Thanks, Cathy "Cow Girl" wrote: I need some help and haven't had luck searching for it on here. I have a large spreadsheet that has location info in column A. The rest of the columns contain the pricing for each location by supplier. My table looks like this: Sup 1 Sup 2 Sup 3 PIT 5.50 6.00 6.00 LAX 5.50 5.50 4.50 ORD 6.00 5.25 5.50 In Column E, I'd like the max value, and in Column F, the Supplier Name(s) with the max value for each location. I'm good with Column E (=MAX(B2:D2)), and getting "6", but I'm stuck on Column F. I found this formula: =INDEX($B$1:$D$1,MATCH(MAX($B2:$D2),$B2:$D2,0)), but it's only returning "Sup 2" where I'd like to see both Sup 2 & Sup 3. I have no problems listing each supplier in a different column, but when I drag it over to Column G, I still only get Sup 2. All help is greatly appreciated!!! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Formula
Thanks for your help!!
Bernard - I've almost got your VBA code working, but not quite there. I'm off to a meeting for the remainder of the day. So, I'll have to get back to this. NBVC - I must be having a rough day, as your formula is giving me issues too. Thank you all though!! "Cow Girl" wrote: I need some help and haven't had luck searching for it on here. I have a large spreadsheet that has location info in column A. The rest of the columns contain the pricing for each location by supplier. My table looks like this: Sup 1 Sup 2 Sup 3 PIT 5.50 6.00 6.00 LAX 5.50 5.50 4.50 ORD 6.00 5.25 5.50 In Column E, I'd like the max value, and in Column F, the Supplier Name(s) with the max value for each location. I'm good with Column E (=MAX(B2:D2)), and getting "6", but I'm stuck on Column F. I found this formula: =INDEX($B$1:$D$1,MATCH(MAX($B2:$D2),$B2:$D2,0)), but it's only returning "Sup 2" where I'd like to see both Sup 2 & Sup 3. I have no problems listing each supplier in a different column, but when I drag it over to Column G, I still only get Sup 2. All help is greatly appreciated!!! |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Formula
In F2:
=IF(COUNTIF($B2:$D2,$E2)=COLUMNS($A:A),INDEX($B$1 :$D$1,SMALL(IF($B2:$D2=$E2,TRANSPOSE(ROW(INDIRECT( "1:"&COLUMNS($B$1:$D$1))))),COLUMNS($A:A))),"" ) ctrl+shift+enter, not just enter copy across and down as far as needed adjust your range to suit "Cow Girl" wrote: I need some help and haven't had luck searching for it on here. I have a large spreadsheet that has location info in column A. The rest of the columns contain the pricing for each location by supplier. My table looks like this: Sup 1 Sup 2 Sup 3 PIT 5.50 6.00 6.00 LAX 5.50 5.50 4.50 ORD 6.00 5.25 5.50 In Column E, I'd like the max value, and in Column F, the Supplier Name(s) with the max value for each location. I'm good with Column E (=MAX(B2:D2)), and getting "6", but I'm stuck on Column F. I found this formula: =INDEX($B$1:$D$1,MATCH(MAX($B2:$D2),$B2:$D2,0)), but it's only returning "Sup 2" where I'd like to see both Sup 2 & Sup 3. I have no problems listing each supplier in a different column, but when I drag it over to Column G, I still only get Sup 2. All help is greatly appreciated!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Help (IF) ... INDEX | Excel Worksheet Functions | |||
If or index formula help | Excel Worksheet Functions | |||
INDEX and MATCH in one formula... | Excel Worksheet Functions | |||
Index formula help | Excel Discussion (Misc queries) | |||
Sum and Index formula? | Excel Discussion (Misc queries) |