(Solved) : Imported Data File Product Code First Digit Product Code Numeric Digits Product Code C3972 Q30155118 . . .
Imported Data File
Product code First digit of product code Numeric digits of productcode
C3972
C4758
16332
P3064
7257
O4218
38639
A8218
I3870
G3677
11947
E4374
N2872
D5527
G4254
E7138
E8484
P4987
46561
I3293
K7486
H4300
C2112
M8300
B6342
W6149
A4419
M4455
G2954
L5609
W6726
R9610
S1956
I5069
19033
B5765
D7568
A6918
P2341
G1920
O6570
M6378
N4648
F4684
W5573
E5838
M6955
K9838
K2185
F2761
W4335
D1067
F5260
42220
S6642
D6221
R1872
O1680
R8949
H2376
C9873
G3529
O2257
M7530
N8876
T2989
M6953
R1295
T5488
R2449
K9260
R6450
E4491
H4298
J2568
92605
P1103
W3758
H4728
F7612
A8958
L3071
F7035
E3767
L7960
D4921
D9342
J8921
S4573
A4380
C2650
N2687
I3575
16230
A4957
T7841
N9186
E5690
T7264
S3996
E8189
R5149
R9571
C9150
K7191
N6999
P6951
P9377
K7876
O1532
52649
A5533
P6878
G9991
A4956
W1688
G3491
72841
N7263
E4452
M2493
K2301
M9570
J9606
D1495
I1761
K2878
I5761
Z3972
Z4758
16332
P3064
07257
O4218
38639
A8218
I3873
G3677
H1947
E4374
N2872
D5527
G4254
17138
E8484
P4987
E6561
I3293
37486
H4303
Z2112
M8303
B6342
W6149
A4419
N4455
G2954
L5609
W6726
R9613
41956
I5069
S9033
B5765
D7568
A6918
P2341
G1923
O6573
N6378
N4648
F4684
W5573
E5838
N6955
K9838
L2185
F2761
W4335
21067
F5263
C2223
S6642
D6221
R1872
O1683
R8949
H2376
C9873
G3529
O2257
M7533
N8876
T2989
M6953
R1295
T5488
R2449
L9263
Q6453
E4491
H4298
J2568
A2605
11103
W3758
H4728
F7612
A8958
L3071
F7035
E3767
L7963
D4921
D9342
J8921
S4573
A4383
C2653
N2687
I3575
M6233
A4957
T7841
N9186
E5693
T7264
S3996
E8189
Q5149
Q9571
C9153
K7191
96999
P6951
P9377
K7876
O1532
C2649
A5533
P6878
H9991
A4956
W1688
H3491
T2841
N7263
E4452
M2493
72301
M9573
J9606
D1495
I1761
K2878
I5761
You have gathered from the internet a series of data about someproducts. This data is contained in the Excel workbook calledProblem 5.xlsx. There was some interference during the download andsome part numbers were not downloaded correctly. Valid part numbersstart with an alphabetic character and have four numeric digits,for example “P3064” is a valid part number. a. Use Excel toseparate the part numbers into two columns, one for the firstcharacter the part number and one for the remaining four digits.Place this data in columns B and C of the worksheet. b. Next, usingVBA, in column D of the worksheet, enter “Invalid Part Number” fora row that does not have a valid part number (one that begins witha letter). Change the Fill color to yellow. c. Next, part numbersthat are even numbers are ones the firm produces itself, while onesthat are odd numbers they purchase from an external source. Again,using VBA, for the valid part numbers display in column D, “Ours”for the ones we produce and “Purchase” for the ones we get fromexternal sources. d. Finally, again using VBA, create a display inCells E3:F11 that shows the title in cell E3, the Total Ours =number, Total Purchased = number, and Total Invalid Part Numbers =number (see example below). Hint: You might wish to look atrecorded macros for guidance in VBA Fill, Font and columnadjustment commands. An example showing how the first part of theworksheet will look when your solution is done. The values will, ofcourse, be somewhat different. Save your workbook.
Imported DataFileProduct codeFirst digit of product codeNumeric digits of product codeC3972C475816332P30647257O421838639A8218I3870G367711947E4374N2872D5527G4254E7138E8484P498746561I3293K7486H4300C2112M8300B6342W6149A4419M4455G2954L5609W6726R9610S1956I506919033B5765D7568A6918P2341G1920O6570M6378N4648F4684W5573E5838M6955K9838K2185F2761W4335D1067F526042220S6642D6221R1872O1680R8949H2376C9873G3529O2257M7530N8876T2989M6953R1295T5488R2449K9260R6450E4491H4298J256892605P1103W3758H4728F7612A8958L3071F7035E3767L7960D4921D9342J8921S4573A4380C2650N2687I357516230A4957T7841N9186E5690T7264S3996E8189R5149R9571C9150K7191N6999P6951P9377K7876O153252649A5533P6878G9991A4956W1688G349172841N7263E4452M2493K2301M9570J9606D1495I1761K2878I5761Z3972Z475816332P306407257O421838639A8218I3873G3677H1947E4374N2872D5527G425417138E8484P4987E6561I329337486H4303Z2112M8303B6342W6149A4419N4455G2954L5609W6726R961341956I5069S9033B5765D7568A6918P2341G1923O6573N6378N4648F4684W5573E5838N6955K9838L2185F2761W433521067F5263C2223S6642D6221R1872O1683R8949H2376C9873G3529O2257M7533N8876T2989M6953R1295T5488R2449L9263Q6453E4491H4298J2568A260511103W3758H4728F7612A8958L3071F7035E3767L7963D4921D9342J8921S4573A4383C2653N2687I3575M6233A4957T7841N9186E5693T7264S3996E8189Q5149Q9571C9153K719196999P6951P9377K7876O1532C2649A5533P6878H9991A4956W1688H3491T2841N7263E4452M249372301M9573J9606D1495I1761K2878I5761
Expert Answer
Answer to Imported Data File Product Code First Digit Product Code Numeric Digits Product Code C3972 Q30155118 . . .
OR