Extract value if sequence is respected in a table


I have a table of 2 columns: Orders and Operations.
An order normally starts with an operation 0010 and if needed, an operation 2 is added with the code 0020 and so on.
IE1: If an order needs 5 operations, we would have: Operation 0010, 0020, 0030, 0040 and 0050.

However, it is possible for someone to delete one of the operation.
IE2: If we take IE and someone deletes the 0010 and 0020, the order would only have the operation 0030, 0040 and 0050.

Is it possible to extract all orders that have deleted operation and all the deleted operations?
David LelièvreAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hello David,

Here is a very quick and dirty example of how you can do this:

Sub ExtractOrdersWithDeletions()

  Dim intRow As Integer
  Dim strOperation As String
  Dim strOrder As String
  intRow = 2
  Do While intRow < 235
    strOrder = Cells(intRow, 1).Text
    strOperation = Cells(intRow, 2).Text
    If strOperation <> "0010" Then
      Debug.Print strOrder
    End If
    Do While Cells(intRow, 1).Text = strOrder
      intRow = intRow + 1

End Sub

I an using Debug.Print to show the orders with deletions, but you could out put the numbers to the worksheet if you chose.
Ejgil HedegaardCommented:
You could also use conditional format to show the orders with missing operations.
See attached.

The condition is that the last operation for the order must match the number of operations.
But if the last operation is deleted it is not possible to see that.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David LelièvreAuthor Commented:

@ armchair_scouse,
How can I put the output in column D with the deleted operations next to it (if it exists)?

IE with 2 deleted operations
D2: 1928121
E2: 0020
F3: 0030

@ Ejgil Hedegaard
The last operation may be deleted :/

Ejgil HedegaardCommented:
The last operation may be deleted :/

It is not restricted to a formula solution.
Without knowing what the last operation is, no solution can detect that the last is missing.
David LelièvreAuthor Commented:
You are right. I didn't even think about it. Omg.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.