Array Values based on IF THEN condition

anshikam

Board Regular
Joined
Aug 20, 2016
Messages
87
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,273
Office Version
  1. 365
Platform
  1. Windows
Does myArray currently store all the data?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,273
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Aug 20, 2016
Messages
87

ADVERTISEMENT

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

Board Regular
Joined
Aug 20, 2016
Messages
87
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,273
Office Version
  1. 365
Platform
  1. Windows
You wouldn't loop through the columns, you would loop through the rows and check the relevant column.

Something like this.
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,753
Messages
5,626,667
Members
416,199
Latest member
Gautamsunil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top