VBA code that checks column A for values that are duplicates.

Hello,

I have a userform and when I submit, a record is added to a spreadsheet called "CallData", every record should ideally have a unique record under "CustomerID".

Here is an example:

Customer ID
12345
14785
12345
68996
45666
12345

Each record has multiple other rows, I do not want to overwrite the first record of "12345" when I am adding it again due to dealing with the same customer, each record is an interaction and should be individually added.

I would like to upon submitting a new record, if it's a duplicate, add a unique string onto the end to ensure I am not overwriting these records, for example "12345-1", if the same customer appears in the future, I add this "interaction", the Customer ID would be "12345-2" and so on.

I would then want to be able to retrieve the record of "12345" by populating the list box with all results that include "12345", those that have "-1", "-2" etc.. on the end

Can someone suggest a way to with VBA:
1: Upon submitting customer record, if CustomerID exists, add "-1", "-2" etc.. to the end
2: Populate the list box with the references corresponding to "12345" that is typed into a text box (and the duplicates)


Thanks
Will.

Originally Posted by Bernie Deitrick

Allow duplicates in column A, but add a new column B with formulas like

=COUNTIF($A$2:A2,A2)

or

=A2 & "-" & COUNTIF($A$2:A2,A2)

and that will add the transaction number for the customer ID. Then you can just filter A as normal.

Thanks for that,

would it be safe to add this to the far right of the spreadsheet? So that I could then update my VBA code so when it's updating a record its based on that transaction number, and at the same time I don't have to edit all the code to reflect everything bumped forward a column.

Thanks, simple and effective, I now won't risk editing the wrong interaction.