VB Help

[Shon]

I have posted before about finding duplicate data but have now changed the
criteria of what I am looking for and really think I need vb code to perform
the following task?. Any help would be gratefully appreciated!.

What I want to be able to do is look down my rows of data and at each change
in the item number look at the location code for this record and keep (or
mark with an x) this record and any other records that are the same until the
next change in item number. If there is only 1 item number and no duplicates
I would like to keep this record aswell. e.g. if I have the following data;

Item Number Location
2345 AA1
2345 AA2
2345 AA1
2345 AA2
5432 BB1
1234 CC1
1234 CC4
1234 CC5
1234 CC1
1234 CC4

I would like to see the following results (either removing the records, or
putting an x next to them, in this example I have put an x next to them;

Item Number Location
2345 AA1 x
2345 AA2
2345 AA1 x
2345 AA2
5432 BB1 x
1234 CC1 x
1234 CC4
1234 CC5
1234 CC1 x
1234 CC4

Therefore the item number is the key criteria followed by the location of
the first item number. I REALLY hope this makes sense as I think I have not
been clear in the past!

 

[OssieMac]

Hello Shon,

Try Advanced Filter. (Look up in Help if you don't know how to select
Advanced Filter).

Select all of your data including column headers.
Select Advanced Filter.
Ensure the List range in the Dialog box matches the selected range.
Click Copy to another location.
In the Copy to field Click the button at the end of the field. (Dialog box
changes)
Select just one cell in a clear area of your worksheet for the Copy to
location and then Click the button at the end of the field. (Returns to
original dialog box)
Check the box Unique records only.
Click OK.

 

[Robert Flanagan]

Shon, we have an add-in, the Duplicate Finder, that does exactly what you
want. You can find it at http://www.add-ins.com/duplicate_finder.htm. Lot
easier than writing your own code.

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Shon]

Thanks for looking at this. i cannot use advanced filter as I need to keep
duplicates that match the location for the first change in item number and
remove any other duplicates (in my example this means removing record 2345
AA2 which appears twice as this does not match the same location when the
item number changed) also single records that have no duplicates.

This is obviously not straightforward!