Is there a way to sum the values in columns based on a criteria?

[ouadad]

I have 6818 columns of data like the example below. Of the 6818 columns, there are 886 unique part numbers. I want to be able to sum the data in each row based on the part number in row 1. For example, the first two columns with part number 000030616 would sum row-by-row for each month to produce another column with the same number of rows. Then I would just remove the two unsummed columns and replace them with the summed column.

Part	000030616	000030616	000058308	000166483	000166483	000174685	000276507
DRP Forecast	1	2	3	4	5	6	7
Jan-17	0	0	0	0	8	0	0
Feb-17	0	0	0	0	2	0	0
Mar-17	0	0	0	0	0	2	0
Apr-17							
May-17							
Jun-17	0	0	0	0	0	1	0
Jul-17	0	2	1	0	0	0	0
Aug-17	0	0	1	0	8	1	0
Sep-17	0	0	0	0	4	0	0
Oct-17	0	0	0	0	4	1	0
Nov-17	1	0	5	0	4	1	0
Dec-17	1	0	5	0	4	1	0
							
Demand							
Jan-17	0	3	2	0	0	0	0
Feb-17	0	0	0	0	0	0	0
Mar-17	0	0	0	0	0	2	0
Apr-17	0	0	0	0	0	0	0
May-17	0	0	7	0	0	0	0
Jun-17	0	0	0	0	0	0	1
Jul-17	0	1	0	0	0	0	0
Aug-17	0	0	0	0	50	0	0
Sep-17	0	0	0	0	0	0	0
Oct-17	7	0	0	0	0	0	0
Nov-17	0	0	0	0	0	0	0
Dec-17	0	0	0	0	0	0	0
							
Forecast							
Jan-17	0	0	1	0	8	0	0
Feb-17	0	0	0	0	8	0	0
Mar-17	0	0	0	0	8	0	0
Apr-17	0	0	0	0	2	0	0
May-17	0	0	0	0	0	0	0
Jun-17	0	0	0	0	8	0	0
Jul-17	0	1	0	0	8	0	0
Aug-17	0	0	0	0	8	0	0
Sep-17	0	0	1	0	4	0	0
Oct-17	0	0	1	0	4	0	0
Nov-17	1	0	1	0	4	0	0
Dec-17	1	0	0	0	4	0	0

 

[RileyC]

Probably a way easier than this that someone would know but I would create an class array of that holds part number, size [2] forcast array, and as many size [2] date arrays as you need. Then I would create a new sheet that inputs the values as you want shown. How did you come across the sheet to be formatted this way?

 

[ouadad]

It's part of a data set that includes parts with locations where the part is used. I just deleted the locations row.

 

[RileyC]

Oh wait herp I think you can use a pivot table to solve this.

 

[ouadad]

I like your first idea. I'll just create a new sheet with unique values of each part in columns and write VBA code to search and sum the rows for each part. Thanks.