Concatenating A Unique Number

[Sheldon Potolsky]

If my data looks like the following -

StudentID School Year CourseID CourseName
724260 2000-2001 8881 PE
724260 2000-2001 8881 PE
697660 2001-2002 8891 PE
697660 2001-2002 8891 PE
697660 2001-2002 8891 PE

I would like to make each record unique by addling a number to the
CourseID so that I'd end up with:

StudentID School Year CourseID CourseName
724260 2000-2001 8881-1 PE
724260 2000-2001 8881-2 PE
697660 2001-2002 8891-1 PE
697660 2001-2002 8891-2 PE
697660 2001-2002 8891-3 PE

How can I do this?

Thank you,

Sheldon Potolsky

 

[Lars-Åke Aspelin]

If my data looks like the following -

StudentID School Year CourseID CourseName
724260 2000-2001 8881 PE
724260 2000-2001 8881 PE
697660 2001-2002 8891 PE
697660 2001-2002 8891 PE
697660 2001-2002 8891 PE

I would like to make each record unique by addling a number to the
CourseID so that I'd end up with:

StudentID School Year CourseID CourseName
724260 2000-2001 8881-1 PE
724260 2000-2001 8881-2 PE
697660 2001-2002 8891-1 PE
697660 2001-2002 8891-2 PE
697660 2001-2002 8891-3 PE

How can I do this?

Thank you,

Sheldon Potolsky

If your data is in columns A thru D, you can get a column with unique
Course ID in column E by this formula in cell E2:

=C2&"-"&(SUMPRODUCT((C$1:C1=C2)*(A$1:A1=A2)*(B$1:B1=B2)*(D$11=D2))+1)

Copy down as far as you have data.

The first parenthesis (C1$:C1=C2) should always be there.
The other parenthesis you can add if needed (your example does not
show if different student ID' or different School years can have the
same Course ID).

Hope this helps / Lars-Åke

 

[Bernd P]

Hello,

Why not just
=C2&"-"&COUNTIF(C$2:C2,C2)
and copy down?

Regards,
Bernd

 

[Lars-Åke Aspelin]

Hello,

Why not just
=C2&"-"&COUNTIF(C$2:C2,C2)
and copy down?

Regards,
Bernd

From the example given it is not clear if there could be
the same course ID for different student ID's (or different school
years) and, if so, the unique course ID should be "globally unique" or
just unique for each student ID.

Lars-Åke

 

[Bernd P]

Hello Lars-Åke,

Ok, you are right if local uniqueness is required.

But then I would suggest to use
=C2&"-"&SUMPRODUCT((C$2:C2=C2)*(A$2:A2=A2)*(B$2:B2=B2)*(D$22=D2))
or even
=C2&"-"&SUMPRODUCT((C$2:C2=C2)*(A$2:A2=A2)*(B$2:B2=B2))

Regards,
Bernd

 

[Lars-Åke Aspelin]

Hello Lars-Åke,

Ok, you are right if local uniqueness is required.

But then I would suggest to use
=C2&"-"&SUMPRODUCT((C$2:C2=C2)*(A$2:A2=A2)*(B$2:B2=B2)*(D$22=D2))
or even
=C2&"-"&SUMPRODUCT((C$2:C2=C2)*(A$2:A2=A2)*(B$2:B2=B2))

Regards,
Bernd

Yes, that is a bit simpler / Lars-Åke