I have two sheets. Sheet1 is an imported XML table so it looks a little awkward, like a stairstep of tables. Sheet2 is a single column that is a list of strings. Sheet1 has several thousand rows. Sheet2 has a few hundred entries.
I want to find every instance of each exact string in Sheet2 within Sheet1, then delete the whole row in Sheet1. That is the procedure.
In my head this is simple, and if I had hours I could manually do it but that would be a tedious waste of time since it should be automated.
I've been trying to find resources on how to do this but I find it very difficult to get information that would let me write a general procedure. Both Sheet1 and Sheet2 data sources will grow and change over time, so I want to make a general solution now.
I have a Python script that is supposed to trim out the XML source using what would be the contents of Sheet2, but the resulting XML is broken and unacceptable as it ends up missing data types. I don't know if doing this via Excel will preserve the data types but that why I'm trying. I learned C in college (electrical engineering) but never Python or VBA.
Assuming sheets are like arrays indexed by [rows,cols]. This is what I'm trying to do, with the functions standing in for whatever needs to be in VBA to achieve the same goal.
String str;
Int r, foundStrRow;
*/rowFind(sheet, string) being a function that finds arg string within sheet and returns the row index
Delete(sheet, row) being a function that deletes the index row from sheet*/
While(Sheet2[r,1]) {
Str = Sheet2[r, 1];
foundStrRow = rowFind(Sheet1,Str);
Delete(Sheet1, foundStrRow);
r++
}
Unfortunately the data itself is not something that can be shared for security reasons but I can provide a faux set of the data here.
Imagine that Sheet1 has that stairstep that is about 20000 rows and 200 columns. Sheet2 is 400 rows and one column.
This is what I have come up with. It is able to find and delete a whole row if the string from Sheet2 exists in Sheet1, but now I have to figure out how to make it conditional on the string existing. Not sure how to do that yet, but if thr string from Sheet2 isn't in Sheet1 then I need to skip to the next entry.
Sub Trimmer()
Dim Rows2 as Integer
Dim wordToSearch as String
'Sets Rows2 to be limit of the loop
With Sheet2
With .Cells.SpecialCells(xlCellTypeLastCell)
Rows2 = .Row
End With
End With
For r2=1 to Rows2
Sheets("Sheet2").Select
wordToSearch = Cells(r2,1).Value
Sheets("Sheet1").Select
Cells.Find(What := wordToSearch, After := ActiveCell, LookIn := xlFormulas2, _
LookAt :=xlWhole, SearchOrder := xlByRows, SearchDirection:= xlNext, _
MatchCase := False, SearchFormat := False).Activate
Rows(ActiveCell.Row).Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
This will iterate and take out the whole rows like I want where the match to the list in Sheet2 exists, but I just need to have it skip when something in the Sheet2 list isn't found in Sheet1.
What I would do is create two arrays to hold sheets 1 and 2. Then loop array 2, do an inner loop for array 1, and look for matches.
Sub MatchLinesFromSheets()
Dim ArrSheet1(), ArrSheet2()
Dim Rows1 As Long, r1 As Long, Cols1 As Long, c1 As Long
Dim Rows2 As Long, r2 As Long
Dim PasteRow As Long
'Load Sheet1 into array
With Sheet1
With .Cells.SpecialCells(xlCellTypeLastCell)
Rows1 = .Row
Cols1 = .Column
End With
ArrSheet1 = .Range(.Cells(1, 1), .Cells(Rows1, Cols1 + 1)).Value
End With
'Load Sheet2 into array
With Sheet2
With .Cells.SpecialCells(xlCellTypeLastCell)
Rows2 = .Row
End With
ArrSheet2 = .Range(.Cells(1, 1), .Cells(Rows2, 1)).Value
End With
'Step through each row of Sheet2
For r2 = 1 To Rows2
'Step through each row and column of Sheet1
For r1 = 1 To Rows1
For c1 = 1 To Cols1
If ArrSheet2(r2, 1) = ArrSheet1(r1, c1) Then
ArrSheet1(r1, c1) = ""
ArrSheet1(r1, Cols1 + 1) = "x"
Exit For
End If
Next
Next
Next
'Remove blank rows
For r1 = 1 To Rows1
If ArrSheet1(r1, Cols1 + 1) <> "x" Then
PasteRow = PasteRow + 1
For c1 = 1 To Cols1
ArrSheet1(PasteRow, c1) = ArrSheet1(r1, c1)
ArrSheet1(r1, c1) = ""
Next
End If
Next
With Sheets.Add
.Range(.Cells(1, 1), .Cells(Rows1, Cols1)) = ArrSheet1
End With
End Sub