Break one column into multiple, based on information from another column.

[Alex_V]

Hello,

I am attempting to create a break out crafting/costing system for an online game. The game market system is all posted on an online website so I have been using Excel Power Query to gather the information into a list. The issue I have run into is that the information I've gather has been combined into a flat list, which is difficult to read use.

For now I am starting with one item: Thunderbolt | Crossout DB - Crossout Market and Crafting Calculator
There are over 280 items I need to list and evaluate. Each item uses basically the same URL with the end number changing from anywhere between 1 to 323. So far I can scrape all of the information from that webpage but as I said I only have a flat list. As you can see on the webpage under the "recipe" box there is a "+" symbol that breaks into a crafting list. My plan is to recreate or preserve this information for visual/data manipulation purposes. Here is the list after some cleanup and processing:

Name Unit Amount Type Rarity Sell Amount Sell Price/unit Total Sell Price Buy Amount Buy Price/unit Total Buy Price Thunderbolt 1 Shotgun Epic 379.99 353.00 Scrap Metal 100 Resource Common 250 x 6.99 17.48 250 x 6.65 16.63 Copper 100 Resource Common 150 x 13.89 20.84 150 x 13.85 20.78 Wires 100 Resource Common 750 x 7.37 55.28 750 x 7.13 53.48 Rocket booster 1 Booster Rare 2 x 52.76 105.52 2 x 45 90 Scrap Metal 100 Resource Common 450 x 6.99 31.46 450 x 6.65 29.93 Copper 100 Resource Common 50 x 13.89 6.95 50 x 13.85 6.93 Avenger 57mm 1 Cannon Common 2 x 0.11 0.22 2 x 0.06 0.12 Scrap Metal 100 Resource Common 300 x 6.99 20.97 300 x 6.65 19.95 Radio 1 Radio Common 2 x 0.07 0.14 2 x 0.06 0.12 Avia booster 1 Booster Common 3 x 0.20 0.6 3 x 0.14 0.42 Rare Minimum Bench Cost 1 Meta Item Rare 1 x 4.50 4.5 1 x 4.5 4.5 Sledgehammer 1 Shotgun Rare 2 x 43.37 86.74 2 x 36.62 73.24 Scrap Metal 100 Resource Common 450 x 6.99 31.46 450 x 6.65 29.93 Copper 100 Resource Common 50 x 13.89 6.95 50 x 13.85 6.93 Small wheel ST 1 Wheel Common 2 x 0.18 0.36 2 x 0.13 0.26 Avia booster 1 Booster Common 2 x 0.20 0.4 2 x 0.14 0.28 Lupara 1 Shotgun Common 3 x 0.10 0.3 3 x 0.06 0.18 Scrap Metal 100 Resource Common 300 x 6.99 20.97 300 x 6.65 19.95 Rare Minimum Bench Cost 1 Meta Item Rare 1 x 4.50 4.5 1 x 4.5 4.5 Spitfire 1 Shotgun Rare 2 x 41.54 83.08 2 x 41.5 83 Scrap Metal 100 Resource Common 450 x 6.99 31.46 450 x 6.65 29.93 Copper 100 Resource Common 50 x 13.89 6.95 50 x 13.85 6.93 Small wheel ST 1 Wheel Common 2 x 0.18 0.36 2 x 0.13 0.26 Car jack 1 Special module Common 2 x 0.07 0.14 2 x 0.06 0.12 Lupara 1 Shotgun Common 3 x 0.10 0.3 3 x 0.06 0.18 Scrap Metal 100 Resource Common 300 x 6.99 20.97 300 x 6.65 19.95 Rare Minimum Bench Cost 1 Meta Item Rare 1 x 4.50 4.5 1 x 4.5 4.5 Epic Minimum Bench Cost 1 Meta Item Epic 1 x 18.00 18 1 x 18 18 <tbody> </tbody>

<tbody>
</tbody>

I've been trying to figure out a method of using the "Rarity" and the "type" to organize and break into crafting.I have been unable to figure out how to do that, while creating a system I can apply data manipulation to for every item. These other items range in complexity from a single sub item, to nearly 3 times more than there are in my example above. Basically I'm looking for something that will go through and move every item needed for crafting the main item (in this case the "Thunderbolt") to another column or divide it from the rest of the items, then do so for each subset of item crafting. I was thinking that since each item is always 1 "rarity" level below the item it is being used to craft (excluding resources which are always "common" rarity), I could break it down by that. Except that I need resources to stay with the main item they are being used by. Fortunately as resources have the "resource" type I could maybe say that each item with the type "resource" will be one level below the "non-resource" item above it. So that the 3 resources that the Thunderbolt use will be in the same divisor as "Rocket Booster" "Sledgehammer" and "Spitfire". Despite having an idea of HOW to make this work, I have no idea how to actually do that. Nor do I know if this is the best method for accomplishing this task.

My end goal is break down each of these items to their very base components so I know exactly how many of each item it will take to create the top item, and can see the costs of buying the resources to make each item vs buying the item itself at each level. The "Avenger 57mm" is a good example of this. It costs about $0.14 but takes 300 Scrap Metal to craft which costs about $20.97. So it would be significantly cheaper to simply buy this item rather than buy the resources to make it. On top of this I also plan to make a list that keeps up to date with what items sell for the most and cost the least to make.

At the moment I'm trying simply to tackle breaking this one item into useful data, but any advice on the entire process will be much appreciated!

Thanks,
Alex V.

 

[xenou]

Closed - duplicate question posted by mistake. Current thread at:
Break one column into multiple, based on information from another column.