data FILTER rows AND columns

[greenfibres]

Hi everyone
The data FILTER function is so useful, I want to be able to filter BOTH rows AND columns...... is this possible?

i.e i can set the data filter along the columns and then find specific rows that contain results i'm looking for....
but then i also want to data filter along the rows and find specific columns.....

I have faith that someone has discovered the answer smile

All the best
Jim

 

[JVFriederick]

The only way I know how to do this is with a little VB code. In the following example, the code is looking for a cell value named 'Hide' that resides in row 1 from column E to AZ

For IntC = 5 To 52 ' E:AZ columns
If ActiveSheet.Rows(1).Columns(IntC).Value = "Hide" Then
ActiveSheet.Rows(1).Columns(IntC).Select
Selection.ColumnWidth = 0
Else
End If
Next IntC

If you wish to pick the value up from the spreadsheet based on the cell you have selected, the following code is useful

RowNum = Selection.Range("A1").Row ' Determine Row number of active cell
ColNum = Selection.Range("A1").Column ' Determine Row number of active cell
SelectedCellValue = ActiveSheet.Rows(RowNum).Columns(ColNum).Value ' get value

Hope this is helpful, and I am making the assumption you have written VB in some form.

 

[xlbo]

There is no way to filter columns without code (as per above)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

jim,

The concept that you stated in your request, is not directly possible using spreadsheet features.

However, this is exactly what a lookup function does -- picks out a value in a table based on some criteria in a row and another criteria in a column. VLOOKUP & HLOOKUP do this. Also check out INDEX & MATCH.

Skip,

_[red][/red]