Finding Dinosaurs with Excel
Last week I read Jon Peltier’s and Chandoo’s posts about dinosaurs and Excel. These reminded me of the fun discussions about monsters and dinosaurs I’ve been having at home with my 2-year-old toddler. Dinosaurs are cool, but dinosaurs and spreadsheets; even better. I knew I had to jump in and this looked like a fun challenge. After all, I am a PM in the Excel Team AND paleontology is cool.
In this post, I explain an alternative solution to the dinosaur mystery question asked by one of Chandoo’s readers:
“I need an algebraic formula to solve this in Excel
I have 5 heads, 5 bodies, 4 arm sets, 4 leg sets and 3 tails. I need to see if I can create 1000 dinosaurs from these, and if that’s too many AND I need the 5 digit groupings to prove it and create them.
basically Xa*Xb*Xc*Xd*Xe=1000 — I’m not supposed to go over 1200. […] And then I want the 5 digit combinations if possible — right now they are trying to do the combinations by hand — would be awesome if we could do it in Excel.”
— Debby
Since Chandoo already solved this by using functions, in my solution, I use PivotTables, the Data Model, and 1 simple function. Note that I will be using Excel 2016.
We start by adding our dinosaur body parts to Excel. Per Debby’s question, we have lists for “Heads”, “Bodies”, “Arm Sets”, “Leg Sets”, and “Tails”.
Then, we convert these lists into Tables. You can easily do this by selecting a cell within any of your lists and using the “CTRL+T” keyboard shortcut. Alternatively, you can go to the “Insert” tab and click on the “Table” button.
To make things easier later on, you may want to name each of your tables using the “Name Box” to correspond with the table header cell.
Next, add all of your dinosaur body part tables into the Data Model. Go to the “Power Pivot” Tab in the Ribbon, and click on the ”Add to Data Model” button for each of the 5 Tables.
Once all 5 of your tables are added, your Data Model in Power Pivot should look something like this:
After that, create a PivotTable based on your Data Model. To do this, click on the PivotTable button within Power Pivot and pick a destination for your new PivotTable.
At this point, you should have a PivotTable with all your dinosaur body parts as fields. Before dragging any PivotTable fields, let’s set up the display layout of your PivotTable. Go to the design tab and select a few options.
- Report Layout > “Show in Tabular Form”
- Report Layout > “Repeat All Item Labels”
- Subtotals > “Do Not Show Subtotals”
Then go to the Analyze Tab and select the following option
- Show > “+/1 Buttons”
Now drag all your dinosaur part fields into the “Rows” bucket, this should look something like this:
And your PivotTable should look like this:
Finally, use the new CONCAT function to create the 5 digit combination, and copy this function for all 1200 combinations.
If you want to play with the actual file I created, you can find the “Dinosaurs in Excel” spreadsheet here: https://doc.co/Jkq2Xr
