It sounds to me like you're gonna have lots and lots of formulas.

Debra Dalgleish's has some notes you may like:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

I think you'll need the =index(match()) formula for each cell in your table.

(I'd use the code <bg)

Winger wrote:

Dave,

Mob1 etc are indicators, and we do have the actual number alongside it. We
actually created the indicators to try and help us (!). I've dabbled with VB
programming in Access, but was hoping to avoid it in Excel.

The link you've kindly sent me seems to suggest a programming route to solve
the problem, but I'm sure a few simple Refercing functions could sort it.
I can obviously put each number under its respective column, and I'm
convinced that some form of "multiple" look up might work i.e. (in pseudo
speak) - if the occurance where the studentID and Phone type (eg Mob1 etc)
from a lookup matches the student ID on the curent line, and the Phone type
matches the curent column heading, then put the phone number in, otherwise,
leave it blank.

Some form of Array and Vlookup might do it, but I don't know how to tackle
the syntax.

thankyou for your efforts on this.

Regards

Winger

"Dave Peterson" wrote:

I'm confused.

Are Mob1 real phone numbers or is it an indicator of the type of phone? If it's
an indicator, is there a phone number in column C?

If it's a real phone number, how would you know if its a mobile, office, or home
number?

If you have 3 columns:
StudentName, TypeOfPhone, PhoneNumber

You may be able to use this:

http://groups.google.co.uk/group/mic...657d4a528ba66d

(one line in your browser)

or
http://snipurl.com/k4xw



Winger wrote:

I'm trying to tidy up 65,000 lines of student data ready to export into
another system. Our current spreadsheet has a sepearte line for each
telephone number we have for each student (S1, S2 etc).

So it currently looks like this:

S1 Mob1
S1 Mob2
S1 Home1
S2 Office1
S3 Mob1
S4 Home1
S4 Office 1
S5 Mob 1

I want each student to have just a single entry (down the page) and then the
type of number we have for them across the page, like this:

Mob1 Mob2 Mob3 Home 1 Office1
S1
S2
S3
S4
S5

I need a function (or combo of moves) so that the spreadsheet goes and
checks that we have (for example) a Mob1 for S1, or Mob2 for S1 and puts the
right phone number in the right boxes.

Any advcie / guidance on how to tackle this would be much appreciated.

Thanks

Winger

--

Dave Peterson


--

Dave Peterson