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

ouadad

Board Regular
Joined
Jun 19, 2005
Messages
213
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.

Code:
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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?
 
Upvote 0
It's part of a data set that includes parts with locations where the part is used. I just deleted the locations row.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,209
Members
453,022
Latest member
RobertV1609

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