Merge duplicate data across different columns and return sum of its qty

[StarG]

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:

Floor Tile 1 <tbody> </tbody>	FT Qty 1 <tbody> </tbody>	Floor Tile 2 <tbody> </tbody>	FT Qty 2 <tbody> </tbody>	Floor Tile 3 <tbody> </tbody>	FT Qty 3 <tbody> </tbody>	Floor Tile 4 <tbody> </tbody>	FT Qty 4 <tbody> </tbody>	Floor Tile 5 <tbody> </tbody>	FT Qty 5 <tbody> </tbody>
CEBOTHUES18 <tbody> </tbody>	1,400.00 <tbody> </tbody>	CETEXBEIG18 <tbody> </tbody>	1,100.00 <tbody> </tbody>	PNWOOSTUC836 <tbody> </tbody>	1,600.00 <tbody> </tbody>
FLFORMYST624 <tbody> </tbody>	1,400.00 <tbody> </tbody>
ALALABLAN117 <tbody> </tbody>	2,100.00 <tbody> </tbody>
CETEXBEIG18 <tbody> </tbody>	1,200.00 <tbody> </tbody>	FLFORMYST624 <tbody> </tbody>	1,400.00 <tbody> </tbody>
MZARTAVOR20 <tbody> </tbody>	150.00 <tbody> </tbody>	CETEXBEIG18 <tbody> </tbody>	1,200.00 <tbody> </tbody>	CETEXHUES18 <tbody> </tbody>	1,900.00 <tbody> </tbody>
EGCLAIVOR1224 <tbody> </tbody>	2,400.00 <tbody> </tbody>	LAPOSBLAN21 <tbody> </tbody>	150.00 <tbody> </tbody>	CEORLHUES18 <tbody> </tbody>	1,500.00 <tbody> </tbody>	EGMARWALN624 <tbody> </tbody>	200.00 <tbody> </tbody>	EGMARGREY624 <tbody> </tbody>	150.00 <tbody> </tbody>
CEBOTHUES18 <tbody> </tbody>	1,100.00 <tbody> </tbody>	ALBOSNOCE17 <tbody> </tbody>	50.00 <tbody> </tbody>
RNPRICESA20 <tbody> </tbody>	1,200.00	PNWOOSTUC836 <tbody> </tbody>	200.00
LAEQUSAGE18 <tbody> </tbody>	100.00 <tbody> </tbody>	FLFORMYST624 <tbody> </tbody>	1,200.00	CETEXBEIG18	150.00

<tbody>
</tbody>

Example Final Report

CEBOTHUES18	2,500.00
CETEXBEIG18	3,650.00
PNWOOSTUC836	1,800.00
FLFORMYST624	4,000.00
ALALABLAN117 <tbody> </tbody>	2,100.00
MZARTAVOR20	150.00
EGCLAIVOR1224	2,400.00
LAPOSBLAN21 <tbody> </tbody>	150
CEORLHUES18 <tbody> </tbody>	1,500.00

<tbody>
</tbody>

HELP!

 

[Fluff]

Hi & welcome to MrExcel.
Are you interested in a VBA approach?
Also if the columns are not adjacent, which columns are your data in?

 

[StarG]

I am interested in ANYTHING that will work.

The data is in all 10 columns but split (as shown) by each type then qty.

EX:
Tile Type 1 = Column A Qty for Type 1 = Column B
Tile Type 2 = Column C Oty for Type 2 = Column D
Tile Type 3 = Column E Qty for Type 3 = Column F

I need to merge all similar Tile Types, return the sum, and display in an easy to read table.

 

[Fluff]

In that case try this

Sub AddValues()
   Dim Cl As Range
   Dim UsdRws As Long
   
   UsdRws = Range("A" & Rows.Count).End(xlUp).Row
   With CreateObject("scripting.dictionary")
      For Each Cl In Intersect(Rows("2:" & UsdRws), Range("A:A,C:C,E:E,G:G,I:I"))
         If Cl.Value <> "" Then .Item(Cl.Value) = .Item(Cl.Value) + Cl.Offset(, 1).Value
      Next Cl
      Range("A" & UsdRws + 10).Resize(.Count).Value = Application.Transpose(.Keys)
      Range("B" & UsdRws + 10).Resize(.Count).Value = Application.Transpose(.Items)
   End With
End Sub