Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Having Problems Excel 2003
I have been trying to rectify the anomalies in the data in a
spreadsheet that has become a mishmash of different Project and Task codes in order to provide a structure so that a final spreadsheet or database can be constructed that makes sense. I have 2 sheets of an Excel 2003 workbook, Sheet 1 has 11000 entries the first four columns are ProjectID, NewProjectID, TaskID and NewTaskID with 10 other columns. NewProjectID and NewTaskID are currently empty columns and I am looking for a formula that will populate them from sheet2 Sheet 2 has about 500 unique entries with the same first four columns ProjectID, NewProjectID, TaskID and NewTaskID and all four columns are completed I have extracted every single unique combination from Sheet1 to create sheet 2 and allocated a NewProjectID or NewTaskID. What I am trying to do now is write the formulas for sheet1, NewProjectID so that where sheet1 ProjectID = sheet2 ProjectID and Sheet1 TaskID = sheet2 TaskID then it picks up the NewProjectID in sheet2 and enters it into the corresponding column in sheet1. I also need to do exactly the same to create a formula to pick up the NewTaskID. I could do all this manually but with 11000 entries it's going to take a considerable time . The problem is the more I look at it the more confused I have become. Probably because I am so close to it and I've trying to rationalise all the options for four days Unfortunately the TaskID have about seven different ways of coding and there are nine different methodologies for the projectID which led to so much of my confusion in the first place. Can anyone help, I really am at my wits end. I am sure it will be a relatively simple formula using something like match and vlookup or something along those lines but my head is a plate of mince and I really have to get this done Any help would be gratefully appreciated Thanks BJthebear Scotland |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Having Problems Excel 2003
On 23 mar, 23:38, "BJ&theBear" wrote:
I have been trying to rectify the anomalies in the data in a spreadsheet that has become a mishmash of different Project and Task codes in order to provide a structure so that a final spreadsheet or database can be constructed that makes sense. I have 2 sheets of an Excel 2003 workbook, Sheet 1 has 11000 entries the first four columns are ProjectID, NewProjectID, TaskID and NewTaskID with 10 other columns. NewProjectID and NewTaskID are currently empty columns and I am looking for a formula * that will populate them from sheet2 Sheet 2 has about 500 unique entries with the same first four columns ProjectID, NewProjectID, TaskID and NewTaskID and all four columns are completed I have extracted every single unique combination from Sheet1 to create sheet 2 and allocated a NewProjectID or NewTaskID. What I am trying to do now is write the formulas for sheet1, NewProjectID so that where sheet1 ProjectID = sheet2 ProjectID and Sheet1 TaskID = sheet2 TaskID then it picks up the NewProjectID in sheet2 and enters it into the corresponding column in sheet1. *I also need to do exactly the same to create a formula to pick up the NewTaskID. I could do all this manually but with 11000 entries it's going to take a considerable time . *The problem is the more I look at it the more confused I have become. *Probably because I am so close to it and I've trying to rationalise all the options for four days Unfortunately the TaskID have about seven different ways of coding and there are nine different methodologies for the projectID which led to so much of my confusion in the first place. Can anyone help, I really am at my wits end. *I am sure it will be a relatively simple formula using something like match and vlookup or something along those lines but my head is a plate of mince and I really have to get this done Any help would be gratefully appreciated Thanks BJthebear Scotland Hello, I would suggest the following : - create an unique identifier for each row of each sheet : for example by concatenating ProjectId and TaskId with a separator (ProjectId &"."&TaskId) - then you can use a vlookup formula to retrieve the information fromm sheet2 to sheet1 using this unique identifier Hope this helps |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Having Problems Excel 2003
I think this sounds like a probable solution.
For BJ&theBear: it'll probably be best to insert a new column A on the sheet you need to get values from using VLOOKUP() on the other sheet. Remember that VLOOKUP() looks at the leftmost column for the match and then pulls data from some column to the right of that one on the matched row. An alternative, if you don't want to insert a new column A, is to use plain old LOOKUP() which lets you lookup from one column and then pull data from one to the left or right of it. In either case, you can use Copy and Paste Special with the Values option selected later to convert the results of the VLOOKUP()s to actual values and do away with any columns you may have added to collate the data. " wrote: On 23 mar, 23:38, "BJ&theBear" wrote: I have been trying to rectify the anomalies in the data in a spreadsheet that has become a mishmash of different Project and Task codes in order to provide a structure so that a final spreadsheet or database can be constructed that makes sense. I have 2 sheets of an Excel 2003 workbook, Sheet 1 has 11000 entries the first four columns are ProjectID, NewProjectID, TaskID and NewTaskID with 10 other columns. NewProjectID and NewTaskID are currently empty columns and I am looking for a formula that will populate them from sheet2 Sheet 2 has about 500 unique entries with the same first four columns ProjectID, NewProjectID, TaskID and NewTaskID and all four columns are completed I have extracted every single unique combination from Sheet1 to create sheet 2 and allocated a NewProjectID or NewTaskID. What I am trying to do now is write the formulas for sheet1, NewProjectID so that where sheet1 ProjectID = sheet2 ProjectID and Sheet1 TaskID = sheet2 TaskID then it picks up the NewProjectID in sheet2 and enters it into the corresponding column in sheet1. I also need to do exactly the same to create a formula to pick up the NewTaskID. I could do all this manually but with 11000 entries it's going to take a considerable time . The problem is the more I look at it the more confused I have become. Probably because I am so close to it and I've trying to rationalise all the options for four days Unfortunately the TaskID have about seven different ways of coding and there are nine different methodologies for the projectID which led to so much of my confusion in the first place. Can anyone help, I really am at my wits end. I am sure it will be a relatively simple formula using something like match and vlookup or something along those lines but my head is a plate of mince and I really have to get this done Any help would be gratefully appreciated Thanks BJthebear Scotland Hello, I would suggest the following : - create an unique identifier for each row of each sheet : for example by concatenating ProjectId and TaskId with a separator (ProjectId &"."&TaskId) - then you can use a vlookup formula to retrieve the information fromm sheet2 to sheet1 using this unique identifier Hope this helps . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Having Problems Excel 2003
On Mar 24, 5:42*am, JLatham wrote:
I think this sounds like a probable solution. For BJ&theBear: *it'll probably be best to insert a new column A on the sheet you need to get values from using VLOOKUP() on the other sheet. * Remember that VLOOKUP() looks at the leftmost column for the match and then pulls data from some column to the right of that one on the matched row. An alternative, if you don't want to insert a new column A, is to use plain old LOOKUP() which lets you lookup from one column and then pull data from one to the left or right of it. In either case, you can use Copy *and *Paste Special with the Values option selected later to convert the results of the VLOOKUP()s to actual values and do away with any columns you may have added to collate the data. " wrote: On 23 mar, 23:38, "BJ&theBear" wrote: I have been trying to rectify the anomalies in the data in a spreadsheet that has become a mishmash of different Project and Task codes in order to provide a structure so that a final spreadsheet or database can be constructed that makes sense. I have 2 sheets of an Excel 2003 workbook, Sheet 1 has 11000 entries the first four columns are ProjectID, NewProjectID, TaskID and NewTaskID with 10 other columns. NewProjectID and NewTaskID are currently empty columns and I am looking for a formula * that will populate them from sheet2 Sheet 2 has about 500 unique entries with the same first four columns ProjectID, NewProjectID, TaskID and NewTaskID and all four columns are completed I have extracted every single unique combination from Sheet1 to create sheet 2 and allocated a NewProjectID or NewTaskID. What I am trying to do now is write the formulas for sheet1, NewProjectID so that where sheet1 ProjectID = sheet2 ProjectID and Sheet1 TaskID = sheet2 TaskID then it picks up the NewProjectID in sheet2 and enters it into the corresponding column in sheet1. *I also need to do exactly the same to create a formula to pick up the NewTaskID. I could do all this manually but with 11000 entries it's going to take a considerable time . *The problem is the more I look at it the more confused I have become. *Probably because I am so close to it and I've trying to rationalise all the options for four days Unfortunately the TaskID have about seven different ways of coding and there are nine different methodologies for the projectID which led to so much of my confusion in the first place. Can anyone help, I really am at my wits end. *I am sure it will be a relatively simple formula using something like match and vlookup or something along those lines but my head is a plate of mince and I really have to get this done Any help would be gratefully appreciated Thanks BJthebear Scotland Hello, I would suggest the following : * * * * * * * * * * * * * * - create an unique identifier for each row of each sheet : for example by concatenating ProjectId and TaskId with a separator (ProjectId &"."&TaskId) * * * * * * * * * * * * * *- then you can use a vlookup formula to retrieve the information fromm sheet2 to sheet1 using this unique identifier Hope this helps .- Hide quoted text - - Show quoted text - Thanks so much for the help - the unique identifier worked a treat and I have managed to get a consistency about the master worksheet that can be used in the future. I will however admit to having one of those (forgive the expression) "blonde" moments when I could not fathom out why it was not concatenating the unique identifier - it kept on showing the formula??!! Silly me had forgotten to turn off show formulas but it took me nearly 3 minutes to think of that Thanks once again for your help I just wish I knew enough to help more of the other users Thanks again BJ the bear |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A set of Excel 2003 problems | New Users to Excel | |||
Excel 2003 Calculation Problems | Excel Discussion (Misc queries) | |||
Autofilter problems in Excel 2003 | Excel Discussion (Misc queries) | |||
Problems with Excel 2003 - Dashboards | Excel Discussion (Misc queries) | |||
Problems with Excel 2003 | Setting up and Configuration of Excel |