CODEWITHSUNDEEP
excelvbscriptxlsxlsx
joshpt
joshpt

Reputation: 733

Excel - Delete all one-to-one rows between two columns

Problem: I have Column A and Column B. Column A is predictable, there are thousands of column A rows. Column B has variying rows per Column A. For example, Column A may have the value REQ_000021 with 5 rows in Column B. Those are okay. The rows I need deleted are the ones where there is only one Column B per Column A.

I know this isn't a typical thing that people do so I couldn't find a VB script out there to do this. I would post a picture if the site would allow me to. Need 15 rep points first.

Edit:
I just learned how to do newlines on stackoverflow , so here this goes: 

Col A    Col B

----------------------------------

Scott    Us

         Germany

         Spain

----------------------------------

brian    US

----------------------------------

Mark     US

----------------------------------

         Italy

Tim      Portugal

         Ireland

----------------------------------

Delete brian and Mark because there is one row for Column A and Column B.
Leave Scott and Tim alone because there is more than one Column B row per column A row. Keep in mind that Column A for Scott and Tim are large, merged rows.
That's what I'm trying to get done.

Edit:
per Scott's answer, this is the output:
I captioned some markings for clarity for anyone else who is looking at this.
output from Scott's solution


Edit: This following VB script will Delete any row that has the "DELETE" value in column C.
I used it as a macro in Excel. For those who are new to macros, enable Developer view in Excel 2007 to gain access to the view that lets you toggle to the VB screen.

Sub deleteDeleteString()
Dim rng As Range, cell As Range, del As Range
Dim strCellValue As String
Set rng = Intersect(Range("C:C"), ActiveSheet.UsedRange)
For Each cell In rng

strCellValue = (cell.Value)
'if DELETE is found in a cell in Column C greater than 0 times, delete it's entire row
If InStr(strCellValue, "DELETE") > 0 Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.delete
End Sub

Upvotes: 2

Views: 945

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27249

Here is the new formula based on your edit of how your data is formatted.

Place the following formula in C1 and run down the column to get the rows to be deleted:

=IF(OR(ISBLANK(A1),ISBLANK(A2)),"","DELETE")

You may or may not want to un-merge the cells in column A first, but it shouldn't matter given the rules of how Excel handles merged cells. I tested this on the data set you provided and it worked with or without merging the cells.

I am not providing a VBA solution because you don't need one here. Run this formula than filter on Column C for "DELETE" and delete your rows.

Upvotes: 1

Related Questions