# Calculating and completing a complex report in Excel

#### thewinchester

##### New Member
Summary:
I'm trying to create a report/worksheet within Excel that shows me exactly how much of each component good I need to have/make on a specific day, based on the quantity of finished goods items that use it.

And I'm honestly stuck on the best way of how to achieve this.

Overview:
I have a table of products (called Finished Goods, or FG, primary key is FGSKU), each of which has a number of components (in a table called Component Goods, or CG, Primary key CGSKU).

I then have another Table, where I define which CG items that are part of each FG (called FG to CG Mapping), where a FG has a 1-M relationship with CG.

For example, my Simple Pumpkin Soup Kit - makes 1.5L (FGSKU 81450) might have 7x CG items mapped to it.

Data quality is enforced through the use of lists.

Back in the FG table (where the main work each day needs to happen), I enter in the number of units on order for that FG on a given day.

The intent being that I can produce a report which calculates for me and shows how many of each CG I need to make on each day.

So sticking with the example - if I had 3x Simple Pumpkin Soup Kit - makes 1.5L (FGSKU 81450) on order; then it would tell me the total quantity of each CG item associated with the FG item was needed. This would mean the report would show 300 g of Pumpkin Butternut; 6 ea of Onion Brown (Whole); 1200 g of Potato White; and so on.

And ideally to speed production, it would be good to sort the results based on the CG Category associated with each CG line.

Data examples:

FG table

 FGSKU FG Name FG Category FG Pick Location Qty Required for current day 7470 Chunky Melon Trio - 250g Fruit Prepared F-10 7455 Chunky Pineapple - 220g Fruit Prepared F-10 7465 Chunky Rockmelon - 250g Fruit Prepared F-10 7460 Chunky Watermelon - 250g Fruit Prepared F-10 7450 Family Seasonal Fruit Platter - 1.3Kg Fruit Prepared F-10 7475 Fruit Salad - 250g Fruit Prepared F-10 7500 Sliced Melons Trio - 1kg Fruit Prepared F-10

<colgroup><col style="width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:15155;width:333pt" width="444"> <col style="mso-width-source:userset;mso-width-alt:4676;width:103pt" width="137"> <col style="mso-width-source:userset;mso-width-alt:3942;width:87pt" width="116"> <col style="mso-width-source:userset;mso-width-alt:4778;width:105pt" width="140"> </colgroup><tbody>
</tbody>

CG table

 CG Name CGSKU CG Unit CG Unit value CG Category CG Pick Location Avocado (Prep or unit type) 900001 g 1 Vegetable - Whole Baby Rocket 900002 g 1 Vegetable - Leaf Bacon (type) 900003 g 1 Meat Basil 900004 g 1 Herb Basil Pesto 900005 g 1 Condiment Bay leaf 900006 g 1 Herb Beef Lean (cut type) 900007 g 1 Meat Beef Mince Lean (g) 900008 g 1 Meat Beef Mince Low Fat 900009 g 1 Meat - Beef Beetroot (Ready to eat) 900010 g 1 Vegetable - Whole

<colgroup><col style="mso-width-source:userset;mso-width-alt:10222;width:225pt" width="300"> <col style="mso-width-source:userset;mso-width-alt:4539;width:100pt" width="133"> <col style="mso-width-source:userset;mso-width-alt:3976;width:87pt" width="117"> <col style="mso-width-source:userset;mso-width-alt:4147;width:91pt" width="122"> <col style="mso-width-source:userset;mso-width-alt:5870;width:129pt" width="172"> <col style="mso-width-source:userset;mso-width-alt:3976;width:87pt" width="117"> </colgroup><tbody>
</tbody>

FG to CG Mapping

 FGSKU FG Name CGSKU CG Name CG Unit CG Qty Required for FGSKU 81450 Simple Pumpkin Soup Kit - makes 1.5L 900053 Pumpkin Butternut g 100 81450 Simple Pumpkin Soup Kit - makes 1.5L 900042 Onion Brown (Whole) ea 2 81450 Simple Pumpkin Soup Kit - makes 1.5L 900052 Potato White g 400 81450 Simple Pumpkin Soup Kit - makes 1.5L 900026 Garlic (Clove) g 1 81450 Simple Pumpkin Soup Kit - makes 1.5L 900065 Stock Vegetable (Packet type) ea 1 81450 Simple Pumpkin Soup Kit - makes 1.5L 900023 Cream (400ml carton) ea 1 81450 Simple Pumpkin Soup Kit - makes 1.5L 900045 Parsley Italian g 1 81485 Potato and Crispy Bacon Soup Kit - makes 1.5L 900065 Stock Vegetable (Packet type) ea 1 81458 Sweet Potato & Basil Soup Kit - makes 1.5L 900065 Stock Vegetable (Packet type) ea 1

<colgroup><col style="width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:9608;width:211pt" width="282"> <col style="width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:6212;width:137pt" width="182"> <col style="width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:6161;width:135pt" width="181"> </colgroup><tbody>
</tbody>

Example idea for the report (to show the what i'm aiming towards)

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### Fazza

##### MrExcel MVP
At first glance this seems like well structured data & a great task for MS Access. And if staying in Excel, a pivot table might be a good solution.

Replies
0
Views
893
Replies
1
Views
291
Replies
15
Views
1K
Replies
2
Views
239
Replies
1
Views
208

1,129,384
Messages
5,635,982
Members
416,891
Latest member
Okomomo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back