Help! data lookup remove if criteria met.

[aromaboards]

Background info: Microsoft Excel 2010 / VBA 2010
I have a problem I am trying to stream line a report I have to file monthly.I get a CSV file from webbased application that i turn into a xlsx My problem is that I have data in Column A in excel. Its a Load number to track whats been processed. For the most part its fine, however in the webbased application, someone messes up on a load they re-open it do the fixes and. When this happens it ads a -AO to the load number and saves the data. when the CSV file gets generated, not only do I have the original data but also the -AO data as well.
What Im looking for, is someway that excel would look at Column A compare the Load numbers, if a Load number contains -AO at the end, then delete all data associated with the load number that does not have the -AO at the End.

load#	Item	Descr.	qty
2015 4/121/3-AO <tbody> </tbody>	NI-MISC PP <tbody> </tbody>	NA	15
2015 4/121/3-AO <tbody> </tbody>	Basic Set	NA	2
2015 4/121/3-AO <tbody> </tbody>	Large Set	NA	50
2015 4/121/3	NI-MISC PP	NA	15
2015 4/121/3	Basic Set	NA	1
2015 4/121/3	Large Set	NA	50
2015 10/138/2/AO <tbody> </tbody>	Item3	NA	200
2015 10/138/2-AO	Item4	NA	450
2015 10/138/3	Item3	NA	450

<tbody>
</tbody>

In this example, There was some Admin Opens, and for the Basic set they added wrong qty, it was qty2 not qty1, So I need a Formula to just keep the -AO in this case. But only when the preceeding characters are matching.. Example- 2015 4/121/3 and 2015 4/121/3-AO, in instances like this delete rows with just 2015 4/121/3, but dont get rid 2015 4/121/6.
If thats confusing. sorry Im not good at explainging this.
Think like this, if Load# was one Column and -AO was another, I could code in a command compare the Load# column if duplicate then check Column -AO if value in -AO Delete duplicate rows without -AO, for all Other duplicates without -AO then do nothing.
But thats the problem I cant figure out how to do it since -AO is at the end of Load number.

any ideas??

 

[Jorge_B]

Hi,
It certainly is a little bit confusing, so let me see if I got it right.
You have a report in which, each time that someone updates a record, a new record is added with the same load# and “-AO” at the end. If that’s correct, it would mean that those records with “-AO” at the end contains the most recent and accurate data. Therefore you’d need to perform a kind of “clean up” of your report, deleting all records with duplicate load#, letting only the one with “-AO” at the end.
If the above is right, I assume that in the table you posted, only rows from 3 to 6 (not counting headers) must be deleted since there is at least one record with the same load# and “-AO” at the end.

In case that I got it correctly, I think you could try a macro little the one that follows.
Please note that it assumes your report is in the first sheet of the workbook.

Option Explicit
Sub CompareData()
Dim Cell As Range
Dim ReportRng As Range
Dim AOsList As Range
Set ReportRng = Sheets(1).UsedRange
Worksheets.Add after:=Sheets(ThisWorkbook.Sheets.Count)
Sheets(ThisWorkbook.Sheets.Count).Name = "AOsList"
ReportRng.AutoFilter field:=1, Criteria1:="*AO*"
ReportRng.Columns(1).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("AOsList").Range("A1")
Sheets("AOsList").Range("A:A").RemoveDuplicates Columns:=1, Header:=xlYes
Set AOsList = Sheets("AOsList").Range("A2", "A" & Sheets("AOsList").Range("A2").End(xlDown).Row)
AOsList.Replace What:="-AO", Replacement:=vbNullString
For Each Cell In AOsList
ReportRng.AutoFilter field:=1, Criteria1:=Cell.Value, Criteria2:="<>*AO*"
ReportRng.Rows(1).Hidden = True
ReportRng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
ReportRng.Rows(1).Hidden = False
Next
ReportRng.AutoFilter
Application.DisplayAlerts = False
Sheets("AOsList").Delete
Application.DisplayAlerts = True
End Sub

I have a couple of questions though:
- You say that “-AO” is added at the end, but what about the 7th record in the table you posted, note that it ends with “/AO”
- The comparison must be done based only on the load#, or should the item column be take into account too?

Hope it helps.