Change the lockstructure to 0 is a new thing I learned. Thanks alot. Found your video at the right time.

I have one issue sometimes its a zero, sometimes it's blank! If I copy the blank (in the original sheet where it's being searched) over the blank (that outputs zero), then it becomes blank output. I checked, theres nothing there, no space nothing and I tried pasting format only but nothing changes too, Really weird

Very informative. Thanks.

Amazing 👌

This is the VBA code to use the LAMBDA function in any Excel Workbook: Sub custom_function_with_lambda() 'This LAMBDA function is used to find the last row number for any single column ActiveWorkbook.Names.Add Name:="_last_row", RefersToR1C1:= _ "=LAMBDA(col_range,LOOKUP(2, 1/(col_range<>""""),ROW(col_range)))" ActiveWorkbook.Names("_last_row").Comment = "This custom function is used to find the last row number for any single column. Parameter: col_range" End Sub These are the VBA code for the UDFs last_row_v1 and last_row_v2: 'One version of the last row function Function last_row_v1(col_range As Range) As Long 'Turn on error handling On Error Resume Next 'Find the last row based on the entire column. last_row_v1 = col_range.Worksheet.Cells(col_range.Worksheet.Rows.Count, col_range.Column).End(xlUp).Row 'If error occurs... If (Err.Number <> 0) Then 'Last row will be equal to 0 last_row_v1 = 0 'Clear the error Err.Clear End If 'Turn off error handling On Error GoTo 0 End Function 'Another version of the last row function Function last_row_v2(col_range As Range) As Long 'Range variable used to loop through each cell in the selected range Dim cell As Range 'Long variable used to store the current last row number in each looped cell Dim last_row As Long 'Turn on error handling On Error Resume Next 'Loop through each cell in selected range For Each cell In col_range 'If current cell is not blank, then store the current row number into last_row variable If (cell.Value <> "") Then last_row = cell.Row End If Next cell 'Store the value in last_row to last_row_v2 for output last_row_v2 = last_row 'If error occurs... If (Err.Number <> 0) Then 'Last row will be equal to 0 last_row_v2 = 0 'Clear the error Err.Clear End If 'Turn off error handling On Error GoTo 0 End Function

with cte as (select user_id,created_at, lead(created_at,1) over(order by user_id,created_at) as nextday,lead(user_id,1) over(order by user_id,created_at) as nextuser from amazon_transactions) select distinct user_id from cte where user_id=nextuser and datediff(nextday,created_at)<7;

Pivots can actually collapse and expand groups..

Good one

Excellent!

What about mixed type data issues in the same column? I mean numeric and string type.

those slicers are great! you have a like from me!

Note that I cannot paste my 3 functions into the description box as they are too big, so I will just paste them here as a temporary measure. You can copy and paste the 3 functions below into your Excel add-in file and enable the add-in file to be used in any Excel Workbooks. The formatting (indentations) might look a bit messed up when copying and pasting here, so apologies in advanced!: 'This UDF will retrieve a list of file name from one or more folder paths Function get_file_names(ParamArray folder_paths() As Variant) As Variant 'Variables used to create FileSystemObject, everything associated with it, and keep track of files looped Dim fso As Object Dim fol_path As Object Dim file As Object Dim folder_paths_index As Long Dim file_names() As String Dim file_count As Long Set fso = CreateObject("scripting.filesystemobject") 'Turn on error handling 'If an error occurs, go to label "error_handler" On Error GoTo error_handler file_count = 0 'Loop through each folder path provided by the user For folder_paths_index = LBound(folder_paths) To UBound(folder_paths) 'Check to see if the folder path exists If (fso.FolderExists(folder_paths(folder_paths_index))) Then 'Set fol_path to current folder being looped Set fol_path = fso.GetFolder(CStr(folder_paths(folder_paths_index))) 'Loop through each file in the current folder For Each file In fol_path.Files 'Store the file name in the file_names array and adjust the size of the array for each file stored ReDim Preserve file_names(file_count) file_names(file_count) = file.Name file_count = file_count + 1 Next file End If Next 'If there are items in file_names, then transpose the array so output shows vertical array instead of horizontal 'Otherwise, output "No file(s) found." If (file_count > 0) Then get_file_names = Application.Transpose(file_names) Else get_file_names = Array("No file(s) found.") End If 'Reset object variables to nothing Set fso = Nothing Set fol_path = Nothing Set file = Nothing 'Assuming no errors, exit the function Exit Function error_handler: 'Handle errors and provide error descriptions 'Reset object variables to nothing get_file_names = Array("Error: " & Err.Description) Set fso = Nothing Set fol_path = Nothing Set file = Nothing End Function 'The UDF will show the file type in one column and the count for each file type in the next column from one or more folder paths Function count_files_by_type(ParamArray folder_paths() As Variant) As Variant 'Variables used to create FileSystemObject, everything associated with it, and keep track of files looped Dim fso As Object Dim fol_path As Object Dim file As Object Dim file_extension As String Dim file_count_dict As Object Dim key As Variant Dim result() As Variant Dim folder_paths_index As Long Dim i As Long 'Turn on error handling 'If an error occurs, go to label "error_handler" On Error GoTo error_handler Set fso = CreateObject("Scripting.FileSystemObject") Set file_count_dict = CreateObject("Scripting.Dictionary") 'Loop through each folder path provided by the user For folder_paths_index = LBound(folder_paths) To UBound(folder_paths) 'Check to see if the folder path exists. If (fso.FolderExists(folder_paths(folder_paths_index))) Then 'Set fol_path to current folder being looped Set fol_path = fso.GetFolder(CStr(folder_paths(folder_paths_index))) 'Loop through each file in the current folder For Each file In fol_path.Files 'Get the file extension name and store the name in a dictionary if it does not exist already 'Store the number of times each file extension name appears file_extension = fso.GetExtensionName(file.Path) If (file_count_dict.exists(file_extension)) Then file_count_dict(file_extension) = file_count_dict(file_extension) + 1 Else file_count_dict.Add file_extension, 1 End If Next file End If Next 'Populate the 2D result array with the dictionary values that will be used for count_files_by_type ReDim result(1 To file_count_dict.Count, 1 To 2) i = 1 For Each key In file_count_dict.Keys result(i, 1) = key result(i, 2) = file_count_dict(key) i = i + 1 Next key 'Return the result array count_files_by_type = result 'Reset object variables to nothing Set fso = Nothing Set fol_path = Nothing Set file = Nothing Set file_count_dict = Nothing 'Assuming no errors, exit the function Exit Function error_handler: 'Handle errors and provide error descriptions 'Reset object variables to nothing count_files_by_type = Array("Error: " & Err.Description) Set fso = Nothing Set fol_path = Nothing Set file = Nothing Set file_count_dict = Nothing End Function 'This UDF will check to see if the specific file name exists in one or more folder paths Function file_exists_in_folders(file_name As String, ParamArray folder_paths() As Variant) As Boolean 'Variables used to create FileSystemObject, everything associated with it, and keep track of files looped Dim fso As Object Dim fol_path As Object Dim file As Object Dim folder_paths_index As Long Set fso = CreateObject("scripting.filesystemobject") 'Loop through each folder path provided by the user For folder_paths_index = LBound(folder_paths) To UBound(folder_paths) 'Check to see if the folder path exists If (fso.FolderExists(folder_paths(folder_paths_index))) Then 'Set fol_path to current folder being looped Set fol_path = fso.GetFolder(CStr(folder_paths(folder_paths_index))) 'Loop through each file in the current folder For Each file In fol_path.Files 'If file exists in any of the folders, return function output as TRUE, reset object variables, 'and exit the function If (fso.fileexists(fol_path & "\" & file_name)) Then file_exists_in_folders = True Set fso = Nothing Set fol_path = Nothing Set file = Nothing Exit Function End If Next file End If Next 'If there were no files found in each folder path, then output FALSE file_exists_in_folders = False 'Reset object variables to nothing Set fso = Nothing Set fol_path = Nothing Set file = Nothing End Function

I like your function. Is it possible for you to modify it so that it works on Excel Tables (ListObjects)?

Excellent. Cant we use this as an Add-in?

Much worse than the possibilities with python and co.

Awesome video! Might actually use this myself for a project 🔥to fill up a database

pl provide PDF note.thank yu

Super helpful

Really great!! This video deserves much more views. It’s super helpful. Your guidance is gold.

main problem is data science and analytics is very vast , going though various jd i found this , each and every company holds different requirement

Great job

Can you do a video of moving from India to abroad by getting a job. Nobody sponsors visas these days, especially for freshers

great advice brother

what's with the accent

On hindsight, I should have removed the duplicate rows before remove the unnecessary columns since the extra columns might contain unique values instead of duplicate values. Also, it was not necessary to remove the extra columns either, but did it anyway to make the file smaller and faster to load.

Thank you a lot of this video ! Excel still a powerful tool in 2023 ! Can you creat videos about the use of Excel for analytics please ?

What other topics for VBA / Excel do you want to see? FORGOT TO MENTION IN THE VIDEO : You can also share your code easily with anyone to run the automation scripts. All you have to do (or one way) is by sharing the macro enabled workbook.

I am 20 number subscriber, aspiring DA from Vancouver. Best wishes for Channel....expecting more real-life business/data analysis case videos.

This is good content. I am currently persuing IBM data Science certificate and im at 8 th course out of 10. I also have access to google data analytics and google advanced analytics course on coursera. I have got an overview of data analytics . I will complete it in 1 week or so. I have many courses in coursera for free. After that Is it better to do googe advanced data analytics course or do other specialization course like SQl , Statistics , machine learning etc. ?

Hi there! Your vídeo is very helpful! I've just have my Google Analytics certificate and i don know where exactly i'll start to hunt job. But you gave me a few ideas. Thank you.

What else do you want to see? Comment below.

very helpful thank you

What did you guys think of this video so far? I started testing out music with my own audio, so hopefully it is not too out of place. Let me know what else you would want to see and any areas of improvement for future videos.

What other video do you guys want to see. Drop down with a comment below!