Converting from Ranges to Arrays to do calculations

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
196
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I am relatively new to arrays and find this a bit confusing.

I have a table, starting in cell A2, variable depth, but with constant width of 62 columns wide.

From Column N (col 14), I create a list of unique values in Column BL (col 64) -> all good

The idea is to count each incidence in Column N of the unique value in Column BL and output the count result to Column BM (col 54) -> all good, but slow.........

Enter Arrays..... because I am trying to loop one array (unique values), inside another (the raw data), I cant seem to get the syntax right. I am probably doing something most silly...

This is the basic code with all the extra bits removed.

Would somebody, please be so kind as to add the extra bits to turn this into an array sub, so I can compare it against my fruitless efforts. :)


VBA Code:
Sub DualAcceptanceTest()

    Dim rngData As Range, rngUniqueData As Range
    Dim lngRow As Long, RowCount As Long, lngCount As Long

    'Select active sheet
    Sheet5.Select 'Input Import
   
    'Set up Data to search
    Set rngData = Range("N3", Range("N3").End(xlDown))
     
    'Set up Unique values to look for
    RowCount = Range("N4").End(xlDown).Row
    Range("BL3").Formula2 = "=unique(N3:N" & RowCount & ")"
    Set rngUniqueData = Range("BL3", Range("BL3").End(xlDown))
   
    With rngUniqueData
       
        For lngRow = 1 To .Rows.Count
                   
            lngCount = Application.WorksheetFunction.CountIf(rngData, .Cells(lngRow, 1).Value)
            .Cells(lngRow, 2).Value = lngCount
            
            If lngCount > 3 Then
                .Cells(lngRow, 2).Interior.ColorIndex = 6
            End If
        
        Next lngRow
    End With

End Sub
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,549
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help & thanks for the feedback.:)
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
196
Office Version
  1. 365
Platform
  1. Windows
Hi Akuini, the work we did last night was nothing short of bloody excellent. Thanks man.
However, this morning it has created an unforeseen situation.

There are 63 incidents today of count >6, that require manual intervention.
So here is a a cheeky challenge for you, if you are interested :) .

If the value in Column BM is >=6 then
find the value in Column BL in Column N (which it always will because Col BL is the unqiue values of column N)
then if the value in Col S is found in the sting in Col BJ
delete range(a:bj)

Feel free to tell me to go away if you like.

Col NCol SCol BJCol BLCol BM
horse namehorse numberscratchedUnique horse namecount of horse name
Bajan Gold
5​
15;16;17;18;19Spiderbait
3​
Bajan Gold
5​
15;16;17;18;19Royal Inn
3​
Bering Sea
6​
15;16;17;18;19Ferdinand
3​
Bering Sea
6​
15;16;17;18;19Brother Max
3​
Bering Sea
6​
15;16;17;18;19Lowry
3​
Play Me Now
7​
15;16;17;18;19Inca Warrior
3​
Play Me Now
7​
15;16;17;18;19Ladies Man
3​
Play Me Now
7​
15;16;17;18;19Plushenko
3​
Buster Block
8​
15;16;17;18;19Swiss Kitty
6​
Buster Block
8​
15;16;17;18;19Tickle Me Almo
3​
Buster Block
8​
15;16;17;18;19Flavius
3​
Live The Moment
9​
15;16;17;18;19Hit The Switch
3​
Live The Moment
9​
15;16;17;18;19Tellmesomethingboy
3​
Live The Moment
9​
15;16;17;18;19Atomic Kitten
3​
Stubai
10​
15;16;17;18;19Taumalolo
3​
Stubai
10​
15;16;17;18;19Dance 'n' Sing
1​
Stubai
10​
15;16;17;18;19Mianjo
3​
 
Last edited:

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,549
Office Version
  1. 365
Platform
  1. Windows
I have a table, starting in cell A2, variable depth, but with constant width of 62 columns wide.
1. So it is an actual table not just a range, right? What's the table name?

then if the value in Col S is found in the sting in Col BJ
delete range(a:bj)
2. Do you want to delete the row or just the content?

3. Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive? And then share the link here.
This will make it easier to test & find a solution.
 

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
196
Office Version
  1. 365
Platform
  1. Windows
Hi Akuini,

1. yes it is a table. It is a result of a PQ. Table name is "input"
2. Only want to delete the range(a:bj) because the unique data and count are to the right of the table in columns BL & BM
3. I can up load the data no problem.
 

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
196
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

just trying to work out how to share a dropbox link - sorry
 

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
196
Office Version
  1. 365
Platform
  1. Windows
Ah Got it.. because off of the problems I had until last weekend, Dropbox was not enabled at startup. But anyway, here it is.....

 

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
196
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

With the range(a:bj), if the information to the right was elsewhere, then there is no problem deleting the entire row, but at the end of the day, all of the content in that row needs to be deleted. I cant have blank lines in this data because the procedures that follow this step use .currentregion property quite a lot.
 

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
196
Office Version
  1. 365
Platform
  1. Windows
Hi Akuini,

here is an easy way of doing it.

1. In a helper column add the formula =IFERROR(IF(FIND(S3,BJ3,1)>0,"Delete",""),"")
2. Autofilter on the word DELETE
3. Delete the Visible cells only.

should have thought about this before I ask you about it.

That works easy enough, hey?

That gets rid of any horse that is scratched anyway..
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,549
Office Version
  1. 365
Platform
  1. Windows
Ok, glad you found a working solution. (y)
 

Forum statistics

Threads
1,147,562
Messages
5,741,848
Members
423,691
Latest member
Fahad987

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