|
Import a Code to a paragraph using a formula or macro!
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 |
Import a Code to a paragraph using a formula or macro!
Try something like this:
With your data list beginning in Cell A1 B4: =RIGHT(INDEX($A$1:A3,MAX((LEFT($A$1:A3,12)="Manufa ctured")*ROW($A$1:A3)),1),10) Note: Commit that array formula by holding down the [Ctrl][Shift] keys and press [Enter]. Select B4 EditCopy Select from B5 through the last item in Col_B Press the [F5] key (that's editgo 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 EditCopy EditPaste Special: Values Adjust range references to suit your situation. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Manos" wrote: 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 |
Import a Code to a paragraph using a formula or macro!
Hi Manos i wish i some solutions to give but i don't understand it at all.
"Manos" wrote: 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 |
Import a Code to a paragraph using a formula or macro!
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" wrote in message ... Try something like this: With your data list beginning in Cell A1 B4: =RIGHT(INDEX($A$1:A3,MAX((LEFT($A$1:A3,12)="Manufa ctured")*ROW($A$1:A3)),1),10) Note: Commit that array formula by holding down the [Ctrl][Shift] keys and press [Enter]. Select B4 EditCopy Select from B5 through the last item in Col_B Press the [F5] key (that's editgo 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 EditCopy EditPaste Special: Values Adjust range references to suit your situation. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Manos" wrote: 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 |
| All times are GMT +1. The time now is 10:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com