Delete whole line based on sequence
I have some data dumping from MYOB that I need to sort. The data dumped by MYOB is in the following format:
- BSB number: 000-000
- Bank Acct No: 123456789
- Bank name: Company name
I have an if statement added to the right of these cells that return 1 or 2 or 3 which I have defined as a sequence.
Is there a macro or a way that I can delete the entire data line if the sequence 1,2,3 is not saved?
Example:
- BSB number: 000-000 1
- Bank Acct No: 123456789 2
- Bank name: Company name 3
- BSB number: 000-000 1
- Bank Acct No: 123456789 2
- Bank name: Company name 3
- BSB number: 000-000 1
- Bank name: Company name 3
- BSB number: 000-000 1
- Bank Acct No: 123456789 2
- BSB number: 000-000 1
- Bank Acct No: 123456789 2
- Bank name: Company name 3
The sequence must remain 1, then 2, then 3. If the sequence is interrupted, the line must be deleted.
Is it possible?
Thank!
source to share
Quick VBA Macro. Assuming your data is column C (= 3).
It writes "x" to column D if the row should be deleted
Sub sort()
Application.ScreenUpdating = False
For i = Columns(3).Find("*", [C1], , , , xlPrevious).Row To 4 Step -1
Merge = Cells(i - 0, 3).Value _
& Cells(i - 1, 3).Value _
& Cells(i - 2, 3).Value _
& Cells(i - 3, 3).Value
If Merge = "4321" Then
i = i - 3
Else
Cells(i, 4) = "x"
End If
Next i
Application.ScreenUpdating = True
End Sub
Not ideal as the first 4 lines are not sorted. But this is the beginning.
Perhaps others can improve the answer.
source to share
I really like your question and I was a little shocked - +1 for that!
The only way I could come up with this was with a rather ugly formula listing your 3 possibilities. Suppose you had your numbers in column B starting from the cell B1
, this formula will work from the cell B4
onwards (this means you have to look at the first 3 results yourself).
=IF(OR(AND(B4=1,B5=2,B6=3),AND(B3=1,B4=2,B5=3),AND(B2=1,B3=2,B4=3)),TRUE,FALSE)
Then you can simply delete the rows where this formula returns a value FALSE
.
I'll still think and try more and come up with a more elegant solution, but it will definitely do the trick.
EDIT (based on comments)
To resolve 4
, as the screenshot shows, you start the formula in B5
and it will be as follows:
=IF(OR(AND(B5=1,B6=2,B7=3, B8=4),AND(B4=1,B5=2,B6=3,B7=4),AND(B3=1,B4=2,B5=3,B6=4),AND(B2=1,B3=2,B4=3,B5=4)),TRUE,FALSE)
source to share