| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP MATCH INDEX two conditions / criterias text and date
In Sheet1 I have a table with 5000 rows and 30 columns. I have simplifiede it
he A B C D E F Prod Categ. Descr. Code 1 Date Status R GS Red X 12.05.2008 Stopped B GS Yellow X 13.05.2008 Running E RG Green X Running X RG Blue X 10.05.2008 Stopped H JG Blue X 08.01.2008 Stopped F KG Red X 04.04.2008 Running ....5000 rows down... In Sheet2 I need a table with extracted data from Sheet1 based on two criterias: The Status from col.F is "Running", and the date in col.E has been filed in. I don't need all 30 columns, so I have selected column A, B, C. The result would then be like this: Product Product category Description B GS Yellow F KG Red ....filled 50 rows down... I don't think I can use a VLOOKUP as the data are not sorted accending, and there are two criterias. I believe I need an INDEX / MATCH formula. I have read http://www.contextures.com/xlFunctions02.html but I could't convert it to my use. Can anyone help? One array formula in one cell filling 50 rows down in Sheet2 would be perfect. Formulas copied out in Sheet2 A2:C50 would also be OK. -- Thanks John |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP MATCH INDEX two conditions / criterias text and date
Dear John,
How are you? Your question is not very clear. May I request you to mail me the workbook at . Please explain the problem very clearly. -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "John" wrote in message ... In Sheet1 I have a table with 5000 rows and 30 columns. I have simplifiede it he A B C D E F Prod Categ. Descr. Code 1 Date Status R GS Red X 12.05.2008 Stopped B GS Yellow X 13.05.2008 Running E RG Green X Running X RG Blue X 10.05.2008 Stopped H JG Blue X 08.01.2008 Stopped F KG Red X 04.04.2008 Running ...5000 rows down... In Sheet2 I need a table with extracted data from Sheet1 based on two criterias: The Status from col.F is "Running", and the date in col.E has been filed in. I don't need all 30 columns, so I have selected column A, B, C. The result would then be like this: Product Product category Description B GS Yellow F KG Red ...filled 50 rows down... I don't think I can use a VLOOKUP as the data are not sorted accending, and there are two criterias. I believe I need an INDEX / MATCH formula. I have read http://www.contextures.com/xlFunctions02.html but I could't convert it to my use. Can anyone help? One array formula in one cell filling 50 rows down in Sheet2 would be perfect. Formulas copied out in Sheet2 A2:C50 would also be OK. -- Thanks John |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP MATCH INDEX two conditions / criterias text and date
=IF(ISERROR(SMALL(IF((Sheet1!$E$1:$E$50<"")*(Shee t1!$F$1:$F$50="Running"),ROW(Sheet1!$A$1:$A$50),"" ),ROW($A1))),"",
INDEX(Sheet1!A$1:A$50,SMALL(IF((Sheet1!$E$1:$E$50< "")*(Sheet1!$F$1:$F$50="Running"),ROW(Sheet1!$A$1 :$A$50),""),ROW($A1)))) It is an array formula, so commit with Ctrl-Shift-Enter, Copy acroos 3 columns and down as far as you need. -- __________________________________ HTH Bob "John" wrote in message ... In Sheet1 I have a table with 5000 rows and 30 columns. I have simplifiede it he A B C D E F Prod Categ. Descr. Code 1 Date Status R GS Red X 12.05.2008 Stopped B GS Yellow X 13.05.2008 Running E RG Green X Running X RG Blue X 10.05.2008 Stopped H JG Blue X 08.01.2008 Stopped F KG Red X 04.04.2008 Running ...5000 rows down... In Sheet2 I need a table with extracted data from Sheet1 based on two criterias: The Status from col.F is "Running", and the date in col.E has been filed in. I don't need all 30 columns, so I have selected column A, B, C. The result would then be like this: Product Product category Description B GS Yellow F KG Red ...filled 50 rows down... I don't think I can use a VLOOKUP as the data are not sorted accending, and there are two criterias. I believe I need an INDEX / MATCH formula. I have read http://www.contextures.com/xlFunctions02.html but I could't convert it to my use. Can anyone help? One array formula in one cell filling 50 rows down in Sheet2 would be perfect. Formulas copied out in Sheet2 A2:C50 would also be OK. -- Thanks John |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP MATCH INDEX two conditions / criterias text and date
The best way is to use a PIVOT table
"John" wrote: In Sheet1 I have a table with 5000 rows and 30 columns. I have simplifiede it he A B C D E F Prod Categ. Descr. Code 1 Date Status R GS Red X 12.05.2008 Stopped B GS Yellow X 13.05.2008 Running E RG Green X Running X RG Blue X 10.05.2008 Stopped H JG Blue X 08.01.2008 Stopped F KG Red X 04.04.2008 Running ...5000 rows down... In Sheet2 I need a table with extracted data from Sheet1 based on two criterias: The Status from col.F is "Running", and the date in col.E has been filed in. I don't need all 30 columns, so I have selected column A, B, C. The result would then be like this: Product Product category Description B GS Yellow F KG Red ...filled 50 rows down... I don't think I can use a VLOOKUP as the data are not sorted accending, and there are two criterias. I believe I need an INDEX / MATCH formula. I have read http://www.contextures.com/xlFunctions02.html but I could't convert it to my use. Can anyone help? One array formula in one cell filling 50 rows down in Sheet2 would be perfect. Formulas copied out in Sheet2 A2:C50 would also be OK. -- Thanks John |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP MATCH INDEX two conditions / criterias text and date
I tried the Pivot Table, but I need the data shown as a table - with no
blanks. A Pivot Table groups all values in the first column, then the next, etc. This leaves empty spaces on the left side, gradually reducing as you move towards right. I have no data to put in the data field in the Pivot Table. All my fields has to be put in the "row area" of the Pivot Table. I have one version of this, but it doesen't look very nice, and I cant sort it decending on one of the "row-fields". I have hidden all "totals". -- Thanks John "Teethless mama" wrote: The best way is to use a PIVOT table "John" wrote: In Sheet1 I have a table with 5000 rows and 30 columns. I have simplifiede it he A B C D E F Prod Categ. Descr. Code 1 Date Status R GS Red X 12.05.2008 Stopped B GS Yellow X 13.05.2008 Running E RG Green X Running X RG Blue X 10.05.2008 Stopped H JG Blue X 08.01.2008 Stopped F KG Red X 04.04.2008 Running ...5000 rows down... In Sheet2 I need a table with extracted data from Sheet1 based on two criterias: The Status from col.F is "Running", and the date in col.E has been filed in. I don't need all 30 columns, so I have selected column A, B, C. The result would then be like this: Product Product category Description B GS Yellow F KG Red ...filled 50 rows down... I don't think I can use a VLOOKUP as the data are not sorted accending, and there are two criterias. I believe I need an INDEX / MATCH formula. I have read http://www.contextures.com/xlFunctions02.html but I could't convert it to my use. Can anyone help? One array formula in one cell filling 50 rows down in Sheet2 would be perfect. Formulas copied out in Sheet2 A2:C50 would also be OK. -- Thanks John |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP MATCH INDEX two conditions / criterias text and date
Thank you Bob. This works great. One problem. I need to sort Sheet2, col.C,
but it will not sort. Is this due to the: ROW($A...)? Is there any way around? The data in Sheet2 are constantly changing and the data in col.C should be sorted frequently. -- Thanks John "Bob Phillips" wrote: =IF(ISERROR(SMALL(IF((Sheet1!$E$1:$E$50<"")*(Shee t1!$F$1:$F$50="Running"),ROW(Sheet1!$A$1:$A$50),"" ),ROW($A1))),"", INDEX(Sheet1!A$1:A$50,SMALL(IF((Sheet1!$E$1:$E$50< "")*(Sheet1!$F$1:$F$50="Running"),ROW(Sheet1!$A$1 :$A$50),""),ROW($A1)))) It is an array formula, so commit with Ctrl-Shift-Enter, Copy acroos 3 columns and down as far as you need. -- __________________________________ HTH Bob "John" wrote in message ... In Sheet1 I have a table with 5000 rows and 30 columns. I have simplifiede it he A B C D E F Prod Categ. Descr. Code 1 Date Status R GS Red X 12.05.2008 Stopped B GS Yellow X 13.05.2008 Running E RG Green X Running X RG Blue X 10.05.2008 Stopped H JG Blue X 08.01.2008 Stopped F KG Red X 04.04.2008 Running ...5000 rows down... In Sheet2 I need a table with extracted data from Sheet1 based on two criterias: The Status from col.F is "Running", and the date in col.E has been filed in. I don't need all 30 columns, so I have selected column A, B, C. The result would then be like this: Product Product category Description B GS Yellow F KG Red ...filled 50 rows down... I don't think I can use a VLOOKUP as the data are not sorted accending, and there are two criterias. I believe I need an INDEX / MATCH formula. I have read http://www.contextures.com/xlFunctions02.html but I could't convert it to my use. Can anyone help? One array formula in one cell filling 50 rows down in Sheet2 would be perfect. Formulas copied out in Sheet2 A2:C50 would also be OK. -- Thanks John |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP MATCH INDEX two conditions / criterias text and date
Not, it is not sorted because you are extracting in the order in appears in
Sheet1. -- __________________________________ HTH Bob "John" wrote in message ... Thank you Bob. This works great. One problem. I need to sort Sheet2, col.C, but it will not sort. Is this due to the: ROW($A...)? Is there any way around? The data in Sheet2 are constantly changing and the data in col.C should be sorted frequently. -- Thanks John "Bob Phillips" wrote: =IF(ISERROR(SMALL(IF((Sheet1!$E$1:$E$50<"")*(Shee t1!$F$1:$F$50="Running"),ROW(Sheet1!$A$1:$A$50),"" ),ROW($A1))),"", INDEX(Sheet1!A$1:A$50,SMALL(IF((Sheet1!$E$1:$E$50< "")*(Sheet1!$F$1:$F$50="Running"),ROW(Sheet1!$A$1 :$A$50),""),ROW($A1)))) It is an array formula, so commit with Ctrl-Shift-Enter, Copy acroos 3 columns and down as far as you need. -- __________________________________ HTH Bob "John" wrote in message ... In Sheet1 I have a table with 5000 rows and 30 columns. I have simplifiede it he A B C D E F Prod Categ. Descr. Code 1 Date Status R GS Red X 12.05.2008 Stopped B GS Yellow X 13.05.2008 Running E RG Green X Running X RG Blue X 10.05.2008 Stopped H JG Blue X 08.01.2008 Stopped F KG Red X 04.04.2008 Running ...5000 rows down... In Sheet2 I need a table with extracted data from Sheet1 based on two criterias: The Status from col.F is "Running", and the date in col.E has been filed in. I don't need all 30 columns, so I have selected column A, B, C. The result would then be like this: Product Product category Description B GS Yellow F KG Red ...filled 50 rows down... I don't think I can use a VLOOKUP as the data are not sorted accending, and there are two criterias. I believe I need an INDEX / MATCH formula. I have read http://www.contextures.com/xlFunctions02.html but I could't convert it to my use. Can anyone help? One array formula in one cell filling 50 rows down in Sheet2 would be perfect. Formulas copied out in Sheet2 A2:C50 would also be OK. -- Thanks John |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP MATCH INDEX two conditions / criterias text and date
I solved this by "cheating" the pivot table. I inserted a row-field to the
left (col.A) with a unique serial No. As a result the pivot table has no longer got grouped data display. I also use VBA for automatic update and sorting of the pivot table result. I'm OK with this for now. Thank you All ! -- John "John" wrote: I tried the Pivot Table, but I need the data shown as a table - with no blanks. A Pivot Table groups all values in the first column, then the next, etc. This leaves empty spaces on the left side, gradually reducing as you move towards right. I have no data to put in the data field in the Pivot Table. All my fields has to be put in the "row area" of the Pivot Table. I have one version of this, but it doesen't look very nice, and I cant sort it decending on one of the "row-fields". I have hidden all "totals". -- Thanks John "Teethless mama" wrote: The best way is to use a PIVOT table "John" wrote: In Sheet1 I have a table with 5000 rows and 30 columns. I have simplifiede it he A B C D E F Prod Categ. Descr. Code 1 Date Status R GS Red X 12.05.2008 Stopped B GS Yellow X 13.05.2008 Running E RG Green X Running X RG Blue X 10.05.2008 Stopped H JG Blue X 08.01.2008 Stopped F KG Red X 04.04.2008 Running ...5000 rows down... In Sheet2 I need a table with extracted data from Sheet1 based on two criterias: The Status from col.F is "Running", and the date in col.E has been filed in. I don't need all 30 columns, so I have selected column A, B, C. The result would then be like this: Product Product category Description B GS Yellow F KG Red ...filled 50 rows down... I don't think I can use a VLOOKUP as the data are not sorted accending, and there are two criterias. I believe I need an INDEX / MATCH formula. I have read http://www.contextures.com/xlFunctions02.html but I could't convert it to my use. Can anyone help? One array formula in one cell filling 50 rows down in Sheet2 would be perfect. Formulas copied out in Sheet2 A2:C50 would also be OK. -- Thanks John |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
| index Match, or Vlookup Match.. | Excel Worksheet Functions | |||
| lookup with INDEX MATCH formule depending on 2 conditions | Excel Worksheet Functions | |||
| vlookup, match, index: all some or one? | Excel Discussion (Misc queries) | |||
| Index Match Vlookup or something else | Excel Discussion (Misc queries) | |||
Linear Mode
