Excel Conditional Loop without VBA?

[TMervis]

On Sheet1, i have two columns: (a) Sale_Date, (b) Sale_Status.
On Sheet2, i want to generate a consecutive list of those Sale_Dates whose Sales_Status is "Pending".
I'm on Excel 16 and i don't want to use VBA. I don't want to either just use a filter and copy.
thanks

 

[MrJoosten]

You can use a pivottable for this I guess. I've made a simple setup:

Column A: Names
Column B: Dates
Column C: Status

Select data - Insert - Pivottable

Rows: Date & Name
Columns: Status (Filter on Pending)
Values: Count of Name or whatever you want to see.

Hope this helps.

 

[ttray33y]

try this, not test tho.

Sub test()


Application.ScreenUpdating = False


Dim lRow As Long
Dim i As Long
Dim wsh1 As Worksheet
Dim wsh2 As Worksheet
Dim stxt As String


Set wsh1 = ActiveWorkbook.Sheets("Sheet1")
Set wsh2 = ActiveWorkbook.ActiveSheet 'this is your Sheet1, put a button in Sheet2
lRow = wsh1.Cells(Rows.Count, 1).End(xlUp).Row
stxt = "Pending"


For i = 2 To lRow 'assuming your data in sheet1 is tarting in row 2, row 1 is the header, change 2 to whatever row your starting data is


    If wsh1.Cells(i, 2) = stxt Then
        With wsh1
            .Range(.Cells(i, 1), .Cells(i, 2)).Copy Destination:=wsh2.Range("A99999").End(xlUp).Offset(1, 0)
        End With
    End If
    
Next i


Application.ScreenUpdating = True


End Sub

 

[ttray33y]

i recommend using pivot table in your case, otherwise try my code above.

 

[pgc01]

Hi
Welcome to the board

Another option is to do the extraction using the Advanced Filter.