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

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

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