"match" based on a name range - vba array - large data set

yolanno

New Member
Joined
Mar 6, 2019
Messages
1
Hello everyone,

I am new to this forum but I already have read a lot. Thank you for all the good work!

VoilÃ*, I am not used to Arrays and I think it would spare me a lot of time calculations.

I am trying to summarise informations based on a large dataset (265'000 +). To do that, I check if the Postal Code is my Name Range (dynamic name range) with a Isnumber(match()). It takes time and I have to work with other calculations after that so it will take even more time (countifs, sumifs, to compile the data based on my interest= name range)

Informations
- Filter on Sheet 1 Define 1 person
- Dynamic range based on filter in sheet 2 -> 1 person owns specific postal codes

Code:
 =OFFSET(Sheet2!$B$2;0;0;COUNTA(Sheet2!$B$2:$B$14)-COUNTBLANK(Sheet2!$B$2:$B$14);1)
- Sheet 3 (Area1 = Namerange 1, area2 name range2, area3 = name range3)

=ISNUMBER(MATCH(CP1;AREA1;0))

CPSalesAREA1AREA2AREA3
1007136FALSEFALSETRUE
1008832FALSEFALSEFALSE
1009729FALSEFALSEFALSE
10104FALSEFALSETRUE
10117064TRUEFALSEFALSE
101245454FALSEFALSEFALSE
1019212FALSETRUEFALSE
10206FALSEFALSEFALSE
10302496FALSEFALSEFALSE

<tbody>
</tbody>

So, I have found some snippet code to help me but I cannot get it to work

Code:
Public Function IsInArray(valToBeFound As Variant, arr As Variant) As Boolean
'DEVELOPER: Ryan Wells (wellsr.com)
'DESCRIPTION: Function to check if a value is in an array of values
'INPUT: Pass the function a value to search for and an array of values of any data type.
'OUTPUT: True if is in array, false otherwise
Dim element As Variant
On Error GoTo IsInArrayError: 'array is empty
    For Each element In arr
        If element = valToBeFound Then
            IsInArray = True
            Exit Function
        End If
    Next element
Exit Function
IsInArrayError:
On Error GoTo 0
IsInArray = False
End Function

Sub Demo()
Dim arr(2) As String
Dim i As Integer
arr(0) = "100"
arr(1) = "50"
arr(2) = "2"
i = 2
MsgBox IsInArray(CStr(i), arr)
End Sub

I would like to put "arr" as my array CP in sheet3 and "i" as my array AREA1 in sheet2. And I would like to return 1 or 0 (or true or false, whatever) in column 'AREA1" in sheet3.

I have made all different modifications but cannot get through.

I would really appreciate your help!

Thank you all for reading.

Belle journée,
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Watch MrExcel Video

Forum statistics

Threads
1,109,464
Messages
5,528,957
Members
409,848
Latest member
Blomsten
Top