Vlookup / Match / Hlookup / Array fundtion or What?

[Guest]

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

 

[Guest]

More clarification:

I perhaps could have been a bit clearer in what I'm after -

I need a formula that looks down the list of student numbers (each student
is probably listed several times), and if the type of number that is found
(Mob1, Mob2, Office1etc) matches the current column heading, then put the
value of the telephone number into the cell.

Many thanks

Winger

 

[Dave Peterson]

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/mi...nsubject:table"&rnum=2&hl=en#2e657d4a528ba66d

(one line in your browser)

or
http://snipurl.com/k4xw

 

[Guest]

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]

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>)

 

[Dave Peterson]

I think I gave you a bum steer by pointing to Debra's site.

One formula that will work is =index(match()), but I don't think Deb has a
sample of that (yet???):

=index(othersheet!$c$1:$c$65535,
match(1,($a2=othersheet!$a$1:$a$65535)*(b$1=othersheet!$b$1:$b$65535),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.


The $a2 means that it when you drag this formula to the right, it will still be
using what's in column A (name) to match column A of the othersheet.

Similarly, b$1 means that it will always use row 1 (indicator) to match column B
of the other sheet.

But with the amount of data that you have, I would expect the formulas to take a
serious amount of time to recalc.

(I'd still use the code <vbg>.)