Trouble Looping to eliminate multiple data

[gordon.graha]

I am working on a script which pulls data from one tab of a spreadsheet, depending on a particular milestone situated in a column. In the past, this worked due to having a custom field in our test environment specifically for this "milestone".

As we move in to the future, we are no longer allowed to design customised fields and so instead, we have began to embed our milestones within test case names in our tracker.

I have rewritten the script to capture these milestones, however, as there are several milestones being processed - rather than the contents of a field which would change as the need for a new milestone would - it is difficult to filter this information to display the information we require.

For example, we could have one test case which has two milestones - this is not correct and only the milestone furthest along the test cycle would be counted.

I'll illustrate with an example:

---
E2E_Test_Baked-Potato_01 Open [GBS Complete] No Run
E2E_Test_Baked-Potato_01 Open [Outstanding Steps] No Run
---

Baked Potato has passed through 2 milestones (GBS first, then Outstanding Steps) - therefore it's milestone is Outstanding steps.

Should i write a loop to step through each test case and check how far each have got along in terms of milestone, overwriting the milestones as a greater one is reached?

Can anyone help me?

I'm confusing myself, nevermind everyone else!!

[Bob Phillips]

Are the duplicates next to each other?

[gordon.graha]

Are the duplicates next to each other?

Well as they are in an excel sheet, i can order them so they are next to each other if need be...

[Bob Phillips]

Okay. FUrther question, in that example is that all in one column or in three columns?

[gordon.graha]

Okay. FUrther question, in that example is that all in one column or in three columns?

4 columns

Test Case: E2E_Test_Baked-Potato_01
Status: Open
Milestone:[GBS Complete]
Run Status:No Run

[Bob Phillips]

Okay, try this, it copies the rows to a new sheet

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

.Columns("A:C").Sort key1:=.Range("A1"), order1:=xlAscending, header:=xlGuess
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = LastRow To 1 Step -1

If .Cells(i, TEST_COLUMN).Value <> .Cells(i + 1, TEST_COLUMN).Value Then

Worksheets("Sheet2").Rows(1).Insert
.Rows(i).Copy Worksheets("Sheet2").Range("A1")
End If
Next i
End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub
[/vba]