Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup / Match / Hlookup / Array fundtion or What?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup / Match / Hlookup / Array fundtion or What?
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 "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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup / Match / Hlookup / Array fundtion or What?
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup / Match / Hlookup / Array fundtion or What?
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup / Match / Hlookup / Array fundtion or What?
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup / Match / Hlookup / Array fundtion or What?
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=others heet!$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.) Dave Peterson wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
Need help with HLOOKUP and MATCH functions | Excel Worksheet Functions | |||
How do I find a value in an array (VLOOKUP? HLOOKUP?) | New Users to Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Vlookup, Index & Match | Excel Worksheet Functions |