VBA For Loop Data Matching using Array

If I was able to help you, feel free to donate.
Paypal: [e-mail]
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

[@rexbenemerito1943]

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.

[@christophseyfarth8018]

Great Video! Trank you very much!

[@ryancagulang7543]

Its working for me too. Thanks for the video

[@sircorn4248]

Many thanks. Excellent video, solves my problem

[@LoveCodingAndPlay]

Glad it helped

[@sivasubramanian4626]

Great video thank you so much ❤❤❤

[@dabizness6662]

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

[@jeannoelaction]

Bravo good job

[@petralabui9302]

your video is amazing

[@LoveCodingAndPlay]

thank you

[@yazihernandez5166]

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..

[@loidaasar487]

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

[@olatundeopadokun6040]

This is great.....I 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.

[@mikedeboer]

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

[@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

[@kamalakannan.1971]

If the search sheet had data starting at column F you need to add 5 to the column index b in the two loops. See belo
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 5
searchArray(a, b) = sSh.Cells(a, 5 + b)
Next b
Next a
Dim fName, lName As String
For a = 1 To UBound(searchArray)
fName = searchArray(a, 1)
lName = searchArray(a, 2)


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

searchArray(a, 3) = rawArray(b, 7)
searchArray(a, 4) = rawArray(b, 8)
searchArray(a, 5) = rawArray(b, 9)
Exit For
End If
Next b
Next a
'Transfer data back
For a = 1 To UBound(searchArray)
For b = 3 To 5
sSh.Cells(a, 5 + b).Value = searchArray(a, b)
Next b
Next a
Debug.Print Format(Now, "hh:mm:ss")
Debug.Print "Process Completed"
End Sub

[@vijaysahal4556]

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👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻

[@redhaakhund1271]

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.

[@LoveCodingAndPlay]

That should be easy even without using an Array, sure I'll plan a great video for that. Thanks for the supportsf

[@vijaykumar-wg4pg]

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

[@LoveCodingAndPlay]

What is RFM?

[@Ram__o9]

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

[@LoveCodingAndPlay]

I think the excel query is much helpful for that scenario instead of manipulating data thru array

[@Ram__o9]

@@LoveCodingAndPlay thanks!!