 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / May 2008LIST 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 To list entire rows match criteria, consider using AutoFilter  SignatureGary''s Student - gsnu2007 > 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 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 >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 Get can get the same result as AutoFilter by using formulas. See: http://groups.google.com/group/microsoft.public.excel.worksheet.functions/browse _thread/thread/b8a1a3dfa8ca813f/1c786a33cccf991e?lnk=st&q=dog#1c786a33cccf991e SignatureGary''s Student - gsnu2007h > Many thanks for your advice! I know AutoFilter is one option. > [quoted text clipped - 13 lines] > >> Many thanks, > >> Wilchong 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 >Get can get the same result as AutoFilter by using formulas. See: > [quoted text clipped - 4 lines] >> >> Many thanks, >> >> Wilchong 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/microsoft.public.excel.worksheet.functions/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 >Get can get the same result as AutoFilter by using formulas. See: > [quoted text clipped - 4 lines] >> >> Many thanks, >> >> Wilchong 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$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. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- 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 >This play will deliver it for your original posting .. > [quoted text clipped - 13 lines] >would be set in G6:G8. You'd get the exact results that you seek, with all >lines neatly bunched at the top. 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 SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > 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 Good Morning Max, I have study your Excel formular and need your confirmation. Really sorry for my insufficient Excel knowledge! My example in the original posting: The 3 data sets (5 data sample size) 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 G6 is the "1-Jan-08" 【it is the Starting Date】; in cell G7 is the "1-Dec-08"【it is the Ending Date】and in cell G8 is the "Cost" 【it is Category】. In your Excel formular which you suggested to place in cell H6, what does ROWS($1:1) actually mean? Many thanks, Wilchong >It's based on your original post, & I added my assumptions made, as per >lines: [quoted text clipped - 3 lines] >> Many thanks for your efforts! >> Wilchong All should be clear in this implemented, working sample: http://www.freefilehosting.net/download/3gm22 AutoList Data by Multiple Criteria.xls > ROWS($1:1) .. The above is used as an incrementer in the criteria col H. As used in the criteria formula in H6 down, ROWS($1:1) will simply return arb row numbers (1,5,9, etc) where the criteria is satisfied within the source data. These arb row numbers are then used by the extract formulas placed in F12:G12 down to "float up" the results nicely to the top. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Good Morning Max, > I have study your Excel formular and need your confirmation. Really sorry [quoted text clipped - 23 lines] > Many thanks, > Wilchong Many thanks Max, The Excel formulars are working extremely well. In your suggestion, the cell in F12 and G12 are receiving the indicators from col H. I have one question: Do you think your suggested Excel formula in F12 and G12 can be modified in order to have the same result, but WITHOUT depending on the indicator information on col H? What I mean is that without any Excel formula in col H, F12 and G12 still can have the same result! Many thanks for your time and effort, Wilchong >All should be clear in this implemented, working sample: >http://www.freefilehosting.net/download/3gm22 [quoted text clipped - 13 lines] >> Many thanks, >> Wilchong > The Excel formulas are working extremely well. But of course. You're welcome. > What I mean is that without any Excel formula in col > H, F12 and G12 still can have the same result! You can't spare even 1 helper col to specify the complex criteria out of 256 cols in the sheet <g>? I like to keep things simple, easy to understand what's happening / debug / cross apply. It's also more efficient / much faster to use non-array formulas, especially when you have to deal with large ranges. It's might be possible using complex array formulas, but I'll have to leave it to other responders out there to venture their thoughts on this for you. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Many thanks Max, > The Excel formulars are working extremely well. In your suggestion, the [quoted text clipped - 10 lines] > Many thanks for your time and effort, > Wilchong Dear Max, I am really appreciate your help and advice these days! Thanks a lot! From your valuable suggestion, I have learnt array formulas. Please allow me to ask one more question regarding your suggested Excel formula which place in G12. From the formulas: =IF(ROWS($1:1)>COUNT($H$6:$H $100) .................., one thing I still don't understand the purpose of ROWS($1:1) and why ROWS($1:1) needs greater than COUNT($H$6:$H$100). I think the whole morning, and still cannot get the answer. Many thanks for help! Wilchong >> The Excel formulas are working extremely well. >But of course. You're welcome. [quoted text clipped - 15 lines] >> Many thanks for your time and effort, >> Wilchong > .. From your valuable suggestion, I have learnt array formulas. Non-array formulas, you meant All earlier expressions given are normal, non-array formulas > =IF(ROWS($1:1)>COUNT($H$6:$H$100),"", ... The above is basically a simple "trap" to return neat looking blanks: "" instead of "ugly" error values once all the results are "floated up" COUNT($H$6:$H$100) will return the number of arb row numbers within the criteria col H (the number of arb row numbers = number of result lines which satisfy the criteria) ROWS($1:1) simply returns the number series: 1,2,3,... when copied down So when this number exceeds the COUNT, the "trap" will evaluate to TRUE, and return neat looking blanks: "" thereafter Trust the above clarifies it .. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Dear Max, > I am really appreciate your help and advice these days! Thanks a lot! From [quoted text clipped - 11 lines] > Many thanks for help! > Wilchong Good morning Max, I am sorry about one question, what is "arb" stand for? Many thanks, Wilchong >> .. From your valuable suggestion, I have learnt array formulas. > [quoted text clipped - 20 lines] >> Many thanks for help! >> Wilchong arb = arbitrary SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Good morning Max, > I am sorry about one question, what is "arb" stand for? > Many thanks, > Wilchong |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.