Page 1 of 1

Replacing duplicate record numbers

PostPosted: Sat Apr 16, 2016 9:08 pm
by Crash-14
I'm using GSC version 15, 64 bit. I have large csv files that have duplicate field numbers (they are called record numbers on the sheet), approximately 300 out of over a million. I want to correct these so I can merge the csv data into a uniform format, and then upload it into a database. I created a test form to try to learn how to do so, but have not been able to figure this out as yet. Here's my setup:
Column A (2 through 21) has 20 original record numbers. Column B (2 through 11) has the duplicate numbers. Not all of these may be present in A, and they are spread throughout the column, not sequential. Column C (2 through 11) has 10 new unique numbers. I'm using column D for my formula.

In D2, I tried =IF(A2=B2,C2,A2) and got the number from A2. Copying this formula to D3:D21 returned the numbers from column A. This did not solve the problem, since for example A15 is a duplicate listed in B5 and the unique number from C5 needs to appear in D15.

I tried =IF(A2=(B2:B11),C2,A2) and got a #FILL! error.

I tried =replace(A2,B2:B11,C2,A2) and got a #FILL! error.

I tried =lookUp(A3,$B$2:$B$11,$C$1:$C$11) and got a #VALUE! error.

In column D, I need to be able to compare A to the range in B, and then if a match, have the proper C value appear. If there is no match, I need no change to A. While I'd prefer to automatically update Column A, this appears to be a decent compromise, as I can copy and paste column D over A once done.

I have attached my test worksheet in PDF format since I can't upload a .gsc file (?)

Thanks for any help.

Re: Replacing duplicate record numbers

PostPosted: Mon Apr 18, 2016 7:26 pm
by Crash-14
This can be disregarded. I simply added a 4-digit year to all existing record numbers via the concatenate function.