software

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 : 99010100201111070010 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 : 99010200301111070010 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 10mAs 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
Try something like this:
With your data list beginning in Cell A1

B4:
=RIGHT(INDEX($A$1:A3,MAX((LEFT($A$1:A3,12)=quot;Manufa cturedquot;)*ROW($A$1:A3)),1),10)

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

Select B4
Editgt;Copy
Select from B5 through the last item in Col_B
Press the [F5] key (that's editgt;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
Editgt;Copy
Editgt;Paste Special: Values

Adjust range references to suit your situation.

Does that help?

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

XL2002, WinXP-Proquot;Manosquot; wrote:

gt; Dear All
gt;
gt; I simple problem in the excel in which i can not find a solution.
gt;
gt; i have some date that will exported from a BAAN system.
gt; So in column A i have some codes.
gt; Column B is empty
gt; Column C has a description.
gt;
gt; The data as grouped in paragrhs like this:
gt;
gt; Manufactured Ite 1 : 9901010020
gt;
gt;
gt; 1111070010 1 SPRING DOOR FCL6501150mm
gt; 1133010030 1 GLASS TRIPLE 810X450X28 ISO
gt; 1134010020 1 GASKET FV100 849X489 BLK
gt; 1135010020 1 ALU SHEET 2000X1000X4
gt; 1135020110 1 ALU PR DOOR RED CC 2000 4.4m
gt; 1136010160 1 PL PR DOOR COVER BLK 2.70m
gt; 1137010070 1 PL.HINGE BEARING RED FLAT
gt; 1137030090 1 HANDLE BLK BASE, ROUND BLK
gt; 1139040050 1 INSUL.TUBE D10/16.5 ARMOUFLEX
gt; 1156010160 1 SLOTTED CHEESE BOLTM6X45 DIN8
gt; 1157010200 1 SPARE PARTS BAG 900X800/6
gt; 1157020460 1 CARTON BOX 560X100X920
gt; 1157022820 1 CARTON BOX 550?80?920
gt; 1157022960 1 CARTON SPARES LOW.920X550X60
gt; 1157022970 1 CARTON SPARES UP.935X565X60
gt; 1157090190 1 AEROPLAST WIDTH 1m, D DBL 10m
gt;
gt;
gt;
gt; Manufactured Ite 1 : 9901020030
gt;
gt;
gt; 1111070010 1 SPRING DOOR FCL6501150mm
gt; 1133020040 1 GLASS TRIPLE 1244X440X28 ISO
gt; 1134010110 1 GASKET FV280 1278X475 WHT
gt; 1135010020 1 ALU SHEET 2000X1000X4
gt; 1135020050 1 ALU PR DOOR WHT 4.4m
gt; 1136010070 1 PL PR DOOR COVER WHT3m
gt; 1137010050 1 PL HINGE BEARING WHTFLAT
gt; 1137010060 1 PL.HINGE BEARING BLK FLAT
gt; 1137030020 1 HANDLE BLUE BASE,ROUND GREY
gt; 1137110010 1 SPRING'S NEST
gt; 1139040050 1 INSUL.TUBE D10/16.5 ARMOUFLEX
gt; 1156010160 1 SLOTTED CHEESE BOLTM6X45 DIN8
gt; 1157010110 1 SPARE PARTS BAG 1600X700/6
gt; 1157020470 1 CARTON BOX 600X100X1460
gt; 1157022830 1 CARTON BOX 620?80?1480
gt; 1157022980 1 CARTON SPARES LOW.1480X620X60
gt; 1157022990 1 CARTON SPARES UP.1495X635X60
gt; 1157090190 1 AEROPLAST WIDTH 1m, D DBL 10m
gt;
gt;
gt; As you can see each paragraph has different range.
gt; Te problem is that i want to load the code 9901010020, in column B (column B
gt; is the column with 1),
gt; but when i am goint to second paragraph i want ot chage the code 9901020030
gt; in the nect paragraph.
gt;
gt; I can not find something unic in order to prepare a formula with rang and
gt; make copy paste.
gt;
gt; Any idea for formulas of macro that will paste in column B the appropriate
gt; code of each paragraph?
gt;
gt; Thanks in advance
gt; Manos
gt;
gt;
gt;

Hi Manos i wish i some solutions to give but i don't understand it at all.

quot;Manosquot; wrote:

gt; Dear All
gt;
gt; I simple problem in the excel in which i can not find a solution.
gt;
gt; i have some date that will exported from a BAAN system.
gt; So in column A i have some codes.
gt; Column B is empty
gt; Column C has a description.
gt;
gt; The data as grouped in paragrhs like this:
gt;
gt; Manufactured Ite 1 : 9901010020
gt;
gt;
gt; 1111070010 1 SPRING DOOR FCL6501150mm
gt; 1133010030 1 GLASS TRIPLE 810X450X28 ISO
gt; 1134010020 1 GASKET FV100 849X489 BLK
gt; 1135010020 1 ALU SHEET 2000X1000X4
gt; 1135020110 1 ALU PR DOOR RED CC 2000 4.4m
gt; 1136010160 1 PL PR DOOR COVER BLK 2.70m
gt; 1137010070 1 PL.HINGE BEARING RED FLAT
gt; 1137030090 1 HANDLE BLK BASE, ROUND BLK
gt; 1139040050 1 INSUL.TUBE D10/16.5 ARMOUFLEX
gt; 1156010160 1 SLOTTED CHEESE BOLTM6X45 DIN8
gt; 1157010200 1 SPARE PARTS BAG 900X800/6
gt; 1157020460 1 CARTON BOX 560X100X920
gt; 1157022820 1 CARTON BOX 550?80?920
gt; 1157022960 1 CARTON SPARES LOW.920X550X60
gt; 1157022970 1 CARTON SPARES UP.935X565X60
gt; 1157090190 1 AEROPLAST WIDTH 1m, D DBL 10m
gt;
gt;
gt;
gt; Manufactured Ite 1 : 9901020030
gt;
gt;
gt; 1111070010 1 SPRING DOOR FCL6501150mm
gt; 1133020040 1 GLASS TRIPLE 1244X440X28 ISO
gt; 1134010110 1 GASKET FV280 1278X475 WHT
gt; 1135010020 1 ALU SHEET 2000X1000X4
gt; 1135020050 1 ALU PR DOOR WHT 4.4m
gt; 1136010070 1 PL PR DOOR COVER WHT3m
gt; 1137010050 1 PL HINGE BEARING WHTFLAT
gt; 1137010060 1 PL.HINGE BEARING BLK FLAT
gt; 1137030020 1 HANDLE BLUE BASE,ROUND GREY
gt; 1137110010 1 SPRING'S NEST
gt; 1139040050 1 INSUL.TUBE D10/16.5 ARMOUFLEX
gt; 1156010160 1 SLOTTED CHEESE BOLTM6X45 DIN8
gt; 1157010110 1 SPARE PARTS BAG 1600X700/6
gt; 1157020470 1 CARTON BOX 600X100X1460
gt; 1157022830 1 CARTON BOX 620?80?1480
gt; 1157022980 1 CARTON SPARES LOW.1480X620X60
gt; 1157022990 1 CARTON SPARES UP.1495X635X60
gt; 1157090190 1 AEROPLAST WIDTH 1m, D DBL 10m
gt;
gt;
gt; As you can see each paragraph has different range.
gt; Te problem is that i want to load the code 9901010020, in column B (column B
gt; is the column with 1),
gt; but when i am goint to second paragraph i want ot chage the code 9901020030
gt; in the nect paragraph.
gt;
gt; I can not find something unic in order to prepare a formula with rang and
gt; make copy paste.
gt;
gt; Any idea for formulas of macro that will paste in column B the appropriate
gt; code of each paragraph?
gt;
gt; Thanks in advance
gt; Manos
gt;
gt;
gt;

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

quot;Ron Coderrequot; gt; wrote in message
...
gt; Try something like this:
gt; With your data list beginning in Cell A1
gt;
gt; B4:
gt; =RIGHT(INDEX($A$1:A3,MAX((LEFT($A$1:A3,12)=quot;Manufa cturedquot;)*ROW($A$1:A3)),1),10)
gt;
gt; Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
gt; press [Enter].
gt;
gt; Select B4
gt; Editgt;Copy
gt; Select from B5 through the last item in Col_B
gt; Press the [F5] key (that's editgt;go to)
gt; Click the [Special...] button
gt; Check: Constants
gt; Click the [OK] button
gt; (that should select all of the 1's)
gt;
gt; Press the [OK] key
gt; That should put the formula in all of the cells that require the mfg
gt; number
gt; and return the correct mfg number.
gt;
gt; If that works...selec the entire column
gt; Editgt;Copy
gt; Editgt;Paste Special: Values
gt;
gt; Adjust range references to suit your situation.
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Manosquot; wrote:
gt;
gt;gt; Dear All
gt;gt;
gt;gt; I simple problem in the excel in which i can not find a solution.
gt;gt;
gt;gt; i have some date that will exported from a BAAN system.
gt;gt; So in column A i have some codes.
gt;gt; Column B is empty
gt;gt; Column C has a description.
gt;gt;
gt;gt; The data as grouped in paragrhs like this:
gt;gt;
gt;gt; Manufactured Ite 1 : 9901010020
gt;gt;
gt;gt;
gt;gt; 1111070010 1 SPRING DOOR FCL6501150mm
gt;gt; 1133010030 1 GLASS TRIPLE 810X450X28 ISO
gt;gt; 1134010020 1 GASKET FV100 849X489 BLK
gt;gt; 1135010020 1 ALU SHEET 2000X1000X4
gt;gt; 1135020110 1 ALU PR DOOR RED CC 2000 4.4m
gt;gt; 1136010160 1 PL PR DOOR COVER BLK 2.70m
gt;gt; 1137010070 1 PL.HINGE BEARING RED FLAT
gt;gt; 1137030090 1 HANDLE BLK BASE, ROUND BLK
gt;gt; 1139040050 1 INSUL.TUBE D10/16.5 ARMOUFLEX
gt;gt; 1156010160 1 SLOTTED CHEESE BOLTM6X45 DIN8
gt;gt; 1157010200 1 SPARE PARTS BAG 900X800/6
gt;gt; 1157020460 1 CARTON BOX 560X100X920
gt;gt; 1157022820 1 CARTON BOX 550?80?920
gt;gt; 1157022960 1 CARTON SPARES LOW.920X550X60
gt;gt; 1157022970 1 CARTON SPARES UP.935X565X60
gt;gt; 1157090190 1 AEROPLAST WIDTH 1m, D DBL 10m
gt;gt;
gt;gt;
gt;gt;
gt;gt; Manufactured Ite 1 : 9901020030
gt;gt;
gt;gt;
gt;gt; 1111070010 1 SPRING DOOR FCL6501150mm
gt;gt; 1133020040 1 GLASS TRIPLE 1244X440X28 ISO
gt;gt; 1134010110 1 GASKET FV280 1278X475 WHT
gt;gt; 1135010020 1 ALU SHEET 2000X1000X4
gt;gt; 1135020050 1 ALU PR DOOR WHT 4.4m
gt;gt; 1136010070 1 PL PR DOOR COVER WHT3m
gt;gt; 1137010050 1 PL HINGE BEARING WHTFLAT
gt;gt; 1137010060 1 PL.HINGE BEARING BLK FLAT
gt;gt; 1137030020 1 HANDLE BLUE BASE,ROUND GREY
gt;gt; 1137110010 1 SPRING'S NEST
gt;gt; 1139040050 1 INSUL.TUBE D10/16.5 ARMOUFLEX
gt;gt; 1156010160 1 SLOTTED CHEESE BOLTM6X45 DIN8
gt;gt; 1157010110 1 SPARE PARTS BAG 1600X700/6
gt;gt; 1157020470 1 CARTON BOX 600X100X1460
gt;gt; 1157022830 1 CARTON BOX 620?80?1480
gt;gt; 1157022980 1 CARTON SPARES LOW.1480X620X60
gt;gt; 1157022990 1 CARTON SPARES UP.1495X635X60
gt;gt; 1157090190 1 AEROPLAST WIDTH 1m, D DBL 10m
gt;gt;
gt;gt;
gt;gt; As you can see each paragraph has different range.
gt;gt; Te problem is that i want to load the code 9901010020, in column B
gt;gt; (column B
gt;gt; is the column with 1),
gt;gt; but when i am goint to second paragraph i want ot chage the code
gt;gt; 9901020030
gt;gt; in the nect paragraph.
gt;gt;
gt;gt; I can not find something unic in order to prepare a formula with rang and
gt;gt; make copy paste.
gt;gt;
gt;gt; Any idea for formulas of macro that will paste in column B the
gt;gt; appropriate
gt;gt; code of each paragraph?
gt;gt;
gt;gt; Thanks in advance
gt;gt; Manos
gt;gt;
gt;gt;
gt;gt;