Checking for duplicates & assigning assending letters in next colomn before sort

[How How]

Hi,
I hope someone can help as I’m trying to learn a bit of VBA.
I’m trying to do 2 things with the example below in my new database….
1. Add a row to the bottom of the table through a user form I assign a log number (Col A), I need VBA to look & see if that number already exists & if it does assign a letter in col B starting with b & then c etc (If no match no value in Col B), then combine the 2 values in column c then sort as in the example on the right.
2. Need to ensure there can’t be duplicates from the 2 combined columns (A & B).

Can it be done?

Please Login or Register  to view this content.

[JBeaucaire]

No need for column B, put this in C2 and copy down:

=IF(COUNTIF($A$1:$A2, $A2)=1, $A2, $A2 & " " & CHAR(65+COUNTIF($A$1:$A1, $A2)))

[How How]

No need for column B, put this in C2 and copy down:

=IF(COUNTIF($A$1:$A2, $A2)=1, $A2, $A2 & " " & CHAR(65+COUNTIF($A$1:$A1, $A2)))

Ah, that worked by assigning the letters as required but it doesnt sort the way I need it (see below), I'm probably missing something!!

A B
100 100
101 101
102 102
103 103
104 104
105 105

102 102 B
102 102 C

[How How]

Sorry JB, I had a senior moment & was sorting in the wrong order!!!


It works perfectly!!

Thanks