| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
LIST out all the data according to the "Criteria"
There are 3 data sets are assumed running in A6, in B6 and in C6 down, viz.:
In A6 down is data of "date": 1-Jan-07, 1-Jul-07, 1-Nov-07, 1-Apr-08 and 1- Oct-08 In B6 down is data of "category": Revenue, Revenue, Revenue, Cost and Cost. In C6 down is data of "US$": US$1,200, US$1,250, US$3,000, US$450 and US$550. Another set of data is the "criteria": In cell G7 is the "1-Jan-08" 【it is Starting Date】; in cell G8 is the "1-Dec-08"【it is Ending Date】and in cell G9 is the "Cost" 【it is Category】. I know using the function "SUMPRODUCT" to compute the total amount within the specific date and under specific category. However, what I want is to "LIST" out all the data under specific date and specific category. In my example, I need a formula which can list "1-Apr-08" and "1-Oct-08" in the cell F12 and F13 and "US$450" and "US$550" in the cell G12 and G13. I need an Excel formula to have this result! Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
LIST out all the data according to the "Criteria"
To list entire rows match criteria, consider using AutoFilter
-- Gary''s Student - gsnu2007 "wilchong via OfficeKB.com" wrote: There are 3 data sets are assumed running in A6, in B6 and in C6 down, viz.: In A6 down is data of "date": 1-Jan-07, 1-Jul-07, 1-Nov-07, 1-Apr-08 and 1- Oct-08 In B6 down is data of "category": Revenue, Revenue, Revenue, Cost and Cost. In C6 down is data of "US$": US$1,200, US$1,250, US$3,000, US$450 and US$550. Another set of data is the "criteria": In cell G7 is the "1-Jan-08" 【it is Starting Date】; in cell G8 is the "1-Dec-08"【it is Ending Date】and in cell G9 is the "Cost" 【it is Category】. I know using the function "SUMPRODUCT" to compute the total amount within the specific date and under specific category. However, what I want is to "LIST" out all the data under specific date and specific category. In my example, I need a formula which can list "1-Apr-08" and "1-Oct-08" in the cell F12 and F13 and "US$450" and "US$550" in the cell G12 and G13. I need an Excel formula to have this result! Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
LIST out all the data according to the "Criteria"
Many thanks for your advice! I know AutoFilter is one option.
In fact, I am looking for alternative way to do it and also the database is located in one spreadsheet and the data which I want to list under criteria is located in another spreadsheet! As a result, I feel Excel function is the best solution my case! Many thanks for your help! Wilchong Gary''s Student wrote: To list entire rows match criteria, consider using AutoFilter There are 3 data sets are assumed running in A6, in B6 and in C6 down, viz.: In A6 down is data of "date": 1-Jan-07, 1-Jul-07, 1-Nov-07, 1-Apr-08 and 1- [quoted text clipped - 15 lines] Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
LIST out all the data according to the "Criteria"
Get can get the same result as AutoFilter by using formulas. See:
http://groups.google.com/group/micro...786a33cccf991e -- Gary''s Student - gsnu2007h "wilchong via OfficeKB.com" wrote: Many thanks for your advice! I know AutoFilter is one option. In fact, I am looking for alternative way to do it and also the database is located in one spreadsheet and the data which I want to list under criteria is located in another spreadsheet! As a result, I feel Excel function is the best solution my case! Many thanks for your help! Wilchong Gary''s Student wrote: To list entire rows match criteria, consider using AutoFilter There are 3 data sets are assumed running in A6, in B6 and in C6 down, viz.: In A6 down is data of "date": 1-Jan-07, 1-Jul-07, 1-Nov-07, 1-Apr-08 and 1- [quoted text clipped - 15 lines] Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
LIST out all the data according to the "Criteria"
Hello Gary''s Student - gsnu200781
I just tried your example and the Excel function you provided and I found out it is working OK! However, I have one question which need your further help. Because the size of your database is too big for me, so I made it to 20 sample size (A1 to A20, B1 to B20 etc.) In your example, the cell of D1 is search the data of value of 7 in col A, my question is how the existing Excel function can be changed if I ADD extra crietria e.g. the time frame e.g. between 【1 Jun 08】and 【30 Jun 08】? Therefore, I want to list out all the information which has value of 7 between 【1 Jun 08】and 【30 Jun 08】. Many thanks for your help! Many thanks, Wilchong Gary''s Student wrote: Get can get the same result as AutoFilter by using formulas. See: http://groups.google.com/group/micro...786a33cccf991e Many thanks for your advice! I know AutoFilter is one option. [quoted text clipped - 13 lines] Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
LIST out all the data according to the "Criteria"
Good morning Gary''s Student - gsnu200781
I just tried your example and the Excel function you provided and I found out it is working OK! Thank for your example: http://groups.google.com/group/micro...nctions/browse _thread/thread/b8a1a3dfa8ca813f/1c786a33cccf991e? lnk=st&q=dog#1c786a33cccf991e However, I have one question which need your further help. Because the size of your database is too big for me, so I made it to 20 sample size (A1 to A20, B1 to B20 etc.) From your example, instead of JUST searching the data which has value of 7 in col A, I want to list out all the information which has value of 7 between 【1 Jun 08】and 【30 Jun 08】. My new question has add an EXTRA crietria e.g. the time frame e.g. between 【1 Jun 08】and 【30 Jun 08】? In the example of 20 sample data, the result will be actomactic appear "27-Jun-08" in cell F1 and "gnu" in cell G1. I need an Excel formula to have this result! I know this question is extremely challenging! Many thanks for your help! Wilchong Gary''s Student wrote: Get can get the same result as AutoFilter by using formulas. See: http://groups.google.com/group/micro...786a33cccf991e Many thanks for your advice! I know AutoFilter is one option. [quoted text clipped - 13 lines] Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
LIST out all the data according to the "Criteria"
This play will deliver it for your original posting ..
Let's assume the source data will be within row 6 to 100 in cols A to C In H6: =IF(COUNTA(G$6:G$8)<3,"",IF(AND(A6=G$6,A6<=G$7,B6 =G$8),ROWS($1:1),"")) Copy down to H100 Then place In F12: =IF(ROWS($1:1)COUNT($H$6:$H$100),"",INDEX(A$6:A$1 00,SMALL($H$6:$H$100,ROWS($1:1)))) In G12: =IF(ROWS($1:1)COUNT($H$6:$H$100),"",INDEX(C$6:C$1 00,SMALL($H$6:$H$100,ROWS($1:1)))) Format F12 as date, G12 as currency to taste, then copy F12:G12 down as far as required to cover the max expected lines for any particular criteria that would be set in G6:G8. You'd get the exact results that you seek, with all lines neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
LIST out all the data according to the "Criteria"
Dear Max,
Is your Excel formula applying on Gary''s Students database or my early database which only from A6 to C10? Please advice! Many thanks for your efforts! Wilchong Max wrote: This play will deliver it for your original posting .. Let's assume the source data will be within row 6 to 100 in cols A to C In H6: =IF(COUNTA(G$6:G$8)<3,"",IF(AND(A6=G$6,A6<=G$7,B 6=G$8),ROWS($1:1),"")) Copy down to H100 Then place In F12: =IF(ROWS($1:1)COUNT($H$6:$H$100),"",INDEX(A$6:A$ 100,SMALL($H$6:$H$100,ROWS($1:1)))) In G12: =IF(ROWS($1:1)COUNT($H$6:$H$100),"",INDEX(C$6:C$ 100,SMALL($H$6:$H$100,ROWS($1:1)))) Format F12 as date, G12 as currency to taste, then copy F12:G12 down as far as required to cover the max expected lines for any particular criteria that would be set in G6:G8. You'd get the exact results that you seek, with all lines neatly bunched at the top. -- Message posted via http://www.officekb.com |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
LIST out all the data according to the "Criteria"
It's based on your original post, & I added my assumptions made, as per
lines: This play will deliver it for your original posting .. Let's assume the source data will be within row 6 to 100 in cols A to C -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "wilchong via OfficeKB.com" <u43231@uwe wrote in message news:83b9fa8cb2eb3@uwe... Dear Max, Is your Excel formula applying on Gary''s Student's database or my early database which only from A6 to C10? Please advice! Many thanks for your efforts! Wilchong |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Data Validation "List" - Setting length of list shown | Excel Discussion (Misc queries) | |||
| How Do I Get Out Of "DATA" ~ "CREATE LIST"? | Excel Discussion (Misc queries) | |||
| cannot use "Create List" and "Share Workbook" same time | Excel Discussion (Misc queries) | |||
| Macro to concatenate into "B1" B2 thru B"x" based on new data in "Col A" | Excel Discussion (Misc queries) | |||
| sort data rows "greater than or equal" criteria in another cell | Excel Worksheet Functions | |||
Hybrid Mode
