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

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

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

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