Import a Code to a paragraph using a formula or macro!

M

Manos

Dear All

I simple problem in the excel in which i can not find a solution.

i have some date that will exported from a BAAN system.
So in column A i have some codes.
Column B is empty
Column C has a description.

The data as grouped in paragrhs like this:

Manufactured Ite 1 : 9901010020


1111070010 1 SPRING DOOR FCL6501150mm
1133010030 1 GLASS TRIPLE 810X450X28 ISO
1134010020 1 GASKET FV100 849X489 BLK
1135010020 1 ALU SHEET 2000X1000X4
1135020110 1 ALU PR DOOR RED CC 2000 4.4m
1136010160 1 PL PR DOOR COVER BLK 2.70m
1137010070 1 PL.HINGE BEARING RED FLAT
1137030090 1 HANDLE BLK BASE, ROUND BLK
1139040050 1 INSUL.TUBE D10/16.5 ARMOUFLEX
1156010160 1 SLOTTED CHEESE BOLTM6X45 DIN8
1157010200 1 SPARE PARTS BAG 900X800/6
1157020460 1 CARTON BOX 560X100X920
1157022820 1 CARTON BOX 550?80?920
1157022960 1 CARTON SPARES LOW.920X550X60
1157022970 1 CARTON SPARES UP.935X565X60
1157090190 1 AEROPLAST WIDTH 1m, D DBL 10m



Manufactured Ite 1 : 9901020030


1111070010 1 SPRING DOOR FCL6501150mm
1133020040 1 GLASS TRIPLE 1244X440X28 ISO
1134010110 1 GASKET FV280 1278X475 WHT
1135010020 1 ALU SHEET 2000X1000X4
1135020050 1 ALU PR DOOR WHT 4.4m
1136010070 1 PL PR DOOR COVER WHT3m
1137010050 1 PL HINGE BEARING WHTFLAT
1137010060 1 PL.HINGE BEARING BLK FLAT
1137030020 1 HANDLE BLUE BASE,ROUND GREY
1137110010 1 SPRING'S NEST
1139040050 1 INSUL.TUBE D10/16.5 ARMOUFLEX
1156010160 1 SLOTTED CHEESE BOLTM6X45 DIN8
1157010110 1 SPARE PARTS BAG 1600X700/6
1157020470 1 CARTON BOX 600X100X1460
1157022830 1 CARTON BOX 620?80?1480
1157022980 1 CARTON SPARES LOW.1480X620X60
1157022990 1 CARTON SPARES UP.1495X635X60
1157090190 1 AEROPLAST WIDTH 1m, D DBL 10m


As you can see each paragraph has different range.
Te problem is that i want to load the code 9901010020, in column B (column B
is the column with 1),
but when i am goint to second paragraph i want ot chage the code 9901020030
in the nect paragraph.

I can not find something unic in order to prepare a formula with rang and
make copy paste.

Any idea for formulas of macro that will paste in column B the appropriate
code of each paragraph?

Thanks in advance
Manos
 
R

Ron Coderre

Try something like this:
With your data list beginning in Cell A1

B4:
=RIGHT(INDEX($A$1:A3,MAX((LEFT($A$1:A3,12)="Manufactured")*ROW($A$1:A3)),1),10)

Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
press [Enter].

Select B4
Edit>Copy
Select from B5 through the last item in Col_B
Press the [F5] key (that's edit>go to)
Click the [Special...] button
Check: Constants
Click the [OK] button
(that should select all of the 1's)

Press the [OK] key
That should put the formula in all of the cells that require the mfg number
and return the correct mfg number.

If that works...selec the entire column
Edit>Copy
Edit>Paste Special: Values

Adjust range references to suit your situation.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
M

Manos

Dear Ron

Yes that was very helpgull, and probably i am looking for something like
this
But the problem is that i wan to put in column b the code number
Manufactured Ite 1 : 9901010020
and not the code of the first column

In second paragraph the code of the other manufactured
an so on.

Can you help with this?

Thanks in advance
Manos




Ron Coderre said:
Try something like this:
With your data list beginning in Cell A1

B4:
=RIGHT(INDEX($A$1:A3,MAX((LEFT($A$1:A3,12)="Manufactured")*ROW($A$1:A3)),1),10)

Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
press [Enter].

Select B4
Edit>Copy
Select from B5 through the last item in Col_B
Press the [F5] key (that's edit>go to)
Click the [Special...] button
Check: Constants
Click the [OK] button
(that should select all of the 1's)

Press the [OK] key
That should put the formula in all of the cells that require the mfg
number
and return the correct mfg number.

If that works...selec the entire column
Edit>Copy
Edit>Paste Special: Values

Adjust range references to suit your situation.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


Manos said:
Dear All

I simple problem in the excel in which i can not find a solution.

i have some date that will exported from a BAAN system.
So in column A i have some codes.
Column B is empty
Column C has a description.

The data as grouped in paragrhs like this:

Manufactured Ite 1 : 9901010020


1111070010 1 SPRING DOOR FCL6501150mm
1133010030 1 GLASS TRIPLE 810X450X28 ISO
1134010020 1 GASKET FV100 849X489 BLK
1135010020 1 ALU SHEET 2000X1000X4
1135020110 1 ALU PR DOOR RED CC 2000 4.4m
1136010160 1 PL PR DOOR COVER BLK 2.70m
1137010070 1 PL.HINGE BEARING RED FLAT
1137030090 1 HANDLE BLK BASE, ROUND BLK
1139040050 1 INSUL.TUBE D10/16.5 ARMOUFLEX
1156010160 1 SLOTTED CHEESE BOLTM6X45 DIN8
1157010200 1 SPARE PARTS BAG 900X800/6
1157020460 1 CARTON BOX 560X100X920
1157022820 1 CARTON BOX 550?80?920
1157022960 1 CARTON SPARES LOW.920X550X60
1157022970 1 CARTON SPARES UP.935X565X60
1157090190 1 AEROPLAST WIDTH 1m, D DBL 10m



Manufactured Ite 1 : 9901020030


1111070010 1 SPRING DOOR FCL6501150mm
1133020040 1 GLASS TRIPLE 1244X440X28 ISO
1134010110 1 GASKET FV280 1278X475 WHT
1135010020 1 ALU SHEET 2000X1000X4
1135020050 1 ALU PR DOOR WHT 4.4m
1136010070 1 PL PR DOOR COVER WHT3m
1137010050 1 PL HINGE BEARING WHTFLAT
1137010060 1 PL.HINGE BEARING BLK FLAT
1137030020 1 HANDLE BLUE BASE,ROUND GREY
1137110010 1 SPRING'S NEST
1139040050 1 INSUL.TUBE D10/16.5 ARMOUFLEX
1156010160 1 SLOTTED CHEESE BOLTM6X45 DIN8
1157010110 1 SPARE PARTS BAG 1600X700/6
1157020470 1 CARTON BOX 600X100X1460
1157022830 1 CARTON BOX 620?80?1480
1157022980 1 CARTON SPARES LOW.1480X620X60
1157022990 1 CARTON SPARES UP.1495X635X60
1157090190 1 AEROPLAST WIDTH 1m, D DBL 10m


As you can see each paragraph has different range.
Te problem is that i want to load the code 9901010020, in column B
(column B
is the column with 1),
but when i am goint to second paragraph i want ot chage the code
9901020030
in the nect paragraph.

I can not find something unic in order to prepare a formula with rang and
make copy paste.

Any idea for formulas of macro that will paste in column B the
appropriate
code of each paragraph?

Thanks in advance
Manos
Click to expand...
Click to expand...
 
Top