Concatenating A Unique Number
 

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

Concatenating A Unique Number
 

On Fri, 23 Jan 2009 11:26:18 -0800 (PST), Sheldon Potolsky
wrote:

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$1:D1=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

Concatenating A Unique Number
 

Hello,

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

Regards,
Bernd

Concatenating A Unique Number
 

On Fri, 23 Jan 2009 12:39:14 -0800 (PST), Bernd P
wrote:

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

Concatenating A Unique Number
 

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$2:D2=D2))
or even
=C2&"-"&SUMPRODUCT((C$2:C2=C2)*(A$2:A2=A2)*(B$2:B2=B 2))

Regards,
Bernd

Concatenating A Unique Number
 

On Sat, 24 Jan 2009 03:38:27 -0800 (PST), Bernd P
wrote:

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$2:D2=D2))
or even
=C2&"-"&SUMPRODUCT((C$2:C2=C2)*(A$2:A2=A2)*(B$2:B2=B 2))

Regards,
Bernd

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