Avatar of David Lelièvre
David Lelièvre

asked on 

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?
* arraysMicrosoft OfficeMicrosoft Excel

Avatar of undefined
Last Comment
David Lelièvre
Avatar of Andy Marshall
Andy Marshall
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of David Lelièvre
David Lelièvre



@ 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 :/

Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

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.
Avatar of David Lelièvre
David Lelièvre


You are right. I didn't even think about it. Omg.
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo