I would like to request Experts help to add additional function in the attached script. Currently data at Validation2 sheet only able to crosscheck data from column B with data at “Data” sheet and update Yes/No at column C (Validation2). I would like to expend the crosschecking data for all “Type” data at row 1 (Column E,H,K,N,Q,T until BV – Column under “Type” header) and update Yes/No (Column C until BW – column under “Status” header). Hope Experts will help me to create this feature.

Sub Compare()Dim oDic As Object, vData As Variant, i As Long, v, vOut(), j As LongSet oDic = CreateObject("Scripting.Dictionary")vData = Sheets("Data").Range("A1").CurrentRegion.Valuev = Sheets("Validation2").Range("B2", Sheets("Validation2").Range("B" & Rows.Count).End(xlUp))ReDim vOut(1 To UBound(v, 1))With oDic For j = LBound(vData, 2) To UBound(vData, 2) For i = LBound(vData, 1) To UBound(vData, 1) If Not IsEmpty(vData(i, j)) And Not .Exists(vData(i, j)) Then .Add vData(i, j), vData(i, j) End If Next i Next j For i = LBound(v, 1) To UBound(v, 1) If .Exists(v(i, 1)) Then vOut(i) = "Yes" Else vOut(i) = "No" End If Next iEnd WithSheets("Validation2").Range("C2").Resize(i - 1) = Application.Transpose(vOut)End Sub

Please kindly find the attached that may works for you.

Dim oDic As Object, vData As Variant, i As Long, v, vOut(), j As Long, k As IntegerSet oDic = CreateObject("Scripting.Dictionary")vData = Sheets("Data").Range("A1").CurrentRegion.ValueFor k = 2 To 74 Step 3 If k <= 26 Then schar = Chr(64 + k) Else schar = Chr(Int(k / 26) + 64) & Chr(k Mod 26 + 64) End If If k < 26 Then tchar = Chr(65 + k) Else tchar = Chr(Int(k / 26) + 64) & Chr(k Mod 26 + 65) End Ifv = Sheets("Validation2").Range(schar & "2", Sheets("Validation2").Range(schar & Rows.Count).End(xlUp))ReDim vOut(1 To UBound(v, 1))With oDic For j = LBound(vData, 2) To UBound(vData, 2) For i = LBound(vData, 1) To UBound(vData, 1) If Not IsEmpty(vData(i, j)) And Not .Exists(vData(i, j)) Then .Add vData(i, j), vData(i, j) End If Next i Next j For i = LBound(v, 1) To UBound(v, 1) If .Exists(v(i, 1)) Then vOut(i) = "Yes" Else vOut(i) = "No" End If Next iEnd WithSheets("Validation2").Range(tchar & "2").Resize(i - 1) = Application.Transpose(vOut)Next k

Thanks for the great solution. I need one help. The Status column at Validation sheet is actually was created manually after the data was exported from the different source. Is that any possible to add “Status” column atomically after “type” column while we run the macro. Hope you will consider this request. I have attached the actual Validation sheet before I add “status” column for your kind perusal. Hope you will consider this request.

Sub Compare()Dim oDic As Object, vData As Variant, i As Long, v, vOut(), j As Long, k As IntegerSet oDic = CreateObject("Scripting.Dictionary")vData = Sheets("Data").Range("A1").CurrentRegion.ValueFor k = 2 To 74 Step 3 If k <= 26 Then schar = Chr(64 + k) Else schar = Chr(Int(k / 26) + 64) & Chr(k Mod 26 + 64) End If If k < 26 Then tchar = Chr(65 + k) Else tchar = Chr(Int(k / 26) + 64) & Chr(k Mod 26 + 65) End Ifv = Sheets("Validation2").Range(schar & "2", Sheets("Validation2").Range(schar & Rows.Count).End(xlUp))Sheets("Validation2").Range(schar & "1").EntireColumn.Offset(0, 1).InsertSheets("Validation2").Range(tchar & "1").Value = "Status"ReDim vOut(1 To UBound(v, 1))With oDic For j = LBound(vData, 2) To UBound(vData, 2) For i = LBound(vData, 1) To UBound(vData, 1) If Not IsEmpty(vData(i, j)) And Not .Exists(vData(i, j)) Then .Add vData(i, j), vData(i, j) End If Next i Next j For i = LBound(v, 1) To UBound(v, 1) If .Exists(v(i, 1)) Then vOut(i) = "Yes" Else vOut(i) = "No" End If Next iEnd WithSheets("Validation2").Range(tchar & "2").Resize(i - 1) = Application.Transpose(vOut)Next kEnd Sub

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are …

The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…