Filtering data

[koperstexdq_dont_use]

Hi,

I hope someone can help with a solution to this problem.
I don't know if a macro is required or it can be done in another way.
Here is my worksheet:

A B C D E F
1 1 2 3 4 5
2 D H E N K
3 Peter afs 1 x aft
4 David x fer 1 1
5 Gordon 1 1 fer fer fer


Row 1 shows the date
Row 2 shows a category
The user input area is B3:F5

I want to filter the data and have them listed as shown below
The output should be like this: "name - date - category - user input"
The only cells that should not appear on the list are cells with the values
"x" or "1" or blank cells

After filtering the list should look like this:
Peter 1-D-afs
Peter-4-N-aft
David-2-H-fer
Gordon-3-E-fer
Gordon-4-N-fer
Gordon-5-K-fer

Thank you for any suggestions.

Kaj Pedersen

 

[MyVeryOwnSelf]

Here is my worksheet:

A B C D E F
1 1 2 3 4 5
2 D H E N K
3 Peter afs 1 x aft
4 David x fer 1 1
5 Gordon 1 1 fer fer fer

After filtering the list should look like this:
Peter 1-D-afs
Peter-4-N-aft
David-2-H-fer
Gordon-3-E-fer
Gordon-4-N-fer
Gordon-5-K-fer

The only cells that should not appear on the list are cells with
the values "x" or "1" or blank cells.

Unfortunately, the data is in a format that's inconvenient for Excel's built-in filtering.

One approach is to make an intermediate result that's more convenient. Here's one way.

My example has:
dates in B1:F1
categories in B2:F2
names in A3:A5
user input in B3:F5
intermediate result in J1:M15

In J1 put
=INDEX(A:A,INT((ROW()-1)/5)+3)

In K1 put
=INDEX($B$1:$F$1,1,MOD(ROW()-1,5)+1)

In L1 put
=INDEX($B$2:$F$2,1,MOD(ROW()-1,5)+1)

In M1, put
=IF(INDEX(A:F,INT((ROW()-1)/5)+3,MOD(ROW()-1,5)+2)="","",
INDEX(A:F,INT((ROW()-1)/5)+3,MOD(ROW()-1,5)+2))

In N1 put
=J1&"-"&K1&"-"&L1&"-"&M1
This is the result column.

Select J1:N1 and copy down to row 15.

Finally, filter on column M to exclude "x" or "1" or blank cells.

Hopefully, some variation of the above meets the need.

 

[Ron Rosenfeld]

Hi,

I hope someone can help with a solution to this problem.
I don't know if a macro is required or it can be done in another way.
Here is my worksheet:

A B C D E F
1 1 2 3 4 5
2 D H E N K
3 Peter afs 1 x aft
4 David x fer 1 1
5 Gordon 1 1 fer fer fer


Row 1 shows the date
Row 2 shows a category
The user input area is B3:F5

I want to filter the data and have them listed as shown below
The output should be like this: "name - date - category - user input"
The only cells that should not appear on the list are cells with the values
"x" or "1" or blank cells

After filtering the list should look like this:
Peter 1-D-afs
Peter-4-N-aft
David-2-H-fer
Gordon-3-E-fer
Gordon-4-N-fer
Gordon-5-K-fer

Thank you for any suggestions.

Kaj Pedersen

You need a macro for this.

Set up your workbook with two worksheets -- one for your data, and one for the results.
In the macro, you will see were I assumed they were named Data and Results. You can change that.

The macro assumes there is nothing in column A or row 1 that does not relate to the data
It also puts the results in separate cells; I was not clear if you meant the "-" to represent a different cell, or if you wanted the results all in a single cell per Name.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

================================
Option Explicit
Sub FilterSpecial()
Dim WS1 As Worksheet, WS2 As Worksheet
Dim vSrc As Variant
Dim vRes() As Variant
Dim rDest As Range
Dim LastRow As Long, LastCol As Long
Dim I As Long, J As Long, K As Long
Dim S As String

Set WS1 = Worksheets("Data")
Set WS2 = Worksheets("Results")
Set rDest = WS2.Range("a1")
WS2.Cells.Clear

With WS1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
vSrc = .Range("A1", .Cells(LastRow, LastCol))
End With

'Rows in Results
K = 0
For I = 3 To UBound(vSrc)
For J = 2 To UBound(vSrc, 2)
S = vSrc(I, J)
If S <> "" And _
S <> "x" And _
S <> "1" Then _
K = K + 1
Next J
Next I

ReDim vRes(1 To K + 1, 1 To 4)

K = 2
vRes(1, 1) = "Name"
vRes(1, 2) = "Date"
vRes(1, 3) = "Category"
vRes(1, 4) = "User Input"
For I = 3 To UBound(vSrc)
For J = 2 To UBound(vSrc, 2)
S = vSrc(I, J)
If S <> "" And S <> "x" And S <> "1" Then
vRes(K, 1) = vSrc(I, 1)
vRes(K, 2) = vSrc(1, J)
vRes(K, 3) = vSrc(2, J)
vRes(K, 4) = S
K = K + 1
End If
Next J
Next I

Set rDest = rDest.Resize(UBound(vRes), UBound(vRes, 2))
rDest = vRes
rDest.EntireColumn.AutoFit

End Sub
====================================