Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Help
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Help
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. -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Help
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" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Help
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! "OssieMac" wrote: 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. -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|