removing or replacing a row based on specific criteria

I have attached a sample file which contains property information. Sheet 1 Column A contains the 'ParcelNumber' which is a county identifier. Column B (imprv_id) contains specific improvement numbers related to the parcel number. Column N (calc_area) is the total area for that specific improvement. If the Parcel number is repeated (rows 2-3, 8-9, 16-17, 23-25, 30-31, and 41-42) it means multiple living area is present on the site (guest house, mother-in-law apartment,...). However they don't parse it out in a way I can work with and after spending the entire day fighting it.... What I am hoping to do (and have created a sample result on Sheet 2) is:

Results to be on Sheet 2.
1. Create two new columns in C (bld_num) and D (add_living)
2. If a parcel number does not repeat, place a 1 in column C (bld_num)
3. If a parcel number does repeat:
a) If the imprv_id (Column B) does not repeat, just number them 1, 2, ...
b) If the imprv_id (Column B) does repeat, delete the repeating row and place the calc_area (Col N) in the new column D (add_living)

The rest of the data which is present can just be copied along and I will deal with it accordingly.

I appreciate your looking and any insight you can offer. Thank you in advance.

Hi

See how this goes.

Please Login or Register  to view this content.

rylo

It works very well on the sample file but when I apply it to the real file I don't get the results. The real file has approximately 35,000 rows. I have tried it with all 35,000, 10,000 and 500 without success. It fills Column C with "1's" and leaves Column D blank. Thank you for your efforts.

I have just gone through the first 2500 rows (one by one) and I think I can simplify the process a little bit.

1. If the 'ParcelNumber' (Column A) does not repeat, no changes are necessary.
2. If the 'ParcelNumber' (Column A) does repeat, do the following:
a. 'A' repeats and 'B' (imprv_id) repeats, delete the duplicates
b. 'A' repeats and 'B' does not repeat, no changes are necessary.

'bld_num' represents the numerical order of the 'ParcelNumber' and how many times it repeats. I have found that the 'add_area' is redundant and is not necessary. It would be helpful if that column the total amount of how much that parcel number did repeat (ie. 1 of 1, or 1 of 2, 2 of 2, ...)

I hope this cleans things up a bit.

Thank you again.

I have continued to work with this and have come up with the following: The 'delete' process as described above can be done easily by simply using the 'Remove Duplicates' function in Excel. I then took the two rows in question (parcelnumber and imprv_id) and wrote the following macro:

Please Login or Register  to view this content.

My only problem now is filling the column of 'total_blds" I will attach this as a new thread "Replacing with highest value in column"