Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with "Function to do what auto-filter does"
I have read the instructions below (created by bj) for creating a new
filtered table in a different location but cannot seem to fully understand the concept -- An example of data - worksheet - raw data A B C 1 ORANGES FRUIT 235 2 CARROT VEG 0 3 APPLES FRUIT 345 I would like to use the following formula to create a new filtered table that will only list columns A and C when column C is greater than zero. Any help and handholding would be appreciated!! bj's solution-- I assume you want to generate a new filtered table in a different location if it is a second worksheet in column A enter =if(Criteria(sheet1 row),Row(Sheet1 row),"") copy down for as many rows as you want in column B enter =if(row()count(A:A),"",small(A:A,row()) in Column C =if(B1="","",index(sheet1-dataset,B1)) and copy down Hide columns A:B Modify The Equations As needed for your actual data set -- rk |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with "Function to do what auto-filter does"
Column A formula:
=IF('Raw Data'!C10,ROW('Raw Data'!C1),"") Column B: =IF(ROW()COUNT(A:A),"",SMALL(A:A,ROW())) Column C: (filtered A column from Raw Data) =IF(B1="","",INDEX('Raw Data'!$A$1:$A$100,B1)) Column D: (filtered C column from Raw Data) =IF(B1="","",INDEX('Raw Data'!$C$1:$C$100,B1)) Then, as BJ said, hide columns A and B. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "rk" wrote: I have read the instructions below (created by bj) for creating a new filtered table in a different location but cannot seem to fully understand the concept -- An example of data - worksheet - raw data A B C 1 ORANGES FRUIT 235 2 CARROT VEG 0 3 APPLES FRUIT 345 I would like to use the following formula to create a new filtered table that will only list columns A and C when column C is greater than zero. Any help and handholding would be appreciated!! bj's solution-- I assume you want to generate a new filtered table in a different location if it is a second worksheet in column A enter =if(Criteria(sheet1 row),Row(Sheet1 row),"") copy down for as many rows as you want in column B enter =if(row()count(A:A),"",small(A:A,row()) in Column C =if(B1="","",index(sheet1-dataset,B1)) and copy down Hide columns A:B Modify The Equations As needed for your actual data set -- rk |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with "Function to do what auto-filter does"
I don't understand it either, but why do you need a function? Why not just
filter column C and hide column B? Regards, Fred. "rk" wrote in message ... I have read the instructions below (created by bj) for creating a new filtered table in a different location but cannot seem to fully understand the concept -- An example of data - worksheet - raw data A B C 1 ORANGES FRUIT 235 2 CARROT VEG 0 3 APPLES FRUIT 345 I would like to use the following formula to create a new filtered table that will only list columns A and C when column C is greater than zero. Any help and handholding would be appreciated!! bj's solution-- I assume you want to generate a new filtered table in a different location if it is a second worksheet in column A enter =if(Criteria(sheet1 row),Row(Sheet1 row),"") copy down for as many rows as you want in column B enter =if(row()count(A:A),"",small(A:A,row()) in Column C =if(B1="","",index(sheet1-dataset,B1)) and copy down Hide columns A:B Modify The Equations As needed for your actual data set -- rk |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with "Function to do what auto-filter does"
Thanks- works perfectly!! If you have the time to explain how this works I
would be grateful. I always like to try to understand the logic, but this time I am missing something. -- rk "Luke M" wrote: Column A formula: =IF('Raw Data'!C10,ROW('Raw Data'!C1),"") Column B: =IF(ROW()COUNT(A:A),"",SMALL(A:A,ROW())) Column C: (filtered A column from Raw Data) =IF(B1="","",INDEX('Raw Data'!$A$1:$A$100,B1)) Column D: (filtered C column from Raw Data) =IF(B1="","",INDEX('Raw Data'!$C$1:$C$100,B1)) Then, as BJ said, hide columns A and B. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "rk" wrote: I have read the instructions below (created by bj) for creating a new filtered table in a different location but cannot seem to fully understand the concept -- An example of data - worksheet - raw data A B C 1 ORANGES FRUIT 235 2 CARROT VEG 0 3 APPLES FRUIT 345 I would like to use the following formula to create a new filtered table that will only list columns A and C when column C is greater than zero. Any help and handholding would be appreciated!! bj's solution-- I assume you want to generate a new filtered table in a different location if it is a second worksheet in column A enter =if(Criteria(sheet1 row),Row(Sheet1 row),"") copy down for as many rows as you want in column B enter =if(row()count(A:A),"",small(A:A,row()) in Column C =if(B1="","",index(sheet1-dataset,B1)) and copy down Hide columns A:B Modify The Equations As needed for your actual data set -- rk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Filter - how to collapse "non-blank" cells in a column? | Excel Discussion (Misc queries) | |||
Help with Auto Filter "Ends With" | Excel Discussion (Misc queries) | |||
how to auto "copy & paste" (or filter) from a Master spreadsheet? | Excel Worksheet Functions | |||
Change the Auto-filter that is "on" to a RED arrow, can't see blu | Excel Worksheet Functions | |||
Q: Auto filter "selected" color | Excel Discussion (Misc queries) |