Forum Discussion
Keywords in one column to populate another
We need to be a little more specific here. Are you wanting to replace the values in column D of your data sheet with this logic? Should an additional column be created instead of overwriting data? Should the search be case-sensitive? - NO Additional column, I want to override that data, does not need to be case sensitive. I want to Search Column C based on my table, and replace the values in Column D that correspond to my table given.
In terms of a solution, were you wanting a formula solution (e.g. add another column in which to return results) or a VBA solution? Which ever is easier, I am new to excel. Is this going to be done repetitively? I notice this is a CSV file and it looks like it's from a web query from shopify. Is the master data somewhere else? You have an export I just did from Shopify, once I do it once I will update manually, I am just trying to do a mass update right now. Do you query to return it? If so, what is the URL or data source you're using? There may be a solution with Power Query we could use also. No return, can not give an URL.
Golfnhockey12 in that case I'd opt for a VBA solution. The below code will do what you want. Install it in the workbook you want it run in.
Sub ReplaceVendors()
Dim SourceSheet As Worksheet
Dim CriteriaSheet As Worksheet
Dim DataRange As Range
Dim RowIndex As Long
Dim CriteriaIndex As Long
Dim Criteria As Variant
Dim Source As Variant
Dim Values As Variant
Set SourceSheet = ThisWorkbook.Worksheets("shopexcel")
Set CriteriaSheet = ThisWorkbook.Worksheets("Criteria")
With SourceSheet
Set DataRange = .Range("A2", .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, .Cells(1, .Columns.Count).End(xlToLeft).Column))
Source = DataRange.Value
End With
With CriteriaSheet
Criteria = .Range("A2", .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, .Cells(1, .Columns.Count).End(xlToLeft).Column))
End With
ReDim Values(1 To UBound(Source, 1), 1 To 1)
For RowIndex = LBound(Source, 1) To UBound(Source, 1)
Values(RowIndex, 1) = Source(RowIndex, 4)
For CriteriaIndex = LBound(Criteria, 1) To UBound(Criteria, 1)
If InStr(1, LCase(Source(RowIndex, 3)), LCase(Criteria(CriteriaIndex, 1)), vbTextCompare) > 0 Then
Values(RowIndex, 1) = Criteria(CriteriaIndex, 2)
Exit For
End If
Next CriteriaIndex
Next RowIndex
SourceSheet.Range("D2:D" & DataRange.Rows.Count).Value = Values
End Sub
HTH