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.
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..
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
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
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
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.
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.
Great video thank you so much ❤❤❤
Great Video! Trank you very much!
Many thanks. Excellent video, solves my problem
Glad it helped
Bravo good job
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.
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..
Is Index(match ) faster than using For loop with array for search? Which is faster? I cant decide on this.. btw great video
your video is amazing
thank you
Its working for me too. Thanks for the video
Good example
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
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
Could we use arrays in another workbook if our raw data in another workbook and search data in another workbook
I think the excel query is much helpful for that scenario instead of manipulating data thru array
@@LoveCodingAndPlay thanks!!
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
This code doesn't work even before you put in the loop.
i hope you can send the practice file so i can practice along
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.
Hi Jay, Thanks for your video. Can you please do a video on RFM using VBA arrays.
Thanks,
Vijay
What is RFM?
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.
That should be easy even without using an Array, sure I'll plan a great video for that. Thanks for the supportsf
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👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻