Array Values based on IF THEN condition

[anshikam]

Hello All!

Is it possible to create an array from a database based on a specific value in a column.

I have a excel spreadsheet with 2500 rows and 100 columns.
I need to create an array for all columns but rows only if col x has certain values.

Since its a large no of coloumns and rows I am using the below to store data in the array vs assigning values manually.

myArray = myTable.Range

Is it possible and if yes how can it be done?

Thanks in Advance
Anshika

 

[Norie]

Does myArray currently store all the data?

 

[anshikam]

Does myArray currently store all the data?

Yes it does

 

[Norie]

You could try something like this.

1 Create a new array with the same dimensions as myArray but transposed.

2 Loop through myArray, checking the relevant column as you go.

3 If you find one of the values you are looking for in the column add the row to the new array, keeping a count of the no of rows being added.

4 Resize the new array using the count from 3.

5 Transpose the new array to get rows/columns the 'right' way round.

 

[anshikam]

You could try something like this.

1 Create a new array with the same dimensions as myArray but transposed.

2 Loop through myArray, checking the relevant column as you go.

3 If you find one of the values you are looking for in the column add the row to the new array, keeping a count of the no of rows being added.

4 Resize the new array using the count from 3.

5 Transpose the new array to get rows/columns the 'right' way round.

Thanks though would you have some code on this ?

 

[anshikam]

Thanks though would you have some code on this ?

So i am able to loop trough he first Column and check for values however the value i need to check is the 7th col and would like to add the entire row only if the value corresponds to 4 diff values else skip the row. I have not found anything on the internet to define the 4 values and check for them .......

 

[Norie]

You wouldn't loop through the columns, you would loop through the rows and check the relevant column.

Something like this.

Dim myArray() As Variant
Dim newArray() As Variant
Dim cnt As Long
Dim idxCol As Long
Dim idxRow As Long

    myArray = myTable.Range.Value

    ReDim newArray(1 To UBound(myArray, 2), 1 To UBound(myArray, 1))

    For idxRow = LBound(myArray, 1) To UBound(myArray, 1)

        ' check value in column 7
        Select Case myArray(idxRow, 7)
            Case "Value1", "Value2", "Value3", "Value4"
                cnt = cnt + 1
                For idxCol = LBound(myArray, 2) To UBound(myArray, 2)
                    newArray(idxCol, cnt) = myArray(idxRow, idxCol)
                Next idxCol
            Case Else
                 ' do nothing
        End Select
    Next idxRow

    If cnt > 0 Then
        ReDim myArray(1 To UBound(myArray, 2), 1 To cnt)
        myArray = Application.Transpose(myArray)
    End If