Hi everyone ,
I need some help to create VBA code that could replace this formula.
There is a lot of cells that needs to be filled and this formula slows excels by a lot makes it almost unusable.
I couldn't post the whole sheet due to limited number of cells.
I need some help to create VBA code that could replace this formula.
There is a lot of cells that needs to be filled and this formula slows excels by a lot makes it almost unusable.
I couldn't post the whole sheet due to limited number of cells.
| excel.xlsx | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | AU | AV | AW | AX | AY | AZ | BA | BB | BC | BD | BE | BF | BG | BH | BI | BJ | BK | BL | BM | BN | |||
| 2 | Indentification number | ERP system number | Name | Weight | … | Branch | … | Total orders | 4/25/2022 | 4/26/2022 | 4/27/2022 | 4/28/2022 | 4/29/2022 | 4/30/2022 | 5/1/2022 | 5/2/2022 | 5/3/2022 | 5/4/2022 | 5/5/2022 | 5/6/2022 | 5/7/2022 | 5/8/2022 | 5/9/2022 | 5/10/2022 | 5/11/2022 | 5/12/2022 | 5/13/2022 | 5/14/2022 | 5/15/2022 | 5/16/2022 | 5/17/2022 | 5/18/2022 | 5/19/2022 | 5/20/2022 | 5/21/2022 | 5/22/2022 | 5/23/2022 | 5/24/2022 | 5/25/2022 | 5/26/2022 | 5/27/2022 | 5/28/2022 | 5/29/2022 | 5/30/2022 | 5/31/2022 | 6/1/2022 | 6/2/2022 | 6/3/2022 | 6/4/2022 | 6/5/2022 | 6/6/2022 | 6/7/2022 | 6/8/2022 | 6/9/2022 | 6/10/2022 | 6/11/2022 | 6/12/2022 | 6/13/2022 | 6/14/2022 | 6/15/2022 | 6/16/2022 | 6/17/2022 | 6/18/2022 | 6/19/2022 | 6/20/2022 | ||
| 3 | 30031265 | S-AL209620. | Obciążnik 900 kg AL209620 | 905 | 96 | Linia D1 wydział P3 | 0 | #N/A | #N/A | #N/A | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 4 | 30031266 | S-CT01081. | Płyta przy. CT01081 (F-0270) | 526 | 48 | Linia D1 wydział P3 | 0 | #N/A | #N/A | #N/A | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 5 | 30031269 | S-1812944. | Korpus 1812944 | 447 | 48 | Linia D1 wydział P3 | #N/A | #N/A | #N/A | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 6 | 30031272 | S-M954.800.040.061. | Obciążnik M954.800.040.061 600kg | 615 | 48 | Linia D1 wydział P3 | 5 | 24 | 24 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 7 | 30031273 | S-CT01080. | Pokrywa CT01080 | 306 | 48 | Linia D1 wydział P3 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 8 | 30031275 | S-AL209870. | Obciążnik 1500kg AL209870 | 1468 | 120 | Linia D1 wydział P3 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 9 | 30031276 | S-GL315H009. | Pokrywa GL315H009 | 189 | 24 | Linia D1 wydział P3 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 10 | 30031280 | S-GL210H011.0. | Korpus GL210H011.0 | 270 | 24 | Linia D1 wydział P3 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 11 | 30031284 | S-370165H01. | Skrzynia przekładni 370165H01 / FUA0007 | 414 | 24 | Linia D1 wydział P3 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 12 | 30031285 | S-1V10H0457. | Pokrywa pom. 1V10H0457 | 1014 | 36 | Linia D1 wydział P3 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 13 | 30031286 | S-2067664. | Korpus silnika 2067664 | 510 | 36 | Linia D1 wydział P3 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 14 | 30031292 | S-1G-14001U. | Skrzynia przekładni 1G-14001U (A5Z00492835) | 895 | 120 | Linia D1 wydział P3 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 15 | 30031295 | S-1G-30023-C. | Obudowa łożyska 1G-30023-C (A5Z00488478) | 82 | 24 | Linia D1 wydział P3 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 16 | 30031298 | S-452.0921. | Pierścień 452.0921-001 | 376 | 36 | Linia D1 wydział P3 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 17 | 30031299 | S-442.9272. | Pierścień 442.9272 | 319 | 36 | Linia D1 wydział P3 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 18 | 30031300 | S-0000956401D1. | Korpus suportu 0000956401D1 | 85 | 24 | Linia D1 wydział P3 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 19 | 30031301 | S-0000956501-D. | Korpus suportu 0000956501-D | 84 | 24 | Linia D1 wydział P3 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 20 | 30031303 | S-7087519650. | Stojak 7087519650 | 447 | 48 | Linia D1 wydział P3 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 21 | 30031304 | S-ARCH_PAAJ023217. | ARCH Obudowa PAAJ023217 (A5Z00023217) | 48 | 24 | Linia D1 wydział P3 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 22 | 30031305 | S-PAAJ044238. | Pokrywa PAAJ044238 (A5Z00044238) | 92 | 36 | Linia D1 wydział P3 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 23 | 30031306 | S-PAAJ045723. | Pokrywa PAAJ045723 (A5Z00045723) | 46 | 24 | Linia D1 wydział P3 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 24 | 30031307 | S-PAAJ044217. | Pokrywa PAAJ044217 (A5Z00044217) | 72 | 48 | Linia D1 wydział P3 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
MAIN | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J3:K6,L4:L6 | J3 | =INDEX(copy!$C:$C,MATCH(1,(MAIN!$D3=copy!$A:$A)*(MAIN!J$2=copy!$B:$B),0)) |
| L3 | L3 | =INDEX(copy!$C:$C,MATCH(1,(MAIN!$D3=copy!$A:$A)*(MAIN!L$2=copy!$B:$B),0)) |
| I3:I4 | I3 | =AGGREGATE(9,6,J3:BN3) |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| copy!_FilterDatabase | =copy!$A$1:$E$1 | J3:L6 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| E3:E522,F:F | Cell | contains an error | text | NO |
| excel.xlsx | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 1 | Name | Delivery time | Amount | ||
| 2 | Głowica 3.45.261.008.401 | 7/10/2020 | 2 | ||
| 3 | Głowica 3.45.261.008.401 | 8/31/2020 | 4 | ||
| 4 | Głowica 3.45.261.008.401 | 9/30/2020 | 4 | ||
| 5 | Kil SE80 449847 Komplet | 11/9/2021 | 1 | ||
| 6 | Pierścień BG00866668 REV D (BG00907838 REV A) | 2/2/2022 | 1 | ||
| 7 | Wrzeciennik 2042757-13 /M-71000A | 2/18/2022 | 1 | ||
| 8 | Pierścień 933.665.007.3.0 SG | 2/23/2022 | 1 | ||
| 9 | Obudowa 119964650 | 3/4/2022 | 1 | ||
| 10 | Prowadnica 141355650 | 3/8/2022 | 2 | ||
| 11 | Wał główny 702719767 (L-07457) | 3/15/2022 | 4 | ||
| 12 | Korpus GL225H011 | 3/16/2022 | 4 | ||
| 13 | Korpus GL225H011 | 3/16/2022 | 3 | ||
| 14 | Stojak FTAA-60 | 3/18/2022 | 2 | ||
| 15 | Rama 141670650 | 3/21/2022 | 1 | ||
| 16 | Kor. pompy cz.górna L-06790 400LNN725 HYD A- żel. szare A48CL.40 wg ASTM | 3/23/2022 | 1 | ||
| 17 | Kor pompy cz.dolna L-06789 400LNN725 HYD A - żel. szare A48CL.40 wg ASTM | 3/23/2022 | 1 | ||
| 18 | Korpus silnika 26538 | 3/24/2022 | 2 | ||
| 19 | Obciążnik M954.800.040.061 600kg | 4/25/2022 | 5 | ||
| 20 | Obciążnik M954.800.040.061 600kg | 3/28/2022 | 28 | ||
| 21 | Kadłub 18-400-25 | 3/28/2022 | 6 | ||
| 22 | Wirnik 12396-C3T Rm471/ Rp330/ A5-12% | 3/29/2022 | 1 | ||
| 23 | Belka osi XR2238/428A | 3/30/2022 | 4 | ||
| 24 | Obciążnik M954.800.040.061 600kg | 3/30/2022 | 24 | ||
| 25 | Korpus CT01184/1 | 3/31/2022 | 2 | ||
| 26 | Pierścień 131172748 (L-07761) | 3/31/2022 | 3 | ||
| 27 | Kil 460.03.02 MID | 4/5/2022 | 1 | ||
| 28 | Koło 760677 M241 | 4/6/2022 | 44 | ||
| 29 | Stoper 14160 Rm-400/Re-240/A5-12% | 4/8/2022 | 2 | ||
| 30 | Koło M354 971195 | 4/8/2022 | 1 | ||
| 31 | Kadłub 18-400-25 | 4/8/2022 | 10 | ||
| 32 | Kil 460.03.02 MID | 4/8/2022 | 1 | ||
| 33 | Kil SE60 453328 Komplet | 4/11/2022 | 1 | ||
| 34 | Skrzynia przekładni 370165H01 / FUA0007 | 4/13/2022 | 20 | ||
| 35 | Korp. reg 10142610594 | 4/13/2022 | 3 | ||
| 36 | Kil B45T 315216 Komplet | 4/13/2022 | 1 | ||
| 37 | Korpus silnika 26538 | 4/14/2022 | 10 | ||
| 38 | Stopa 22825 | 4/14/2022 | 36 | ||
| 39 | KIL BENTE 28 (28 BEN-CK) | 4/14/2022 | 1 | ||
| 40 | KIL BENTE 28 (28 BEN-CK) | 4/14/2022 | 2 | ||
| 41 | Obciążnik 1500kg AL209870 | 4/15/2022 | 1 | ||
| 42 | Obciążnik M954.800.040.061 600kg | 4/15/2022 | 24 | ||
| 43 | Płyta stołu T-31-01-00-8211 | 4/15/2022 | 2 | ||
| 44 | Kil 460.03.02 MID | 4/15/2022 | 1 | ||
| 45 | Kil 460.03.02 MID | 4/15/2022 | 1 | ||
| 46 | Obciążnik M954.800.040.061 600kg | 4/19/2022 | 24 | ||
| 47 | Obciążnik M954.800.040.061 600kg | 4/19/2022 | 24 | ||
| 48 | Obciążnik M954.800.040.061 600kg | 4/20/2022 | 24 | ||
| 49 | Kil B45F 315286 | 4/20/2022 | 1 | ||
| 50 | Obciążnik M954.800.040.061 600kg | 4/21/2022 | 24 | ||
| 51 | Kil B45T 315216 Komplet | 4/21/2022 | 2 | ||
| 52 | Kor. pompy cz.górna L-06790 400LNN725 HYD A- żel. szare A48CL.40 wg ASTM | 4/22/2022 | 3 | ||
| 53 | Obciążnik M954.800.040.061 600kg | 4/22/2022 | 24 | ||
| 54 | Kor pompy cz.dolna L-06789 400LNN725 HYD A - żel. szare A48CL.40 wg ASTM | 4/22/2022 | 3 | ||
| 55 | Koło Blickle 349 (969429/00) | 4/22/2022 | 10 | ||
| 56 | Kil B46F 405594 | 4/22/2022 | 1 | ||
| 57 | Kadłub CS0-111025 | 4/25/2022 | 5 | ||
| 58 | Kostka 85x180x440 200 | 4/25/2022 | 1 | ||
| 59 | Korpus 046270 (10015251) | 4/25/2022 | 1 | ||
| 60 | Obciążnik 900 kg AL209620 | 4/28/2022 | 44 | ||
| 61 | Obciążnik 1150 kg AL209618 | 4/26/2022 | 15 | ||
| 62 | Koło pasowe ZGR1-660P 01.03.00.0 | 4/26/2022 | 1 | ||
| 63 | Koło pasowe GRP1-550 02.02.00.2 | 4/26/2022 | 1 | ||
| 64 | Obciążnik M954.800.040.061 600kg | 4/26/2022 | 24 | ||
| 65 | Wirnik 000096464-D 457/320/12 | 4/26/2022 | 1 | ||
| 66 | Belka poprzeczna 4812111653 | 4/26/2022 | 3 | ||
| 67 | Wspornik 2745794 LAKIER | 4/27/2022 | 4 | ||
| 68 | Wspornik 2745794 LAKIER | 4/27/2022 | 1 | ||
| 69 | Wspornik 2792415 LAKIER | 4/27/2022 | 2 | ||
| 70 | Wspornik 2792415 LAKIER | 4/27/2022 | 2 | ||
| 71 | Wspornik 2792415 LAKIER | 4/27/2022 | 1 | ||
| 72 | Obciążnik M954.800.040.061 600kg | 4/27/2022 | 24 | ||
| 73 | Korpus CT01208 | 4/28/2022 | 2 | ||
| 74 | Piasta GK-4879/1031693 | 4/28/2022 | 1 | ||
| 75 | Kadłub RZ-414640 | 4/28/2022 | 5 | ||
| 76 | Kadłub CS0-111030 | 4/28/2022 | 5 | ||
| 77 | Obudowa wirnika 14413 Rm464/Rp280/A5=12% | 4/28/2022 | 1 | ||
| 78 | Obudowa wirnika 14413 Rm464/Rp280/A5=12% | 4/28/2022 | 1 | ||
| 79 | Pierścień BG00866668 REV D (BG00907838 REV A) | 4/29/2022 | 10 | ||
| 80 | Pierścień BG00220322 | 4/29/2022 | 4 | ||
| 81 | Pierścień BG00219929 | 4/29/2022 | 5 | ||
| 82 | Tłok BG00220177 | 4/29/2022 | 5 | ||
| 83 | Suwak EN 100/125 (P5305-10) | 4/29/2022 | 1 | ||
| 84 | Obciążnik 1150 kg AL209618 | 4/29/2022 | 3 | ||
| 85 | Obciążnik 1800 kg AL209872 | 4/29/2022 | 3 | ||
| 86 | Pierścień 452.6045-001 | 4/29/2022 | 15 | ||
| 87 | Korpus prasy H1.2010.0161/10 | 4/29/2022 | 1 | ||
| 88 | Wirnik 14159-E 400/340/12 | 4/29/2022 | 1 | ||
| 89 | Kil 222862 | 4/29/2022 | 1 | ||
| 90 | Obciążnik 1150 kg AL209618 | 5/2/2022 | 24 | ||
| 91 | Obciążnik 900 kg AL209620 | 5/2/2022 | 16 | ||
| 92 | Belka poprzeczna 4812111653 | 5/3/2022 | 3 | ||
| 93 | Korpus 610837H01 | 5/4/2022 | 17 | ||
| 94 | Obudowa spir. 9332211420-SG | 5/4/2022 | 4 | ||
| 95 | Koło Blickle 355 (765x920) Linia D1 | 5/4/2022 | 1 | ||
| 96 | Obciążnik M954.800.040.061 600kg | 5/4/2022 | 24 | ||
| 97 | Korpus CT01194/1 | 5/5/2022 | 1 | ||
| 98 | Obciążnik 1500kg AL209870 | 5/5/2022 | 3 | ||
| 99 | Belka poprzeczna 1021849 M-70916A | 5/5/2022 | 3 | ||
| 100 | Wirnik 12396-C3T Rm471/ Rp330/ A5-12% | 5/5/2022 | 1 | ||
| 101 | Obciążnik M954.800.040.061 600kg | 5/6/2022 | 24 | ||
| 102 | Wirnik 282941-C3V Rm400/ Re330/ A5-12% | 5/6/2022 | 1 | ||
| 103 | Wirnik 282941-C3V Rm400/ Re330/ A5-12% | 5/6/2022 | 1 | ||
| 104 | Obudowa wirnika 13780 (000085300) 464/280/12 | 5/6/2022 | 1 | ||
| 105 | Obudowa wirnika 13780 (000085300) 464/280/12 | 5/6/2022 | 1 | ||
| 106 | Obudowa wirnika 12398 464/280/12 | 5/6/2022 | 1 | ||
| 107 | Koło Blickle 349 (969429/00) | 5/6/2022 | 33 | ||
| 108 | Kil 460.03.02 MID | 5/6/2022 | 1 | ||
| 109 | Wirnik L-07415 (201006395) | 5/9/2022 | 2 | ||
| 110 | Pokrywa łożyska 131249967 (L-07674) | 5/9/2022 | 3 | ||
| 111 | Obciążnik 1150 kg AL209618 | 5/9/2022 | 16 | ||
| 112 | Kil B40T 316670 | 5/9/2022 | 1 | ||
| 113 | Kil 460.03.11 SHOAL | 5/9/2022 | 1 | ||
| 114 | Korpus CT01208 | 5/10/2022 | 2 | ||
| 115 | Konik 2025945-13 (M-70992A) | 5/10/2022 | 1 | ||
| 116 | Obciążnik M954.800.040.061 600kg | 5/10/2022 | 24 | ||
| 117 | Belka poprzeczna 4812111653 | 5/10/2022 | 2 | ||
| 118 | Belka poprzeczna 4812111653 | 5/10/2022 | 1 | ||
| 119 | Belka poprzeczna 4812111653 | 5/10/2022 | 3 | ||
| 120 | Belka poprzeczna 4812111653 | 5/10/2022 | 1 | ||
| 121 | Belka poprzeczna 4812114726 | 5/10/2022 | 1 | ||
| 122 | Belka poprzeczna 4812114726 | 5/10/2022 | 3 | ||
| 123 | Wał główny 702719767 (L-07457) | 5/11/2022 | 4 | ||
| 124 | Obciążnik M954.800.040.061 600kg | 5/11/2022 | 24 | ||
| 125 | Korpus silnika 26538 | 5/11/2022 | 10 | ||
| 126 | Stopa 22825 | 5/11/2022 | 40 | ||
| 127 | Korpus CT01209 | 5/12/2022 | 2 | ||
| 128 | Obciążnik 1800 kg AL209872 | 5/12/2022 | 5 | ||
copy | |||||
