Excel Questions

[Caid]

I am involved with the clubs lotto. I have all the details of our annual lotto members on excel (about 300).
we pay out for match 3/4 and 4 also.

Looking advice on functions for a spreadsheet that would pick out our the match 3s or 4s after entering the 4 numbers of that week, and perhaps highlight the winners..
appreciate any help to make this a bit easier...

Use hlookups, ifiserror and count function.  Sounds complicated but actually pretty easy

If you pm me your email i'll send u an example

[Treasurer]

I am involved with the clubs lotto. I have all the details of our annual lotto members on excel (about 300).
we pay out for match 3/4 and 4 also.

Looking advice on functions for a spreadsheet that would pick out our the match 3s or 4s after entering the 4 numbers of that week, and perhaps highlight the winners..
appreciate any help to make this a bit easier...

Use hlookups, ifiserror and count function.  Sounds complicated but actually pretty easy

If you pm me your email i'll send u an example

Sounds shorer than the way I've done it!

[donelli]

I have about 300 names in an excel spreadsheet. The full name (first name and surname) is in 1 cell.
I'm trying to sort out the surnames in alphabetical order. Is there anyway i can extract the surname from the cells to sort this or will i just have to re-write all the names in 2 cells ??

[Orior]

Cell A1 contains full name

Cell B1 formula should be     =LEFT(A1, FIND(" ",A1))
Cell C1 formula should be     =RIGHT(A1, LEN(A1) - FIND(" ",A1))


That will give you the forename in B1 and the surname in C1 (assuming that the forename and surname do not contain spaces)

[Bensars]

You also col A and do text to columns and tick space.

It will then seperate them after the first spce. Any Names with Mc or O' for example could then be rejoined in an additional columm

[donelli]

Cell A1 contains full name

Cell B1 formula should be     =LEFT(A1, FIND(" ",A1))
Cell C1 formula should be     =RIGHT(A1, LEN(A1) - FIND(" ",A1))


That will give you the forename in B1 and the surname in C1 (assuming that the forename and surname do not contain spaces)

good stuff. works a treat and will save me an hours work!! cheers

[passedit]

Data, text to columns, delimited space. Might have an issue with the os and mcs tho

[Orior]

Data, text to columns, delimited space. Might have an issue with the os and mcs tho

Sweet. I had actually been thinking of exporting then reimporting but that is much handier.

I learnt something new today.

[Bensars]

any mc's and o's can be joined in additional column.

=("b1"&" "&"c1")           e.g.  colum b =O     column c =Neill

Then copy corrected entries and use paste special to paste values only

[armaghniac]

much like Bensars said, if you end up with O and Mc in B and C then just put CONCATENATE(B1," ",C1) in D1 and copy down.

[Orior]

The following names can also cause problems

Jimmy Barry Murphy
. Cotton (aka Dot Cotton)
Kaka
squiggle (aka Prince from a few years ago)

[Billys Boots]

Data, text to columns, delimited space. Might have an issue with the os and mcs tho

Sweet. I had actually been thinking of exporting then reimporting but that is much handier.

I learnt something new today.

Trust you to find the hard way of doing it Orior. 

[Tony Baloney]

I hope this is easy...

I want to be able to merge data from 2 columns into one column.

i.e.
Column A = Document Reference        e.g. BD001
Column B = Document Revision           e.g. Rev.1

I want to merge both into a single column i.e.
Column C = Document Reference + Document Version e.g. BD001 Rev.1

Help would be really appreciated as I have about 1000 rows!

[thebigfella]

I hope this is easy...

I want to be able to merge data from 2 columns into one column.

i.e.
Column A = Document Reference        e.g. BD001
Column B = Document Revision           e.g. Rev.1

I want to merge both into a single column i.e.
Column C = Document Reference + Document Version e.g. BD001 Rev.1

Help would be really appreciated as I have about 1000 rows!

Try,

=concatenate(A1, " ", B1)

[saffron sam2]

I hope this is easy...

I want to be able to merge data from 2 columns into one column.

i.e.
Column A = Document Reference        e.g. BD001
Column B = Document Revision           e.g. Rev.1

I want to merge both into a single column i.e.
Column C = Document Reference + Document Version e.g. BD001 Rev.1

Help would be really appreciated as I have about 1000 rows!

Or =A1 & " " & B1

Ampersand is a great word.