Having Problems Excel 2003

[BJ&theBear]

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

 

[new1]

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

new1@[no/spam]realce.net

 

[JLatham]

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.

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

new1@[no/spam]realce.net
.

 

[BJ&theBear]

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.

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

new1@[no/spam]realce.net
.- 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