Microsoft Excel 2003 Help

Steve09 · 12 Oct 2006 at 12:55

My Teacher has asked me to construct a Speadsheet which looks like this...

The "-" Indicates a new column being used...

Steve - Chapman - 2006 - CSE
Sam - Collins - 2006 - CSE

My Teacher said he wants me to create a E-Mail address for each person using...

The 3 letters of the 4th column, which is "CSE"
The 2 last letters of the 3rd column, which is "06"
The 1st initial from the 1st column, which is "s"
and the 1st initial from the 2nd column, which is "c"

So the E-Mail Address will look like "cse06sc"

But heres the trick, the E-Mail Address for the 2 peoples names above...
(Steve Chapman and Sam Collins)

They will have the same E-Mail Address, because they have the same 1st and 2nd initial (S and C) they Enrolled the same Year (06) and they are in the same Faculty (CSE) so their E-Mail Address' will be the same, but as you know, you can not have the same E-Mail Address as someone else.

So my Teacher said to figure it out so...

Steve Chapmans E-Mail address was "cse06sc"

Sam Collins E-Mail address was "cse06sc1"

He said that this is using binary numbers? I am not sure.

He mentioned that I need to use the Text Formulas of...
CONCATENATE
LEFT
LOWER
RIGHT

I just don't know how to do this

Please help me someone, thanks for reading, Steve

Echo toxin · 12 Oct 2006 at 13:12

Basic formula is =LOWER(CONCATENATE(D2,RIGHT(C2,2),LEFT(A2,1),LEFT(B2,1)))

Need a bit more time to work out the duplicates bit.

Steve09 · 12 Oct 2006 at 13:23

Echo toxin said:
Basic formula is =LOWER(CONCATENATE(D2,RIGHT(C2,2),LEFT(A2,1),LEFT(B2,1)))

Need a bit more time to work out the duplicates bit.

If you could work it out for me that would be great!
I have just entered your Formula text, but nothing comes up in the box :S

Steve

Echo toxin · 12 Oct 2006 at 13:27

I started on row 2 because I put column headers in.

You might need to change A2,B2,C2,D2 in the formula to A1,B1,C1,D1

Steve09 · 12 Oct 2006 at 13:30

Echo toxin said:
I started on row 2 because I put column headers in.

You might need to change A2,B2,C2,D2 in the formula to A1,B1,C1,D1

I did and it still came up with nothing :S

What does yours come up with?

Thanks

Echo toxin · 12 Oct 2006 at 13:32

Check out the image

http://aycu17.webshots.com/image/3896/2000528314894773358_rs.jpg

Steve09 · 12 Oct 2006 at 13:35

Thats exactly how I want it

Il do it how you did it when I get home at 4pm
In the University Library at the moment.

If you could work it out how to make Sam Collins E-Mail address have the 1 at the end of it and and any others that are the same as Steve Chapman and Sam Collins E-Mail address to have 2 at the end, 3 at the end and so on that would be fantastic.

Thanks bud!

Steve09 · 12 Oct 2006 at 16:56

Back home now, tried the Formula and table set up exactly the same as you, and still nothing is displayed.

Are you using a Macintosh?

Thanks mate.

Echo toxin · 12 Oct 2006 at 17:44

Yes, I'm using a Mac but that won't make a difference to this.
Shall I just send you my spreadsheet?

EDIT: Maybe I'm reading too much into this. Does your teacher want you to just add a '1' or '2' manually? I'm trying to think of Excel doing it automatically but that is way too complicated for the [basic] stuff you are doing here.

Steve09 · 12 Oct 2006 at 22:48

Echo toxin said:
Yes, I'm using a Mac but that won't make a difference to this.
Shall I just send you my spreadsheet?

EDIT: Maybe I'm reading too much into this. Does your teacher want you to just add a '1' or '2' manually? I'm trying to think of Excel doing it automatically but that is way too complicated for the [basic] stuff you are doing here.

Along the lines of this, this is what he said...

"This spreadsheet has a gazillion lines, what I want is a Formula which I can copy into each cell in the E-Mail address column for each different person, and for the people who have the same data (like "cse06sc") I want the Formula to automatically put a 1 at the end, then if someone else has the same as that a 2 is put there automatically and so on."

I manually wrote 1s and 2s at the end for about 10 lines, he said this was wrong unless I wanted to sive through a gizillion lines and do it!

Thanks

Tomsk · 13 Oct 2006 at 08:07

Formulas

Code:

   A        	B	C	D	E     	                                                F

1  Steve	Chapman	2006	CSE	=RIGHT(D1,3) & RIGHT(C1,2) & LEFT(A1,1) & LEFT(B1,1)	=E1 & COUNTIF(E$1:E1,E1)
2  Sam    	Collins	2006	CSE	=RIGHT(D2,3) & RIGHT(C2,2) & LEFT(A2,1) & LEFT(B2,1)	=E2 & COUNTIF(E$1:E2,E2)
3  Stuart	Conner	2006	CSE	=RIGHT(D3,3) & RIGHT(C3,2) & LEFT(A3,1) & LEFT(B3,1)	=E3 & COUNTIF(E$1:E3,E3)

Results

Code:

	A	B	C	D	E	 F
1	Steve	Chapman	2006	CSE	CSE06SC	 CSE06SC1
2	Sam	Collins	2006	CSE	CSE06SC	 CSE06SC2
3	Stuart	Conner	2006	CSE	CSE06SC	 CSE06SC3

Column F has the final email address. The "$" is important!

EDIT
Looking back through the thread you can substitute your own formula for column E.

Steve09 · 13 Oct 2006 at 09:15

Thanks for all your help Echo!
Tomsk, that is exactly what I want, cheers mate

Il give it a go now.
Thanks again to both of you

/edit - Tomsk, how do I put the "lower" Formula included in what you have done, because he said as they are E-Mail address' he wants them in lower case, thanks

Tomsk · 13 Oct 2006 at 09:34

If you have to use "CONCATENATE" instead of "&" then the formulas would be

Echo toxin's formula in column E
=LOWER(CONCATENATE(D2,RIGHT(C2,2),LEFT(A2,1),LEFT( B2,1)))

and mine in column F (adjusted for Echo toxin's column headers)
=E2,COUNTIF(E$2:E2,E2)
There's no need for the LOWER function in my formula as it's already been used.

FYI the function "&" performs addition on text strings.

Steve09 · 13 Oct 2006 at 09:40

Perfect!!
That is exactly how I want/need it.

Now I will try it with other peoples names etc to see if I can just copy and paste the Formulas as he asked.

Here is a screenshot, thanks

Steve09 · 13 Oct 2006 at 09:48

Tomsk, whenever I enter this in column F
=E2,COUNTIF(E$2:E2,E2)

It comes up with an error, so I have to keep the first one...
=E2 & COUNTIF(E$2:E2,E2) (for the 1st row of data)

then
=E2 & COUNTIF(E$3:E2,E2) (for the 2nd row of data)

and
=E2 & COUNTIF(E$4:E2,E2) (for the 3rd row of data)

/edit - Instead of changing the E$2 everytime I go down a row, can I leave this as it is?

/edit - Im having problems.
When I paste them Formulas into a cell which has data like
Paul Smith 2003 ART
It is still returning
CSE06SC

I need 1 formula which I can paste into *a gazillion e-mail address) cells which will return the correct data for the e-mail address cell it has been pasted into regardless if they have the same data like cse06sc or art03ps
I have got to go to another lesson now, I will be at home at 2pm so hopefully we can sort this out!
Thanks for your time so far

Tomsk · 13 Oct 2006 at 10:06

Sorry, should have been =CONCATENATE(E2,COUNTIF(E$2:E2,E2))

You should use the FILL DOWN (CTRL-D) to fill column F. The E$2 doesn't change, but the E2's should reflect the current row eg in row 100 the formula would be CONCATENATE(E100,COUNTIF(E$2:E100,E100)

Steve09 · 13 Oct 2006 at 10:19

Yep, that works

It also works for different data when returned is like "art03ps1"

Tomsk, reckon you could create a Formula, which I could use, to paste into a gazillion E-Mail address cells, which will create the correct E-Mail address for the row its on, without changing things like the row its on from say...

=CONCATENATE(E2,COUNTIF(E$2:E2,E2)) Thats for row 2

then I have to change it every time, say for row 100

=CONCATENATE(E100,COUNTIF(E$2:E100,E100))

I would like, if possible, 1 Formula which I can paste into every E-Mail address cell, without having to change any information in the Formula, yet it still create the correct E-Mail address'

Thanks

Echo toxin · 13 Oct 2006 at 10:32

I've learned something new there then - can you summarise in basic terms what exactly's going on with the COUNTIF?

Steve09 · 13 Oct 2006 at 10:35

I think, what I am asking, can you make the Formulas so they cell reference themselves, without me going through and changing the things a million times?

So all I do, is paste the Formula, into every E-Mail address cell and thats it, cell references itself.

Make this Formula cell reference itself...

=CONCATENATE(E2,COUNTIF(E$2:E2,E2))

The same with Echos Formula too...

=LOWER(CONCATENATE(D2,RIGHT(C2,2),LEFT(A2,1),LEFT( B2,1)))

Thanks guys

Tomsk · 13 Oct 2006 at 14:40

Echo toxin said:
I've learned something new there then - can you summarise in basic terms what exactly's going on with the COUNTIF?

From Excel's own help:

COUNTIF(range,criteria)

Range is the range of cells from which you want to count cells.

Criteria is the criteria in the form of a number, expression, cell reference, or text that defines which cells will be counted. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.

(There's more but I'm sure you can find it.)