Show only items if "x"

[collegeitdept]

Does any on know of a formula that will only return the values in Column B only if it has an "X" in column A (without skipping a row).


Reference List

A B
x Item 1
Item 2
Item 3
Item 4
Item 5
x Item 6
x Item 7
x Item 8
Item 9
x Item 10



Returned values:

Item 1
Item 6
Item 7
Item 8
Item 10



Thanks.

 

[Alex O]

I'm fairly new to VBA myself, but I think this will get you started. Just paste in a standard module.
Create a backup before testing!

Sub Delete_xRows()
Application.ScreenUpdating = False
With ActiveSheet
.AutoFilterMode = False
.Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).AutoFilter field:=1, Criteria1:="=x"
.Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With
Application.ScreenUpdating = True
End Sub

 

[Steve=True]

Here is a non VBA solution.

Needs a dummy column

Column A = X's

Column B = Data to show

Column C =IF(A2="x",10^-6*ROW(),"")

Column D =IFERROR(INDEX($B$2:$B$11,MATCH(SMALL(OFFSET($C$2:$C$11,,,,),ROWS($A$2:A2)),$C$2:$C$11,0)),"")

Copy down. Change from B11 or C11 for the end of your range.

Steve=True
www.exceldashboardtemplates.com

 

[Aladin Akyurek]

Does any on know of a formula that will only return the values in Column B only if it has an "X" in column A (without skipping a row).


Reference List

A B
x Item 1
Item 2
Item 3
Item 4
Item 5
x Item 6
x Item 7
x Item 8
Item 9
x Item 10



Returned values:

Item 1
Item 6
Item 7
Item 8
Item 10



Thanks.

E1, just enter:

=COUNTIF(A1:A10,"X")

E2: list

E3, control+shift+enter, not just enter, and copy down:

=IF(ROWS($E$3:E3)<=$E$1,INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10="X",ROW($B$1:$B$10)-ROW($B$1)+1),ROWS($E$3:E3))),"")

 

[Steve=True]

Hi Aladin,

Why doesn't the countif work inside the array? Seems like it should, but doesn't in testing.

Thanks

Steve=True

 

[Rick Rothstein]

Does any on know of a formula that will only return the values in Column B only if it has an "X" in column A (without skipping a row).

Returned where? Here is a macro which will output the list starting at the cell specified in the FirstCellToOutputTo constant (the Const statement)...

Sub GetXedItems()
  Const FirstCellToOutputTo As String = "D1"
  Range(FirstCellToOutputTo).EntireColumn.Clear
  On Error Resume Next
  Columns("A").SpecialCells(xlCellTypeConstants).Offset(, 1).Copy Range(FirstCellToOutputTo)
End Sub

 

[Aladin Akyurek]

Hi Aladin,

Why doesn't the countif work inside the array? Seems like it should, but doesn't in testing.

Thanks

Steve=True

If you replace

$E$1

in the INDEX formula of E3 (see my post) with:

=COUNTIF($A$1:$A$10,"X")

the formula would still work. But we should avoid such repetitive calcs if we possibly can.

 

[collegeitdept]

Those formulas worked great.

Here's how I applied it to my workbook:


{=IFERROR(IF(ROWS(B$3:B3)<="",INDEX('[- Universal Invoice Form.xls]Sheet1'!$D$2:$D$992,SMALL(IF('[- Universal Invoice Form.xls]Sheet1'!$L$2:$L$992="x",ROW('[- Universal Invoice Form.xls]Sheet1'!$D$2:$D$992)-ROW('[- Universal Invoice Form.xls]Sheet1'!$D$2)+1),ROWS(A$3:B3))),""),0)}


However, that is looks for one variable (if) - if cells in column L = x

How do I add another condition

So the formula returns only values in Column D with the conditions that cells in Column L = X AND Column M = 2?


Thanks.

 

[Aladin Akyurek]

Those formulas worked great.

Here's how I applied it to my workbook:


{=IFERROR(IF(ROWS(B$3:B3)<="",INDEX('[- Universal Invoice Form.xls]Sheet1'!$D$2:$D$992,SMALL(IF('[- Universal Invoice Form.xls]Sheet1'!$L$2:$L$992="x",ROW('[- Universal Invoice Form.xls]Sheet1'!$D$2:$D$992)-ROW('[- Universal Invoice Form.xls]Sheet1'!$D$2)+1),ROWS(A$3:B3))),""),0)}


However, that is looks for one variable (if) - if cells in column L = x

How do I add another condition

So the formula returns only values in Column D with the conditions that cells in Column L = X AND Column M = 2?


Thanks.

If you don't want to calculate a count in order to use it as a counter in the formula, you can just invoke:

=IFERROR(INDEX('[- Universal Invoice Form.xls]Sheet1'!$D$2:$D$992,SMALL(IF('[- Universal Invoice Form.xls]Sheet1'!$L$2:$L$992="x",ROW('[- Universal Invoice Form.xls]Sheet1'!$D$2:$D$992)-ROW('[- Universal Invoice Form.xls]Sheet1'!$D$2)+1),ROWS(B$3:B3))),0)

Augmented with column M = 2, we get:

=IFERROR(INDEX('[- Universal Invoice Form.xls]Sheet1'!$D$2:$D$992,SMALL(IF('[- Universal Invoice Form.xls]Sheet1'!$L$2:$L$992="x",IF('[- Universal Invoice Form.xls]Sheet1'!$M$2:$M$992=2,ROW('[- Universal Invoice Form.xls]Sheet1'!$D$2:$D$992)-ROW('[- Universal Invoice Form.xls]Sheet1'!$D$2)+1)),ROWS(B$3:B3))),0)