Help with Lookup, Please!
 

I have imported some data into excel from an Oracle database and I
can't determine how to re-organize the data so I can plot it. Here is
how the data comes in:

BASE_ID RESOURCE_ID RUN_HRS
060421 ASSY 961
060421 ASSY 0
060421 ASSY 0
060421 DISASSEMBLY 33
060421 DOCCONTROL 45.7
060421 ELEC 364.5
060421 ASSY 0
060421 FORE 182.5
060421 INST 483.5
060421 ASSY 0
060421 JWELD 185.5
060422 ASSY 873
060422 ASSY 0
060422 ASSY 0
060422 DISASSEMBLY 10.5
060422 ASSY 0
060422 DOCCONTROL 23.25
060422 E&I SPECIALIST 7
060422 ELEC 250
......
....
...

I'm trying to re-organize in this manner (without the zero hour rows
and without duplicating):

060421 060422
ASSY 961 873
DISASSEMBLY 33 10.5
DOCCONTROL 45.7 23.25
E&I SPECIALIST 0 7
ELEC 364.5 250
FORE 182.5 etc.
INST 483.5
JWELD 185.5
PAINT 165.5
PRODHRS 8.5
(It's important to note that there was no E&I SPECIALIST in the
imported data for 060421).

First, I've created a macro to return the unique entries in Column A
(ok, I confess - I found one from a newsgroup and copied it!). Now,
I'm trying to use a lookup to find the RUN_HRS for each RESOURCE_ID and
copy it under each BASE_ID in my reorganized data. I'm not sure how to
go about it!

Is there a way to do a "double-lookup", whereby, I lookup the BASE_ID
in the imported data, then lookup the RESOURCE_ID that matches the
BASE_ID and have the formula return the RUN_HRS? I'd prefer to use a
formula or macro on the data, rather than filtering the data and
copy-pasting.

Finally, will this double-lookup skip the rows that have 0 RUN_HRS, or
am I best to delete those rows right after import? (I'm leaning toward
this solution).

Thanks,
Armand

Help with Lookup, Please!
 

Much easier would be to just do a Pivot Table to get exactly what you want...
Place your cursor in the middle of the data set and select (Data - Pivot
Table) A wizard will open up. You can in all likelyhood just hit finish and
it will make all of the correct guesses for you. A new tab will be created
with a pivot table on it... Drag the Base ID to the top row and the Resource
ID to the left column. Now put the Run Hours in the middle and... Tada...
--
HTH...

Jim Thomlinson


"Armand" wrote:

I have imported some data into excel from an Oracle database and I
can't determine how to re-organize the data so I can plot it. Here is
how the data comes in:

BASE_ID RESOURCE_ID RUN_HRS
060421 ASSY 961
060421 ASSY 0
060421 ASSY 0
060421 DISASSEMBLY 33
060421 DOCCONTROL 45.7
060421 ELEC 364.5
060421 ASSY 0
060421 FORE 182.5
060421 INST 483.5
060421 ASSY 0
060421 JWELD 185.5
060422 ASSY 873
060422 ASSY 0
060422 ASSY 0
060422 DISASSEMBLY 10.5
060422 ASSY 0
060422 DOCCONTROL 23.25
060422 E&I SPECIALIST 7
060422 ELEC 250
......
....
...

I'm trying to re-organize in this manner (without the zero hour rows
and without duplicating):

060421 060422
ASSY 961 873
DISASSEMBLY 33 10.5
DOCCONTROL 45.7 23.25
E&I SPECIALIST 0 7
ELEC 364.5 250
FORE 182.5 etc.
INST 483.5
JWELD 185.5
PAINT 165.5
PRODHRS 8.5
(It's important to note that there was no E&I SPECIALIST in the
imported data for 060421).

First, I've created a macro to return the unique entries in Column A
(ok, I confess - I found one from a newsgroup and copied it!). Now,
I'm trying to use a lookup to find the RUN_HRS for each RESOURCE_ID and
copy it under each BASE_ID in my reorganized data. I'm not sure how to
go about it!

Is there a way to do a "double-lookup", whereby, I lookup the BASE_ID
in the imported data, then lookup the RESOURCE_ID that matches the
BASE_ID and have the formula return the RUN_HRS? I'd prefer to use a
formula or macro on the data, rather than filtering the data and
copy-pasting.

Finally, will this double-lookup skip the rows that have 0 RUN_HRS, or
am I best to delete those rows right after import? (I'm leaning toward
this solution).

Thanks,
Armand

Help with Lookup, Please!
 

Jim,

Well, that was just far too easy .... there must be a way to make it
more complicated, isn't there!?!? ;-)

Thanks very much, this seems to work just fine!

Armand






Jim Thomlinson wrote:
Much easier would be to just do a Pivot Table to get exactly what you want...
Place your cursor in the middle of the data set and select (Data - Pivot
Table) A wizard will open up. You can in all likelyhood just hit finish and
it will make all of the correct guesses for you. A new tab will be created
with a pivot table on it... Drag the Base ID to the top row and the Resource
ID to the left column. Now put the Run Hours in the middle and... Tada...
--
HTH...

Jim Thomlinson


"Armand" wrote:

I have imported some data into excel from an Oracle database and I
can't determine how to re-organize the data so I can plot it. Here is
how the data comes in:

BASE_ID RESOURCE_ID RUN_HRS
060421 ASSY 961
060421 ASSY 0
060421 ASSY 0
060421 DISASSEMBLY 33
060421 DOCCONTROL 45.7
060421 ELEC 364.5
060421 ASSY 0
060421 FORE 182.5
060421 INST 483.5
060421 ASSY 0
060421 JWELD 185.5
060422 ASSY 873
060422 ASSY 0
060422 ASSY 0
060422 DISASSEMBLY 10.5
060422 ASSY 0
060422 DOCCONTROL 23.25
060422 E&I SPECIALIST 7
060422 ELEC 250
......
....
...

I'm trying to re-organize in this manner (without the zero hour rows
and without duplicating):

060421 060422
ASSY 961 873
DISASSEMBLY 33 10.5
DOCCONTROL 45.7 23.25
E&I SPECIALIST 0 7
ELEC 364.5 250
FORE 182.5 etc.
INST 483.5
JWELD 185.5
PAINT 165.5
PRODHRS 8.5
(It's important to note that there was no E&I SPECIALIST in the
imported data for 060421).

First, I've created a macro to return the unique entries in Column A
(ok, I confess - I found one from a newsgroup and copied it!). Now,
I'm trying to use a lookup to find the RUN_HRS for each RESOURCE_ID and
copy it under each BASE_ID in my reorganized data. I'm not sure how to
go about it!

Is there a way to do a "double-lookup", whereby, I lookup the BASE_ID
in the imported data, then lookup the RESOURCE_ID that matches the
BASE_ID and have the formula return the RUN_HRS? I'd prefer to use a
formula or macro on the data, rather than filtering the data and
copy-pasting.

Finally, will this double-lookup skip the rows that have 0 RUN_HRS, or
am I best to delete those rows right after import? (I'm leaning toward
this solution).

Thanks,
Armand