I have data being entered into non-adjacent columns that I want to merge, sum then remove the duplicated name. I do not have a master floor type list to query as there are over 50k types. Example, Floor Tile. There are potentially 5 different floor tile types being entered per row (house) and I need to be able to query all columns and produce a single total qty for the material on a separate sheet for purchasing to order from.
EX Data Entered:
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 129"]
<tbody>[TR]
[TD="width: 129"]Floor Tile 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl69, width: 89"] FT Qty 1 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD="class: xl68, width: 129"]Floor Tile 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl69, width: 89"] FT Qty 2 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD="class: xl68, width: 129"]Floor Tile 3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl69, width: 89"] FT Qty 3 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD="class: xl68, width: 129"]Floor Tile 4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl69, width: 89"] FT Qty 4 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl69, width: 89"]Floor Tile 5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl69, width: 89"] FT Qty 5 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]CEBOTHUES18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 1,400.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]CETEXBEIG18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 1,100.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]PNWOOSTUC836[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 1,600.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]FLFORMYST624[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 1,400.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]ALALABLAN117[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 2,100.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]CETEXBEIG18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 1,200.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]FLFORMYST624[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 1,400.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]MZARTAVOR20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 150.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]CETEXBEIG18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 1,200.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]CETEXHUES18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 1,900.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]EGCLAIVOR1224[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 2,400.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]LAPOSBLAN21[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 150.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]CEORLHUES18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 1,500.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]EGMARWALN624[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 200.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]EGMARGREY624[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 150.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]CEBOTHUES18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 1,100.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]ALBOSNOCE17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 50.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]RNPRICESA20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1,200.00
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]PNWOOSTUC836[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]200.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]LAEQUSAGE18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 100.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]FLFORMYST624[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1,200.00[/TD]
[TD]CETEXBEIG18[/TD]
[TD]150.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Example Final Report
[TABLE="class: outer_border, width: 250"]
<tbody>[TR]
[TD]CEBOTHUES18[/TD]
[TD]2,500.00[/TD]
[/TR]
[TR]
[TD]CETEXBEIG18[/TD]
[TD]3,650.00[/TD]
[/TR]
[TR]
[TD]PNWOOSTUC836[/TD]
[TD]1,800.00[/TD]
[/TR]
[TR]
[TD]FLFORMYST624[/TD]
[TD]4,000.00[/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]ALALABLAN117[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2,100.00 [/TD]
[/TR]
[TR]
[TD]MZARTAVOR20[/TD]
[TD]150.00[/TD]
[/TR]
[TR]
[TD]EGCLAIVOR1224[/TD]
[TD]2,400.00[/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]LAPOSBLAN21[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]150
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]CEORLHUES18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1,500.00[/TD]
[/TR]
</tbody>[/TABLE]
HELP!
EX Data Entered:
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 129"]
<tbody>[TR]
[TD="width: 129"]Floor Tile 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl69, width: 89"] FT Qty 1 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD="class: xl68, width: 129"]Floor Tile 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl69, width: 89"] FT Qty 2 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD="class: xl68, width: 129"]Floor Tile 3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl69, width: 89"] FT Qty 3 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD="class: xl68, width: 129"]Floor Tile 4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl69, width: 89"] FT Qty 4 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl69, width: 89"]Floor Tile 5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl69, width: 89"] FT Qty 5 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]CEBOTHUES18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 1,400.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]CETEXBEIG18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 1,100.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]PNWOOSTUC836[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 1,600.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]FLFORMYST624[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 1,400.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]ALALABLAN117[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 2,100.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]CETEXBEIG18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 1,200.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]FLFORMYST624[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 1,400.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]MZARTAVOR20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 150.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]CETEXBEIG18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 1,200.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]CETEXHUES18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 1,900.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]EGCLAIVOR1224[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 2,400.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]LAPOSBLAN21[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 150.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]CEORLHUES18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 1,500.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]EGMARWALN624[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 200.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]EGMARGREY624[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 150.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]CEBOTHUES18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 1,100.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]ALBOSNOCE17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 50.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]RNPRICESA20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1,200.00
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]PNWOOSTUC836[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]200.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]LAEQUSAGE18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 100.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]FLFORMYST624[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1,200.00[/TD]
[TD]CETEXBEIG18[/TD]
[TD]150.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Example Final Report
[TABLE="class: outer_border, width: 250"]
<tbody>[TR]
[TD]CEBOTHUES18[/TD]
[TD]2,500.00[/TD]
[/TR]
[TR]
[TD]CETEXBEIG18[/TD]
[TD]3,650.00[/TD]
[/TR]
[TR]
[TD]PNWOOSTUC836[/TD]
[TD]1,800.00[/TD]
[/TR]
[TR]
[TD]FLFORMYST624[/TD]
[TD]4,000.00[/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]ALALABLAN117[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2,100.00 [/TD]
[/TR]
[TR]
[TD]MZARTAVOR20[/TD]
[TD]150.00[/TD]
[/TR]
[TR]
[TD]EGCLAIVOR1224[/TD]
[TD]2,400.00[/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]LAPOSBLAN21[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]150
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]CEORLHUES18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1,500.00[/TD]
[/TR]
</tbody>[/TABLE]
HELP!