Description
Complete the file below
Unformatted Attachment Preview
Complete the SIX following tasks:
1) Convert the data range below to a table; name the table ‘Product_Table’
2) Remove duplicate records that match on SupplierID and ProductName
3) Insert a column to the right of the UnitsInStock column; add the label (in cell H9) Supplier Total Units
Use a SUMIF function call WITH STRUCTURAL REFERENCES in the new column (H) to sum the UnitsinStock for e
4) Use the ‘Custom Sort’ dialog box to sort the table by CategoryID (smallest to largest), and within CategoryID b
5) Filter the table to display records only where SupplierID equals 2 or equals 5.
ProductID
ProductName
1 Chai
2 Chang
3 Aniseed Syrup
4 Chef Anton’s Cajun Seasoning
5 Chef Anton’s Gumbo Mix
6 Grandma’s Boysenberry Spread
7 Uncle Bob’s Organic Dried Pears
8 Northwoods Cranberry Sauce
9 Mishi Kobe Niku
10 Ikura
11 Queso Cabrales
12 Queso Manchego La Pastora
13 Konbu
14 Tofu
15 Genen Shouyu
16 Pavlova
17 Alice Mutton
18 Carnarvon Tigers
19 Teatime Chocolate Biscuits
20 Sir Rodney’s Marmalade
21 Sir Rodney’s Scones
22 Gustaf’s Knäckebröd
23 Tunnbröd
24 Guaraná Fantástica
25 NuNuCa Nuß-Nougat-Creme
26 Gumbär Gummibärchen
27 Schoggi Schokolade
28 Rössle Sauerkraut
29 Thüringer Rostbratwurst
30 Nord-Ost Matjeshering
31 Gorgonzola Telino
32 Mascarpone Fabioli
33 Geitost
34 Sasquatch Ale
SupplierID
CategoryID
1
1
1
2
2
3
3
3
4
4
5
5
6
6
6
7
7
7
8
8
8
9
9
10
11
11
11
12
12
13
14
14
15
16
QuantityPerUnit
1 10 boxes x 20 bags
1 24 – 12 oz bottles
2 12 – 550 ml bottles
2 48 – 6 oz jars
2 36 boxes
2 12 – 8 oz jars
7 12 – 1 lb pkgs.
2 12 – 12 oz jars
6 18 – 500 g pkgs.
8 12 – 200 ml jars
4 1 kg pkg.
4 10 – 500 g pkgs.
8 2 kg box
7 40 – 100 g pkgs.
2 24 – 250 ml bottles
3 32 – 500 g boxes
6 20 – 1 kg tins
8 16 kg pkg.
3 10 boxes x 12 pieces
3 30 gift boxes
3 24 pkgs. x 4 pieces
5 24 – 500 g pkgs.
5 12 – 250 g pkgs.
1 12 – 355 ml cans
3 20 – 450 g glasses
3 100 – 250 g bags
3 100 – 100 g pieces
7 25 – 825 g cans
6 50 bags x 30 sausgs.
8 10 – 200 g glasses
4 12 – 100 g pkgs
4 24 – 200 g pkgs.
4 500 g
1 24 – 12 oz bottles
35 Steeleye Stout
36 Inlagd Sill
37 Gravad lax
78 Mishi Kobe Niku
38 Côte de Blaye
39 Chartreuse verte
40 Boston Crab Meat
41 Jack’s New England Clam Chowder
42 Singaporean Hokkien Fried Mee
43 Ipoh Coffee
44 Gula Malacca
45 Røgede sild
46 Spegesild
47 Zaanse koeken
48 Chocolade
49 Maxilaku
79 Gravad lax
50 Valkoinen suklaa
51 Manjimup Dried Apples
52 Filo Mix
53 Perth Pasties
54 Tourtière
55 Pâté chinois
56 Gnocchi di nonna Alice
57 Ravioli Angelo
58 Escargots de Bourgogne
59 Raclette Courdavault
60 Camembert Pierrot
61 Sirop d’érable
62 Tarte au sucre
63 Vegie-spread
64 Wimmers gute Semmelknödel
65 Louisiana Fiery Hot Pepper Sauce
66 Louisiana Hot Spiced Okra
67 Laughing Lumberjack Lager
68 Scottish Longbreads
69 Gudbrandsdalsost
70 Outback Lager
71 Fløtemysost
72 Mozzarella di Giovanni
73 Röd Kaviar
74 Longlife Tofu
75 Rhönbräu Klosterbier
16
17
17
4
18
18
19
19
20
20
20
21
21
22
22
23
17
23
24
24
24
25
25
26
26
27
28
28
29
29
7
12
2
2
16
8
15
7
15
14
17
4
12
1 24 – 12 oz bottles
8 24 – 250 g jars
8 12 – 500 g pkgs.
6 18 – 500 g pkgs.
1 12 – 75 cl bottles
1 750 cc per bottle
8 24 – 4 oz tins
8 12 – 12 oz cans
5 32 – 1 kg pkgs.
1 16 – 500 g tins
2 20 – 2 kg bags
8 1k pkg.
8 4 – 450 g glasses
3 10 – 4 oz boxes
3 10 pkgs.
3 24 – 50 g pkgs.
8 12 – 500 g pkgs.
3 12 – 100 g bars
7 50 – 300 g pkgs.
5 16 – 2 kg boxes
6 48 pieces
6 16 pies
6 24 boxes x 2 pies
5 24 – 250 g pkgs.
5 24 – 250 g pkgs.
8 24 pieces
4 5 kg pkg.
4 15 – 300 g rounds
2 24 – 500 ml bottles
3 48 pies
2 15 – 625 g jars
5 20 bags x 4 pieces
2 32 – 8 oz bottles
2 24 – 8 oz jars
1 24 – 12 oz bottles
3 10 boxes x 8 pieces
4 10 kg pkg.
1 24 – 355 ml bottles
4 10 – 500 g pkgs.
4 24 – 200 g pkgs.
8 24 – 150 g jars
7 5 kg pkg.
1 24 – 0.5 l bottles
76 Lakkalikööri
77 Original Frankfurter grüne Soße
23
12
1 500 ml
2 12 boxes
6) Call the Filter function from cell A98 to display records whose ‘SupplierID’ = the value in cell B95. The function sh
Filter Value
ProductID
2
ProductName
SupplierID
CategoryID
QuantityPerUnit
r Total Units
the UnitsinStock for each SupplierID .
d within CategoryID by ProductName (A to Z)
UnitPrice
18
19
10
22
21.35
25
30
40
97
31
21
38
6
23.25
15.5
17.45
39
62.5
9.2
81
10
21
9
4.5
14
31.23
43.9
45.6
123.79
25.89
12.5
32
2.5
14
UnitsInStock
39
17
13
53
0
120
15
6
29
31
22
86
24
35
39
29
0
42
25
40
3
104
61
20
76
15
49
26
0
10
0
9
112
111
UnitsOnOrder
ReorderLevel
0
40
70
0
0
0
0
0
0
0
30
0
0
0
0
0
0
0
0
0
40
0
0
0
0
0
0
0
0
0
70
40
0
0
Discontinued
10
FALSE
25
FALSE
25
FALSE
0
FALSE
0
TRUE
25
FALSE
10
FALSE
0
FALSE
0
TRUE
0
FALSE
30
FALSE
0
FALSE
5
FALSE
0
FALSE
5
FALSE
10
FALSE
0
TRUE
0
FALSE
5
FALSE
0
FALSE
5
FALSE
25
FALSE
25
FALSE
0
TRUE
30
FALSE
0
FALSE
30
FALSE
0
TRUE
0
TRUE
15
FALSE
20
FALSE
25
FALSE
20
FALSE
15
FALSE
18
19
26
97
263.5
18
18.4
9.65
14
46
19.45
9.5
12
9.5
12.75
20
26
16.25
53
7
32.8
7.45
24
38
19.5
13.25
55
34
28.5
49.3
43.9
33.25
21.05
17
14
12.5
36
15
21.5
34.8
15
10
7.75
20
112
11
29
17
69
123
85
26
17
27
5
95
36
15
10
11
65
20
38
0
21
115
21
36
62
79
19
113
17
24
22
76
4
52
6
26
15
26
14
101
4
125
0
0
50
0
0
0
0
0
0
10
0
70
0
0
70
60
50
0
0
0
0
0
0
10
0
0
0
0
0
0
0
80
0
100
0
10
0
10
0
0
0
20
0
15
20
25
0
15
5
30
10
0
25
15
15
0
0
25
15
25
30
10
25
0
10
20
30
20
20
0
0
25
0
5
30
0
20
10
15
15
30
0
0
5
5
25
FALSE
FALSE
FALSE
TRUE
FALSE
FALSE
FALSE
FALSE
TRUE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
TRUE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
18
13
57
32
0
0
20
15
FALSE
FALSE
ll B95. The function should spill values over through column K and down to row 101
UnitPrice
UnitsInStock
Supplier Total Units
UnitsOnOrder
ReorderLevel
Discontinued
79
79
79
79
79
93992
59244
Purchase answer to see full
attachment
