Arial Shar 11 A A General Insert - Paste . B I U Wrap Text Merge & Center TO A 47 M M $ - %. Conditional Format as Cell Formatting Table Styles 33% Delete Format Clipboard Sort & Fird & Filter Select Analyz Data Font Alignment Number Styles Cells Editing Analys C31 N P B D F G H 1 12 J L M 13 Open Inventory 14 On Hand Rail Yard On Boat In Quality Mig Qty 15 G3572 2 12 18 28 38 16 D5904 9 19 29 39 49 17 D1476 18 28 38 48 58 18 D0557 41 51 61 71 81 19 C9208 82 92 102 112 122 20 C1319 169 179 189 199 209 21 W0933 338 348 358 368 378 22 W6415 169 179 189 199 209 23 W1051 338 348 358 368 378 169 W1117 24 179 189 209 199 25 26 27 1. From the Inventory above, find the value for each part # that is in transit via boat using Vlookup for the following Rows below: 28 29 Table 2. 30 In Transit 31 C9208 32 D5904 33 G3572 34 W0933 35 D0557 36 W1051 37 Sheet1 Ready EO Type here to search Cells C31 Editing Analysis Sen R 2 May A B D E F G H 37 L . N o P O 38 39 2. After filling the Table 2 above SORT the Row in ascending order 40 41 Containers to West Coast 42 26 Apr 29 Apr 30-Apr 1-May 43 PMA S1898 2 12 22 32 42 44 JMST1607 9 19 29 39 49 45 BMOU1776 18 28 38 48 58 46 JMST1607 41 51 61 71 81 47 PMA S1898 82 92 102 112 122 48 BMOU1776 169 179 189 199 209 49 BMOU1776 338 348 358 368 378 50 PMAS1898 169 179 189 199 209 JMST1607 51 348 358 378 368 169 UMTS1911 52 179 199 189 209 53 54 55 3. Using the Containers to West Coast schedule, find the total number of pieces due to the West Coast on 4/30 for the following ROWs below (Table 4) using SUMIF functions: 56 57 Table 4 58 4/30 59 PMA S1898 60 BMOU1776 61 JMST1607 62 UMTS1911 338 CP Sheet1 Ready 50 o Type here to search Arial Wrap Text General AY Paste BIU -A + E Merge & Center Insert Delete - and Format $ -% -99 Conditional Format as Cell Formatting Table Styles Styles Z Sort & Fine Filter Sele Clipboard Font Alignment Number Cells Editing C31 B D E F G H 1 J L M N o P rom the Open lerenlory table abowe, find how many parts are scheduled to be produced using Meniup for the following News telom Mfg Qty 64 65 66 67 Table 5 68 69 70 71 72 73 74 W0933 D5904 W1117 C9208 W6415 D0557 75 5. From the Open Inventory table, try to get the TOTAL # of open parts for each of the ROWs below: (Use Sum function) Total 76 77 78 79 Table 6 80 81 82 83 84 85 86 87 88 89 an G3572 D5904 D1476 D0557 C9208 C1319 W0933 W6415 W1117 W1051 Sheet1 SON X Ready Ela O here to search HULUSVE MTSU Excel Test (1) - Excel O Search File Home Insert Page Layout Formulas Abdirahman Mohamed Data Review View Developer Help Arial Share Comments - A A 11 as Wrap Text General Insert Paste B I U a. A F AY O Merge & Center 10% Delete $ % 9 OAM Conditional Format as Cell Formatting Table Styles Styles Format Sort & Find 8 Filter Select Clipboard Analyze Sensitivity Data Font Alignment Number Cells Editing Analysis Sensitivity C31 : N lo R S 100 B D E F 89 G W1051 1 K L M 90 91 92 6. Filter the parts in the Re Worked Parts table below that will show 2001 BOBB only. (Don't remove the filter once you are done) 93 94 Re-Worked Parts 95 Inspection Water Dmg Wrong Clip Connector Lost 96 2001 ESSR 2 12 22 32 42 97 2001 PDQU 9 19 29 39 49 98 2001 BQBB 18 28 38 48 58 99 2001 PDQU 41 51 61 71 81 2001 ESSR 82 92 102 112 122 101 2001 BQBB 169 179 189 199 209 102 2001 BQBB 338 348 358 368 378 103 2001 ESSR 169 179 209 199 104 2001 PDQU 338 348 378 368 2001 BQBB 169 105 199 179 209 106 107 108 7. Use Table 8 and create a Pivot table in cell G114 showing the due date of each pes with the following specifications: 109 110 Rows Part Number 111 Columns Date QTY Body 112 113 Date Day QTY Part Number 114 Table 8 in EC Sheet1 189 358 189 20 2016.10.1 anDnAIT 7:47 F 3/31/2 JUNI Ready EO Clipboard k! Font Formatting Table Styles Format Alignment Filter Select Number Styles Cells Editing C31 fax B D E F H J K M N O Day A 113 114 Table 8 115 116 117 118 119 120 121 122 123 124 125 126 127 128 Part Number 2001 PDQU WED 2001 AMTQ WED 2001 PDQU THU 2001 BQBB THU 2001 PDQU FRI 2001 AMTQ FRI 2001 PDQU SAT 2001 BQBB SAT 2001 CQBQ SAT 2001 AMTQ SAT 2001 PDQU MON 2001 AMTQ MON 2001 PDQU TUE 2001 BQBB TUE 2001 PDQU WED 2001 BQBB WED 2001 CQBQ WED 2001 AMTQ WED 2001 PDU THU 2001 BQBB THU 2001 CQBQ THU 2001 AMTQ THU 2001 PDQU FRI 2001 BOBB FRI 2001 COBO FRI Sheet1 Date 08/15/18 08/15/18 08/16/18 08/16/18 08/17/18 08/17/18 08/18/18 08/18/18 08/18/18 08/18/18 08/20/18 08/20/18 08/21/18 08/21/18 08/22/18 08/22/18 08/22/18 08/22/18 08/23/18 08/23/18 08/23/18 08/23/18 08/24/18 08/24/18 08/24/18 QTY 36 36 36 36 36 36 36 36 36 36 36 36 36 36 36 36 36 36 72 36 72 36 72 72 72 129 130 131 132 133 134 135 136 137 138 139 Ready o Type here to search C31 : X A F G H 1 J K M N O P 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 36 72 36 72 72 72 72 0 370 24 1044 252 0 0 B 2001 BQBB THU 2001 CQBO THU 2001 AMIC THU 2001 PDQU FRI 2001 BQBB FRI 2001 CQBQ FRI 2001 AMTQ FRI 2001 ESSR SAT 2001 PDQU SAT 2001 BQBB SAT 2001 CQBQ SAT 2001 AMTQ SAT 2001 ESSR MON 2001 PDQU MON 2001 BQBB MON 2001 CQBQ MON 2001 AMTQ MON 2001 ESSR THU 2001 PDU THU 2001 BQBB THU 2001 CQBQ THU 2001 AMTQ THU 2001 ESSR SAT 2001 PDQU SAT 2001 BQBB SAT 2001 CQBQ SAT 2001 AMTQ SAT Sheet1 D 08/23/18 08/23/18 08/23/18 08/24/18 08/24/18 08/24/18 08/24/18 09/01/18 09/01/18 09/01/18 09/01/18 09/01/18 10/01/18 10/01/18 10/01/18 10/01/18 10/01/18 11/01/18 11/01/18 11/01/18 11/01/18 11/01/18 12/01/18 12/01/18 12/01/18 12/01/18 12/18/18) 0 864 288 Pool 540 324 0 0 0 370 136 160 Ready Ela in Excel Search File Home Insert Page Layout Formulas Data A Abdirahman Mohamed Review View Developer Help Arial 11 - A Share Comments as Wrap Text Paste General BIU A 47 Merge & Center $ -% Insert Delete Format Clipboard Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Font Analyze Data Sensitivity Alignment Number Cells Editing C31 Analysis Sensitivity A B D E F G H 1 J K L M N O R S 8. Using Table 8 sort this table by part number 9. Using Table 8 determine the total of the part number below using SUMIF Total Part Number 2001 PDQU 2001 AMTQ 2001 BQBB 2001 CQBQ 2001 ESSR 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 10. Using the PIVOT table that you created in question 7, get the QTY for the month of October using VLOOKUP functions Oct QTY Part Number 2001 PDQU 2001 AMTQ 2001 BOBB 2001 CQBQ 2001 ESSR + e Sheet1 00 7:49 PM 3/31/2021 Ready 10 O O Type here to search