VBA - create a new table from a datasource dependent on values in datasource

[fergie_excel]

Hello,
I've been struggling with this problem for nearly a week and any help would be fantastic.

I have a data source with a large number of columns:

Ref No

Date

other1

other2

other3

4

5

6

7

8

9

Scheduled

Sent

timestamp

000

11/03/17

yes

yes

1344

001

12/03/17

no

yes

1216

002

13/04/17

yes

no

003

14/04/17

no

yes

1432

004

15/05/17

no

no

<tbody>
</tbody>

each ref no is report that gets sent via email by a user doing so by scheduling it via an excel user form I have made.
When the user schedule's the report to be sent the row and column relating to it is also updated to show whether it has been scheduled or already sent, in some cases scheduled to be sent for a second time (show by both columns value = yes), the time stamp is updated at the point of being sent.

from the data source (Table1 - also dynamic) I need to display in a separate table only those reports that are currently scheduled but is automatically updates whenever a reports scheduled status is changed.

Table2 only needs to list the report ref number, the date, scheduled, sent and time stamp columns.

Table 1 is contained on a sheet called "all_reports"
Table 2 would be contained on a separate sheet labelled "next_sched"

if its possible, I would like to bypass making the separate table and sheet all together and have the data displayed in a listbox with 5 columns on a userform.

Currently I have a listbox (called "AllReports") which displays table1 on the userform, But I have no idea how to display only specific columns and rows based on a criteria.

Hopefully I've described this ok, Any tips and advice would be great, If anymore info is needed please ask.

Thanks in advance

Fergie

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

This should give you a starting point:

Private Sub UserForm_Initialize()
    Dim ary     As Variant
    Dim dic     As Object
    Dim iRows   As Variant
    Dim i       As Long
    
    With ThisWorkbook.Worksheets("Sheet5")
        ary = .UsedRange
    End With
    
    Set dic = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(ary)
        If ary(i, 2) = DateValue("13/04/2017") Or i = 1 Then
            dic(i) = Array(ary(i, 1), ary(i, 2), ary(i, 12), ary(i, 13), ary(i, 14))
        End If
    Next

    With ListBox1
        .ColumnCount = 5
        .List = Application.Index(dic.items(), 0, 0)
    End With
End Sub

It is some code for the Userform and it runs when the user form is initialized.
I filtered the rows using the date. You can change that.
Note the "i = 1" part of that line is there to include the column headings.
I have assumed that you are using Windows so that I can use a Dictionary object.
Each dictionary item is an array and each array is one row of the output.

The data is read in using .UsedRange. That will include all the rows and columns on the page.

The line:

.List = Application.Index(dic.items(), 0, 0)

populates the listbox list. "Application.Index" is used to transfer all the items in the dictionary, which are all arrays. The "0, 0) means all rows and all columns in the dictionary. "dic.items()" is effectively an array and because each row is another array it is effectively a 2D array.

I had my input data on Sheet5. You might need to change that.

I hope this makes sense.


Regards,