First off, I’m sorry if this is answered somewhere else. I’ve searched through the site and can’t find anything that seems to answer this question for my needs. I’m completely new to macros, this will be the first time I’ve tried implementing one. I attempted recording one myself but, as me coming here clearly shows, that got me nowhere….
Question
Part 1:
How do I create a macro in Excel that will filter my table so it only displays rows where all three columns (Northing, Easting, Elevation) are identical to the Northing, Easting, and Elevation of another row(s)?
Part 2:
Then, how do I make that same macro leave the first instance of the data but delete all rows containing the duplicate Northing, Easting, and Elevation? Again, all three must match the first instance. If only one or two of the columns match the first instance, I want it left because it’s not an identical point.
Part 3:
I need the macro to search my entire table and perform this action on every row where the Northing, Easting, and Elevation data is identical to those three pieces of data in any other row. In every case, I need it to leave the first row that appears with the data but delete the rows containing the duplicate data.
Thank you in advance for any help you might be able to be! I’m pretty desparate to find a solution that will allow me to quickly get through these 7000 rows of data I have.
I hope the sample below is not too long. I tried to give enough data to work with and have a good sample.
Excel file of the sample shown below
Point # | Northing | Easting | Elevation | Description |
---|---|---|---|---|
1 | 486942.990 | 2727277.620 | 817.090 | Example1 |
2 | 487232.950 | 2727284.613 | 787.460 | Example2 |
3 | 486879.180 | 2727517.621 | 803.425 | _NoDuplicate |
4 | 487155.702 | 2727564.718 | 794.449 | _NoDuplicate |
5 | 486942.990 | 2727277.620 | 817.090 | Example1 |
6 | 486942.990 | 2727277.620 | 817.090 | Example1 |
7 | 487222.142 | 2728277.351 | 789.665 | _NoDuplicate |
8 | 486471.604 | 2726417.279 | 780.678 | _NoDuplicate |
9 | 486522.528 | 2726483.133 | 785.644 | Example3 |
10 | 487265.671 | 2726869.400 | 780.803 | _NoDuplicate |
11 | 487232.950 | 2727284.613 | 787.460 | Example2 |
12 | 486942.990 | 2727277.620 | 817.090 | Example1 |
13 | 487232.950 | 2727284.613 | 787.460 | Example2 |
14 | 487232.950 | 2727284.613 | 787.460 | Example2 |
15 | 486942.987 | 2727277.621 | 817.092 | _NoDuplicate |
16 | 486942.990 | 2727277.620 | 817.090 | Example1 |
17 | 486916.976 | 2727960.190 | 792.667 | _NoDuplicate |
18 | 487136.935 | 2727629.030 | 788.643 | _NoDuplicate |
19 | 486522.528 | 2726483.133 | 785.644 | Example3 |
20 | 486522.528 | 2726483.133 | 785.644 | Example3 |
Please take a look at the sample above. Hopefully it will help everything make sense and aid in the solution. (I’ve reduced it to only 20 rows of data, but the file I’m dealing with has around 7000. That’s why I’m trying to find a more efficient way than manually searching for all the duplicates.)
Backstory (if interested/useful)
I’m a CAD drafter at a land surveying company. I’m currently drafting a job that multiple field crews had to work on at different times, meaning they had to import the previous crew’s field data into their GPS equipment to make sure they weren’t collecting the same data as the previous crew. However, they made some errors in their import/export process and now I have thousands of field locates with identical northing, easting, elevation, and point description, but different point numbers. This will make more sense if you take a look at the small sample Excel file I’ve linked.
After a couple days of manually searching hundreds of rows (out of around 7000) for the duplicate points and deleting all instances but the first, I’ve began to wonder if there’s an easier way. That’s what lead me here.