David Lelièvre
asked on
Extract value if sequence is respected in a table
Hi,
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello
@ 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 :/
Thanks!
@ 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 :/
Thanks!
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.
ASKER
You are right. I didn't even think about it. Omg.
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
Loop
Loop
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.