help with importing information between sheets

[watapage]

im very new to excel and trying to create a list that imports certain information from; lets say sheet2 into sheet1 of the same excel file..
the process is as follows..

i have following two sheets:
Sheet1 looks something like:

Number Name
3011010 | 301-10-10 BSP FEMALE
3011210 | 301-12-10 BSP FEMALE
3021010 | 302-10-10 BSP MALE
3021210 | 302-12-10 BSP MALE
3031010 | 303-10-10 BSP 90GR FEMALE
3031210 | 303-12-10 BSP 90GR FEMALE
3041010 | 304-10-10 BSP 45GR FEMALE
3051010 | 305-10-10 BSP 90GR ALBUE

and Sheet2 looks like:

Number Name
PL02-04 | Plasthette BSP 1/4"
PL02-06 | Plasthette BSP 3/8"
PL02-08 | Plasthette BSP 1/2"
PL02-08 | Plasthette BSP 1/2"
PL02-10 | Plasthette BSP 5/8"
PL02-12 | Plasthette BSP 3/4"
PL02-16 | Plasthette BSP 1"
PL02-20 | Plasthette BSP 1-1/4"
PL02-24 | Plasthette BSP 1-1/2"
PL02-32 | Plasthette BSP 2"
PL01-04 | Plastplugg BSP 1/4"
PL01-06 | Plastplugg BSP 3/8"
PL01-08 | Plastplugg BSP 1/2"
PL01-10 | Plastplugg BSP 5/8"
PL01-12 | Plastplugg BSP 3/4"
PL01-16 | Plastplugg BSP 1"
PL01-20 | Plastplugg BSP 1-1/4"
PL01-24 | Plastplugg BSP 1-1/2"
PL01-32 | Plastplugg BSP 2"

what i want to do is:
import Number from sheet2 into sheet1 whith the help of the following rule:
in sheet1 the number column has the follwing form XXXYYZZ
and name has the follwing: something-something-Female or something-something-Male and so on...
in sheet1 the middle two digits and the gender specify the number-column in sheet2, meaning that i want my sheet to look something like:

Number | Name | Number
3011010 | 301-10-10 BSP FEMALE | PL01-10

as you can see that in the last number-column i have something like PLAA-XX where XX are the two middle digits in the name and AA specifies the gender (01 for female and 02 for male)-column..

can anyone help me with a formula or rule?

 

[Domski]

Hi,

There's probably a better way to do this using SUBSTITUTE but this seems to work if I've understand you correctly:

Book1
	A	B	C	D
1	3011010	301-10-10BSPFEMALE	PL01-10	PlastpluggBSP5/8"
2	3011210	301-12-10BSPFEMALE	PL01-12	PlastpluggBSP3/4"
3	3021010	302-10-10BSPMALE	PL02-10	PlasthetteBSP5/8"
4	3021210	302-12-10BSPMALE	PL02-12	PlasthetteBSP3/4"
5	3031010	303-10-10BSP90GRFEMALE	PL01-10	PlastpluggBSP5/8"
6	3031210	303-12-10BSP90GRFEMALE	PL01-12	PlastpluggBSP3/4"
7	3041010	304-10-10BSP45GRFEMALE	PL01-10	PlastpluggBSP5/8"
8	3051010	305-10-10BSP90GRALBUE	PL02-10	PlasthetteBSP5/8"
Sheet1

Hope it helps.

Dom

 

[watapage]

would you please explain the rule..
the values i have posted are afew among 100's and there are other names too like:317-08-04 AISI-316 (which is again a female) or 334-10-04 AISI-316 DKOL 90GRand so on...

and another thing i want to know is, if it is possible to sort the name-column (that looks like 317-08-04 AISI-316 ) according to the middle two digits, i-e sort 317-YY-04 AISI-316 according to the YY..

would be thankful if you can reply me now cuz im working on the files at the moment..

 

[Domski]

The example I gave works on the fact that the last 6 characters of the field are equal to "Female" or not. Sounds like to me you would need a look up table to define in what way the result should be returned.

As far as the sort goes you could use a 'helper' column such as =MID(A1,5,2) to separate the details you wish to sort by. PM me if you want specific assistance as it's difficult to tell from such a brief description what you are trying to achieve.

Alas I'm out on the lash for the next week or so but will try and assist if I can.

Take it easy,

Dom