VBA For Loop Data Matching using Array

If I was able to help you, feel free to donate.

Sub array_Match_Data()

Debug.Print Format(Now, "hh:mm:ss")

Dim rSH As Worksheet
Dim sSh As Worksheet
Set rSH = ThisWorkbook.Sheets("RAW DATA")
Set sSh = ThisWorkbook.Sheets("SEARCH DATA")

Dim rawArray() As String
Dim searchArray() As String

ReDim Preserve rawArray(1 To rSH.Range("A" & Rows.Count).End(xlUp).Row, 1 To 11)
ReDim Preserve searchArray(1 To sSh.Range("A" & Rows.Count).End(xlUp).Row, 1 To 7)

For a = 1 To rSH.Range("A" & Rows.Count).End(xlUp).Row
For b = 1 To 11
rawArray(a, b) = rSH.Cells(a, b)
Next b
Next a

For a = 1 To sSh.Range("A" & Rows.Count).End(xlUp).Row
For b = 1 To 7
searchArray(a, b) = sSh.Cells(a, b)
Next b
Next a

Dim fName As String, lName As String
For a = 2 To UBound(searchArray)
fName = searchArray(a, 1)
lName = searchArray(a, 2)

For b = 2 To UBound(rawArray)
If rawArray(b, 1) = fName And rawArray(b, 2) = lName Then

searchArray(a, 3) = rawArray(b, 4)
searchArray(a, 4) = rawArray(b, 6)
searchArray(a, 5) = rawArray(b, 7)
searchArray(a, 6) = rawArray(b, 8)
searchArray(a, 7) = rawArray(b, 10)
Exit For
End If
Next b
Next a

'Transfer data back
For a = 2 To UBound(searchArray)
For b = 3 To 7
sSh.Cells(a, b).Value = searchArray(a, b)
Next b
Next a

Debug.Print Format(Now, "hh:mm:ss")
Debug.Print "Process Completed"

End Sub

[rexbenemerito]

Very well presented. This is the first of the tutorial that explained array in an uncomplicated way. Thanks very much.
Can you make a Search Module tutorial using UserForm with multiple condition and output the result in a listbox. An example application system for tracking employee training where a company has more than 20 types of health & safety training. 1st search condition is employee lastname or employee ID, 2nd condition is training description and 3rd search is status of certification (expired: soon to expire:active). Hope you can provide some guidance. Thanks a lot.

[sircorn]

Many thanks. Excellent video, solves my problem

[jonnathanjon]

Is Index(match ) faster than using For loop with array for search? Which is faster? I cant decide on this.. btw great video

[TylehurstXL]

Wow, great video. Your Vba writing speed is amazing. I do similar but try using dictionary object to save row number of every fname lname combination. Cleaner and less writing

[ryancagulang]

Its working for me too. Thanks for the video

[yazihernandez]

Doesnt really work as fast for mine tho, any improvement i can make? I have data that has equivalent to 7k rows and 30 columns. takes like a min to load..

[vijaykumar-wgpg]

Hi Jay, Thanks for your video. Can you please do a video on RFM using VBA arrays.
Thanks,
Vijay

[olatundeopadokun]

This is will like to share my worksheet with you(am new to VBA), am trying to Map columns headers on 2 different sheets(rawdata sheet and clean sheet) before coyping the columns rows. Will appreciate your feedback on this.

[phucnguyenlx]

Hi Jay, for the SEARCH DATA sheet. When I start the column from "F" not "A", I did as below but it gave does not return anything. Could you please advise? Tks

Sub array_Match_Data()

Debug.Print Format(Now, "hh:mm:ss")

Dim rSH As Worksheet
Dim sSh As Worksheet
Set rSH = ThisWorkbook.Sheets("RAW DATA")
Set sSh = ThisWorkbook.Sheets("SEARCH DATA")

Dim rawArray() As String
Dim searchArray() As String

ReDim Preserve rawArray(1 To rSH.Range("A" & Rows.Count).End(xlUp).Row, 1 To 9)
ReDim Preserve searchArray(1 To sSh.Range("F" & Rows.Count).End(xlUp).Row, 1 To 7)

For a = 1 To rSH.Range("A" & Rows.Count).End(xlUp).Row
For b = 1 To 9
rawArray(a, b) = rSH.Cells(a, b)
Next b
Next a

For a = 1 To sSh.Range("F" & Rows.Count).End(xlUp).Row
For b = 1 To 7
searchArray(a, b) = sSh.Cells(a, b)
Next b
Next a

Dim fName As String
For a = 1 To UBound(searchArray)
fName = searchArray(a, 1)


For b = 1 To UBound(rawArray)
If rawArray(b, 1) = fName Then

searchArray(a, 2) = rawArray(b, 7)
searchArray(a, 3) = rawArray(b, 8)
searchArray(a, 4) = rawArray(b, 9)
Exit For
End If
Next b
Next a

'Transfer data back
For a = 2 To UBound(searchArray)
For b = 2 To 7
sSh.Cells(a, b).Value = searchArray(a, b)
Next b
Next a

Debug.Print Format(Now, "hh:mm:ss")
Debug.Print "Process Completed"

End Sub

[redhaakhund]

You are master and best of the best every seen👍👍👍👍👍. thank so much. i have subscribed your channel. what if you have a list of employees, date and status in sheet1. if employee status is "" then copy/move to and on the the employee whos status is will be taken his duty/place. Waiting your video soon.

[Ram__o]

Could we use arrays in another workbook if our raw data in another workbook and search data in another workbook

[vijaysahal]

nice but we cnt properly understand bcz it was big data of array if you cn same array programs with small data then you can help with us👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻

[mikedeboer]

This code doesn't work even before you put in the loop.

[loidaasar]

i hope you can send the practice file so i can practice along