Indexing multiple arrays

[Kybynn]

Hi all!

I would like to organize a number of arrays based on the status they are in.

The status is binary, either Yes or No. I am only interested in the "Yesses" and would like to organize them into a single list, ignoring blank rows and "No's".

I have no clue how to solve this and am willing to use VBA (although I am expecting that it is not necessary).

Example included:

The Final Example.xlsx

So in the example, I want to organize the green arrays into the yellow array.

Thanks already!

Cheers!

[zbor]

How about this macro:

Sub YES_list()
Dim i As Integer
Dim cnt As Integer
Dim ws As Worksheet
Dim c01 As Range, c02 As Range, c03 As Range, c04 As Range, c05 As Range

Set ws = Worksheets("Sheet1")

Set c01 = ws.Range("A1:A1000")
Set c02 = ws.Range("E1:E1000")
Set c03 = ws.Range("I1:I1000")
Set c04 = ws.Range("M1")
Set c05 = ws.Range("M1:M1000")

ws.Range("M1:O1000").ClearContents


   For Each mycell In c01
        cnt = WorksheetFunction.Count(c05)
    If mycell.Offset(, 1) = "Yes" Then

        c04.Offset(cnt, 0) = mycell.Offset(, 0)
        c04.Offset(cnt, 1) = mycell.Offset(, 1)
        c04.Offset(cnt, 2) = mycell.Offset(, 2)
 
   End If

   Next mycell

 For Each mycell In c02
            cnt = WorksheetFunction.Count(c05)
    If mycell.Offset(, 1) = "Yes" Then
        c04.Offset(cnt, 0) = mycell.Offset(, 0)
        c04.Offset(cnt, 1) = mycell.Offset(, 1)
        c04.Offset(cnt, 2) = mycell.Offset(, 2)

    End If


   Next mycell

 For Each mycell In c03
            cnt = WorksheetFunction.Count(c05)
    If mycell.Offset(, 1) = "Yes" Then
        c04.Offset(cnt, 0) = mycell.Offset(, 0)
        c04.Offset(cnt, 1) = mycell.Offset(, 1)
        c04.Offset(cnt, 2) = mycell.Offset(, 2)

    End If


   Next mycell
   
End Sub

[Marcol]

A simple formula approach might be to build a Table and List as helpers, these can be hidden, toggled with a grouping button, or on a seperate hidden sheet.

In your exanple the table is ...
In R1

=IF(B1="Yes",COLUMN(A1)+ROW()/1000,"")

in S1

=IF(F1="Yes",COLUMN(E1)+ROW()/1000,"")

In T1

=IF(J1="Yes",COLUMN(I1)+ROW()/1000,"")

Drag/Fill Down to the depth of your largest data table

In Q1

=IF(ROW($A1)>COUNT($R$1:$T$8),"",SMALL($R$1:$T$8,ROW($A1)))

Drag Down to cover the combined lengths of your data tables

Then the results become
In M1

=IF($Q1="","",INDEX($A:$K,MOD($Q1,1)*1000,INT($Q1)+COLUMNS($Q$1:Q$1)-1))

Drag Across to Column O, then Down

All of this can be made more robust by using named ranges for your data tables.

[micope21]

Or this

Yes or No

Click on attachment

[Marcol]

Or even this ...

Change Yes/No in Tables 1 to 3.

Extend the Tables as you need to.

[johnwills]

thank you!
____________________________
watch movies online for free without downloading or paying

[Kybynn]

Thank you very much all! Stars for everyone!!!!

Ow and if you wonder how I felt when I implemented your advice:

Nice!.jpg

Thanks!