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

Status
Not open for further replies.

Alex_V

New Member
Joined
Aug 24, 2017
Messages
2
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:

[TABLE="width: 1382"]
<tbody>[TR]
[TD][TABLE="width: 1234"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Unit Amount[/TD]
[TD]Type[/TD]
[TD]Rarity[/TD]
[TD]Sell Amount[/TD]
[TD]Sell Price/unit[/TD]
[TD]Total Sell Price[/TD]
[TD]Buy Amount[/TD]
[TD]Buy Price/unit[/TD]
[TD]Total Buy Price[/TD]
[/TR]
[TR]
[TD]Thunderbolt
[/TD]
[TD]1[/TD]
[TD]Shotgun
[/TD]
[TD]Epic[/TD]
[TD]379.99[/TD]
[TD][/TD]
[TD][/TD]
[TD]353.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Scrap Metal [/TD]
[TD]100[/TD]
[TD]

Resource
[/TD]
[TD]Common[/TD]
[TD]250 x
[/TD]
[TD] 6.99
[/TD]
[TD]17.48[/TD]
[TD]250 x
[/TD]
[TD]6.65[/TD]
[TD]16.63[/TD]
[/TR]
[TR]
[TD]Copper [/TD]
[TD]100[/TD]
[TD]

Resource
[/TD]
[TD]Common[/TD]
[TD]150 x
[/TD]
[TD] 13.89
[/TD]
[TD]20.84[/TD]
[TD]150 x
[/TD]
[TD]13.85[/TD]
[TD]20.78[/TD]
[/TR]
[TR]
[TD]Wires [/TD]
[TD]100[/TD]
[TD]

Resource
[/TD]
[TD]Common[/TD]
[TD]750 x
[/TD]
[TD] 7.37
[/TD]
[TD]55.28[/TD]
[TD]750 x
[/TD]
[TD]7.13[/TD]
[TD]53.48[/TD]
[/TR]
[TR]
[TD]Rocket booster[/TD]
[TD]1[/TD]
[TD]

Booster
[/TD]
[TD]Rare[/TD]
[TD]2 x
[/TD]
[TD] 52.76
[/TD]
[TD]105.52[/TD]
[TD]2 x
[/TD]
[TD]45[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]Scrap Metal [/TD]
[TD]100[/TD]
[TD]

Resource
[/TD]
[TD]Common[/TD]
[TD]450 x
[/TD]
[TD] 6.99
[/TD]
[TD]31.46[/TD]
[TD]450 x
[/TD]
[TD]6.65[/TD]
[TD]29.93[/TD]
[/TR]
[TR]
[TD]Copper [/TD]
[TD]100[/TD]
[TD]

Resource
[/TD]
[TD]Common[/TD]
[TD]50 x
[/TD]
[TD] 13.89
[/TD]
[TD]6.95[/TD]
[TD]50 x
[/TD]
[TD]13.85[/TD]
[TD]6.93[/TD]
[/TR]
[TR]
[TD]Avenger 57mm[/TD]
[TD]1[/TD]
[TD]

Cannon
[/TD]
[TD]Common[/TD]
[TD]2 x
[/TD]
[TD] 0.11
[/TD]
[TD]0.22[/TD]
[TD]2 x
[/TD]
[TD]0.06[/TD]
[TD]0.12[/TD]
[/TR]
[TR]
[TD]Scrap Metal [/TD]
[TD]100[/TD]
[TD]

Resource
[/TD]
[TD]Common[/TD]
[TD]300 x
[/TD]
[TD] 6.99
[/TD]
[TD]20.97[/TD]
[TD]300 x
[/TD]
[TD]6.65[/TD]
[TD]19.95[/TD]
[/TR]
[TR]
[TD]Radio[/TD]
[TD]1[/TD]
[TD]

Radio
[/TD]
[TD]Common[/TD]
[TD]2 x
[/TD]
[TD] 0.07
[/TD]
[TD]0.14[/TD]
[TD]2 x
[/TD]
[TD]0.06[/TD]
[TD]0.12[/TD]
[/TR]
[TR]
[TD]Avia booster[/TD]
[TD]1[/TD]
[TD]

Booster
[/TD]
[TD]Common[/TD]
[TD]3 x
[/TD]
[TD] 0.20
[/TD]
[TD]0.6[/TD]
[TD]3 x
[/TD]
[TD]0.14[/TD]
[TD]0.42[/TD]
[/TR]
[TR]
[TD]Rare Minimum Bench Cost[/TD]
[TD]1[/TD]
[TD]

Meta Item
[/TD]
[TD]Rare[/TD]
[TD]1 x
[/TD]
[TD] 4.50
[/TD]
[TD]4.5[/TD]
[TD]1 x
[/TD]
[TD]4.5[/TD]
[TD]4.5[/TD]
[/TR]
[TR]
[TD]Sledgehammer[/TD]
[TD]1[/TD]
[TD]

Shotgun
[/TD]
[TD]Rare[/TD]
[TD]2 x
[/TD]
[TD] 43.37
[/TD]
[TD]86.74[/TD]
[TD]2 x
[/TD]
[TD]36.62[/TD]
[TD]73.24[/TD]
[/TR]
[TR]
[TD]Scrap Metal [/TD]
[TD]100[/TD]
[TD]

Resource
[/TD]
[TD]Common[/TD]
[TD]450 x
[/TD]
[TD] 6.99
[/TD]
[TD]31.46[/TD]
[TD]450 x
[/TD]
[TD]6.65[/TD]
[TD]29.93[/TD]
[/TR]
[TR]
[TD]Copper [/TD]
[TD]100[/TD]
[TD]

Resource
[/TD]
[TD]Common[/TD]
[TD]50 x
[/TD]
[TD] 13.89
[/TD]
[TD]6.95[/TD]
[TD]50 x
[/TD]
[TD]13.85[/TD]
[TD]6.93[/TD]
[/TR]
[TR]
[TD]Small wheel ST[/TD]
[TD]1[/TD]
[TD]

Wheel
[/TD]
[TD]Common[/TD]
[TD]2 x
[/TD]
[TD] 0.18
[/TD]
[TD]0.36[/TD]
[TD]2 x
[/TD]
[TD]0.13[/TD]
[TD]0.26[/TD]
[/TR]
[TR]
[TD]Avia booster[/TD]
[TD]1[/TD]
[TD]

Booster
[/TD]
[TD]Common[/TD]
[TD]2 x
[/TD]
[TD] 0.20
[/TD]
[TD]0.4[/TD]
[TD]2 x
[/TD]
[TD]0.14[/TD]
[TD]0.28[/TD]
[/TR]
[TR]
[TD]Lupara[/TD]
[TD]1[/TD]
[TD]

Shotgun
[/TD]
[TD]Common[/TD]
[TD]3 x
[/TD]
[TD] 0.10
[/TD]
[TD]0.3[/TD]
[TD]3 x
[/TD]
[TD]0.06[/TD]
[TD]0.18[/TD]
[/TR]
[TR]
[TD]Scrap Metal [/TD]
[TD]100[/TD]
[TD]

Resource
[/TD]
[TD]Common[/TD]
[TD]300 x
[/TD]
[TD] 6.99
[/TD]
[TD]20.97[/TD]
[TD]300 x
[/TD]
[TD]6.65[/TD]
[TD]19.95[/TD]
[/TR]
[TR]
[TD]Rare Minimum Bench Cost[/TD]
[TD]1[/TD]
[TD]

Meta Item
[/TD]
[TD]Rare[/TD]
[TD]1 x
[/TD]
[TD] 4.50
[/TD]
[TD]4.5[/TD]
[TD]1 x
[/TD]
[TD]4.5[/TD]
[TD]4.5[/TD]
[/TR]
[TR]
[TD]Spitfire[/TD]
[TD]1[/TD]
[TD]

Shotgun
[/TD]
[TD]Rare[/TD]
[TD]2 x
[/TD]
[TD] 41.54
[/TD]
[TD]83.08[/TD]
[TD]2 x
[/TD]
[TD]41.5[/TD]
[TD]83[/TD]
[/TR]
[TR]
[TD]Scrap Metal [/TD]
[TD]100[/TD]
[TD]

Resource
[/TD]
[TD]Common[/TD]
[TD]450 x
[/TD]
[TD] 6.99
[/TD]
[TD]31.46[/TD]
[TD]450 x
[/TD]
[TD]6.65[/TD]
[TD]29.93[/TD]
[/TR]
[TR]
[TD]Copper [/TD]
[TD]100[/TD]
[TD]

Resource
[/TD]
[TD]Common[/TD]
[TD]50 x
[/TD]
[TD] 13.89
[/TD]
[TD]6.95[/TD]
[TD]50 x
[/TD]
[TD]13.85[/TD]
[TD]6.93[/TD]
[/TR]
[TR]
[TD]Small wheel ST[/TD]
[TD]1[/TD]
[TD]

Wheel
[/TD]
[TD]Common[/TD]
[TD]2 x
[/TD]
[TD] 0.18
[/TD]
[TD]0.36[/TD]
[TD]2 x
[/TD]
[TD]0.13[/TD]
[TD]0.26[/TD]
[/TR]
[TR]
[TD]Car jack[/TD]
[TD]1[/TD]
[TD]

Special module
[/TD]
[TD]Common[/TD]
[TD]2 x
[/TD]
[TD] 0.07
[/TD]
[TD]0.14[/TD]
[TD]2 x
[/TD]
[TD]0.06[/TD]
[TD]0.12[/TD]
[/TR]
[TR]
[TD]Lupara[/TD]
[TD]1[/TD]
[TD]

Shotgun
[/TD]
[TD]Common[/TD]
[TD]3 x
[/TD]
[TD] 0.10
[/TD]
[TD]0.3[/TD]
[TD]3 x
[/TD]
[TD]0.06[/TD]
[TD]0.18[/TD]
[/TR]
[TR]
[TD]Scrap Metal [/TD]
[TD]100[/TD]
[TD]

Resource
[/TD]
[TD]Common[/TD]
[TD]300 x
[/TD]
[TD] 6.99
[/TD]
[TD]20.97[/TD]
[TD]300 x
[/TD]
[TD]6.65[/TD]
[TD]19.95[/TD]
[/TR]
[TR]
[TD]Rare Minimum Bench Cost[/TD]
[TD]1[/TD]
[TD]

Meta Item
[/TD]
[TD]Rare[/TD]
[TD]1 x
[/TD]
[TD] 4.50
[/TD]
[TD]4.5[/TD]
[TD]1 x
[/TD]
[TD]4.5[/TD]
[TD]4.5[/TD]
[/TR]
[TR]
[TD]Epic Minimum Bench Cost[/TD]
[TD]1[/TD]
[TD]

Meta Item
[/TD]
[TD]Epic[/TD]
[TD]1 x
[/TD]
[TD] 18.00
[/TD]
[TD]18[/TD]
[TD]1 x
[/TD]
[TD]18[/TD]
[TD]18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Status
Not open for further replies.

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,623
Latest member
Techenthusiast

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top