Sum Columns based on part of the information in the first column

[awiltz]

Hello,
I am working on data where each row has a 9 digit number assigned to it (College Campuses). The first 6 digits are specific to each College and the last 3 are labels for each Location. I then have columns and columns of data for each. What I would like to do is have the columns summed based on the first 6 numbers of the 9 digit number (combine the data for all locations into 1 line). I also then want the last 3 numbers to be deleted so that there is only the first 6 numbers remaining and everything from the columns that started with those 6 numbers added together into 1 final sum. I have been doing this manually but with 14,000+ rows, it is going to take me months to process this all manually.
Thank you for any help you can give,
awiltz

 

[awiltz]

UNITID_P

INSTNM

MURD17

MURD16

MURD15

MURD14

MURD13

MURD12

MURD11

MURD10

MURD9

MURD8

NEG_M17

NEG_M16

NEG_M15

NEG_M14

NEG_M13

105677001​

Roberto-Venn School of Luthiery

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

105701001​

0​

0​

0​

0​

0​

0​

0​

0​

0​

105747001​

Scottsdale Community College

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

105792001​

South Mountain Community College

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

105792002​

South Mountain Community College

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

105792003​

0​

105792004​

South Mountain Community College

0​

0​

0​

0​

0​

0​

105899001​

Arizona Christian University

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

105987001​

0​

0​

0​

0​

0​

0​

0​

0​

0​

106041001​

Universal Technical Institute of Arizona Inc

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

106102001​

0​

0​

0​

0​

0​

0​

0​

0​

0​

106102002​

0​

0​

0​

0​

106102003​

0​

0​

0​

0​

106102004​

0​

0​

0​

0​

106102007​

0​

0​

0​

0​

106111001​

Empire Beauty School-NW Phoenix

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

106148001​

Yavapai College

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

106148002​

Yavapai College

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

106148003​

Yavapai College

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

106148004​

Yavapai College

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

106148005​

Yavapai College

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

106148006​

Yavapai College

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

106245001​

University of Arkansas at Little Rock

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

106245002​

University of Arkansas at Little Rock

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

106245003​

University of Arkansas at Little Rock

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

106245004​

University of Arkansas at Little Rock

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

106263001​

University of Arkansas for Medical Sciences

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

106263002​

University of Arkansas for Medical Sciences

0​

0​

0​

0​

106281001​

0​

0​

0​

0​

0​

0​

0​

0​

0​

106306001​

Arkansas Baptist College

0​

0​

0​

0​

0​

1​

0​

0​

0​

0​

0​

0​

0​

0​

0​

106315001​

Arkansas Beauty School-Little Rock

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

106324001​

Arkansas Beauty College

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

106342001​

Lyon College

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

106351001​

Arkansas College of Barbering and Hair Design

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

106351002​

Arkansas College of Barbering and Hair Design

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

106351003​

0​

0​

0​

0​

106360001​

Arthur's Beauty College Inc-Fort Smith

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

 

[DanteAmor]

You could put a sample of your data and the result that you want.
Use XL2BB tool to show sheet ranges, check out my signature.
If the data is confidential, replace it with generic data, but respect the structure of the sheet.

 

[DanteAmor]

Hi and welcome to MrExcel.
Now carry out the process with those data that you put, to see the result that you want.

 

[awiltz]

All Crime Data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
98	UNITID_P	INSTNM	INSTNM14	INSTNM11	INSTNM08	sector_cd	Sector_desc	men_total	women_total	Total	MURD17	MURD16	MURD15	MURD14	MURD13	MURD12	MURD11	MURD10	MURD9	MURD8	NEG_M17	NEG_M16	NEG_M15
99	105677001	Roberto-Venn School of Luthiery	Roberto-Venn School of Luthiery	Roberto-Venn School of Luthiery	Roberto-Venn School of Luthiery	9	Private for-profit, less-than 2-year	23	1	24	0	0	0	0	0	0	0	0	0	0	0	0	0
100	105701001		Hair Academy of Safford	Hair Academy of Safford	Hair Academy of Safford									0	0	0	0	0	0	0
101	105747001	Scottsdale Community College	Scottsdale Community College	Scottsdale Community College	Scottsdale Community College	4	Public, 2-year	4367	5091	9458	0	0	0	0	0	0	0	0	0	0	0	0	0
102	105792001	South Mountain Community College	South Mountain Community College	South Mountain Community College	South Mountain Community College	4	Public, 2-year	1714	2406	4120	0	0	0	0	0	0	0	0	0	0	0	0	0
103	105792002	South Mountain Community College	South Mountain Community College	South Mountain Community College	South Mountain Community College	4	Public, 2-year	1714	2406	4120	0	0	0	0	0	0	0	0	0		0	0	0
104	105792003			South Mountain Community College													0
105	105792004	South Mountain Community College				4	Public, 2-year	1714	2406	4120	0	0	0								0	0	0
106	105899001	Arizona Christian University	Arizona Christian University	Arizona Christian University	Arizona Christian University	2	Private nonprofit, 4-year or above	493	293	786	0	0	0	0	0	0	0	0	0	0	0	0	0
107	105987001		Tucson College	Tucson College	Tucson College									0	0	0	0	0	0	0
108	106041001	Universal Technical Institute of Arizona Inc	Universal Technical Institute of Arizona Inc	Universal Technical Institute of Arizona Inc	Universal Technical Institute of Arizona Inc	6	Private for-profit, 2-year	1789	71	1860	0	0	0	0	0	0	0	0	0	0	0	0	0
109	106102001		Western International University	Western International University	Western International University									0	0	0	0	0	0	0
110	106102002			Western International University	Western International University												0	0	0	0
111	106102003			Western International University	Western International University												0	0	0	0
112	106102004			Western International University	Western International University												0	0	0	0
113	106102007			Western International University	Western International University												0	0	0	0
114	106111001	Empire Beauty School-NW Phoenix	Empire Beauty School-NW Phoenix	Empire Beauty School-NW Phoenix	Empire Beauty School-NW Phoenix	9	Private for-profit, less-than 2-year	6	103	109	0	0	0	0	0	0	0	0	0	0	0	0	0
115	106148001	Yavapai College	Yavapai College	Yavapai College	Yavapai College	4	Public, 2-year	3172	4198	7370	0	0	0	0	0	0	0	0	0	0	0	0	0
116	106148002	Yavapai College	Yavapai College	Yavapai College	Yavapai College	4	Public, 2-year	3172	4198	7370	0	0	0	0	0	0	0	0	0	0	0	0	0
117	106148003	Yavapai College	Yavapai College	Yavapai College	Yavapai College	4	Public, 2-year	3172	4198	7370	0	0	0	0	0	0	0	0	0	0	0	0	0
118	106148004	Yavapai College	Yavapai College	Yavapai College	Yavapai College	4	Public, 2-year	3172	4198	7370	0	0	0	0	0	0	0	0	0	0	0	0	0
119	106148005	Yavapai College	Yavapai College	Yavapai College	Yavapai College	4	Public, 2-year	3172	4198	7370	0	0	0	0	0	0	0	0	0	0	0	0	0
120	106148006	Yavapai College	Yavapai College	Yavapai College	Yavapai College	4	Public, 2-year	3172	4198	7370	0	0	0	0	0	0	0	0	0	0	0	0	0
121	106245001	University of Arkansas at Little Rock	University of Arkansas at Little Rock	University of Arkansas at Little Rock	University of Arkansas at Little Rock	1	Public, 4-year or above	4547	7077	11624	0	0	0	0	0	0	0	0	0	0	0	0	0
122	106245002	University of Arkansas at Little Rock	University of Arkansas at Little Rock	University of Arkansas at Little Rock		1	Public, 4-year or above	4547	7077	11624	0	0	0	0	0	0	0				0	0	0
123	106245003	University of Arkansas at Little Rock	University of Arkansas at Little Rock	University of Arkansas at Little Rock		1	Public, 4-year or above	4547	7077	11624	0	0	0	0	0	0	0				0	0	0
124	106245004	University of Arkansas at Little Rock	University of Arkansas at Little Rock			1	Public, 4-year or above	4547	7077	11624	0	0	0	0	0	0					0	0	0
125	106263001	University of Arkansas for Medical Sciences	University of Arkansas for Medical Sciences	University of Arkansas for Medical Sciences	University of Arkansas for Medical Sciences	1	Public, 4-year or above	907	1929	2836	0	0	0	0	0	0	0	0	0	0	0	0	0
126	106263002	University of Arkansas for Medical Sciences				1	Public, 4-year or above	907	1929	2836	0	0									0	0
127	106281001		ABC Beauty College Inc	ABC Beauty College Inc	ABC Beauty College Inc									0	0	0	0	0	0	0
128	106306001	Arkansas Baptist College	Arkansas Baptist College	Arkansas Baptist College	Arkansas Baptist College	2	Private nonprofit, 4-year or above	403	190	593	0	0	0	0	0	1	0	0	0	0	0	0	0
129	106315001	Arkansas Beauty School-Little Rock	Arkansas Beauty School-Little Rock	Arkansas Beauty School	Arkansas Beauty School	9	Private for-profit, less-than 2-year	5	124	129	0	0	0	0	0	0	0	0	0	0	0	0	0
130	106324001	Arkansas Beauty College	Arkansas Beauty College	Arkansas Beauty College	Arkansas Beauty College	9	Private for-profit, less-than 2-year	2	25	27	0	0	0	0	0	0	0	0	0	0	0	0	0
131	106342001	Lyon College	Lyon College	Lyon College	Lyon College	2	Private nonprofit, 4-year or above	369	303	672	0	0	0	0	0	0	0	0	0	0	0	0	0
All Crime Data

 

[DanteAmor]

I assume the data in post #5 is the original data.
Now carry out the process with those data that you put, to see the result that you want.
Explain only 3 examples, the most representative.

 

[awiltz]

I'm not sure what process you are talking about?

 

[awiltz]

All Crime Data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
98	UNITID_P	INSTNM	INSTNM14	INSTNM11	INSTNM08	sector_cd	Sector_desc	men_total	women_total	Total	MURD17	MURD16	MURD15	MURD14	MURD13	MURD12	MURD11	MURD10	MURD9	MURD8	NEG_M17	NEG_M16	NEG_M15
99	105677	Roberto-Venn School of Luthiery	Roberto-Venn School of Luthiery	Roberto-Venn School of Luthiery	Roberto-Venn School of Luthiery	9	Private for-profit, less-than 2-year	23	1	24	0	0	0	0	0	0	0	0	0	0	0	0	0
100	105701		Hair Academy of Safford	Hair Academy of Safford	Hair Academy of Safford									0	0	0	0	0	0	0
101	105747	Scottsdale Community College	Scottsdale Community College	Scottsdale Community College	Scottsdale Community College	4	Public, 2-year	4367	5091	9458	0	0	0	0	0	0	0	0	0	0	0	0	0
102	105792	South Mountain Community College									0	0	0	0	0	0	0	0	0	0	0	0	0
103	105899001	Arizona Christian University	Arizona Christian University	Arizona Christian University	Arizona Christian University	2	Private nonprofit, 4-year or above	493	293	786	0	0	0	0	0	0	0	0	0	0	0	0	0
104	105987001		Tucson College	Tucson College	Tucson College									0	0	0	0	0	0	0
105	106041001	Universal Technical Institute of Arizona Inc	Universal Technical Institute of Arizona Inc	Universal Technical Institute of Arizona Inc	Universal Technical Institute of Arizona Inc	6	Private for-profit, 2-year	1789	71	1860	0	0	0	0	0	0	0	0	0	0	0	0	0
106	106102	Western International University												0	0	0	0	0	0	0
107	106111	Empire Beauty School-NW Phoenix	Empire Beauty School-NW Phoenix	Empire Beauty School-NW Phoenix	Empire Beauty School-NW Phoenix	9	Private for-profit, less-than 2-year	6	103	109	0	0	0	0	0	0	0	0	0	0	0	0	0
108	106148	Yavapai College									0	0	0	0	0	0	0	0	0	0	0	0	0
109	106245001	University of Arkansas at Little Rock	University of Arkansas at Little Rock	University of Arkansas at Little Rock	University of Arkansas at Little Rock	1	Public, 4-year or above	4547	7077	11624	0	0	0	0	0	0	0	0	0	0	0	0	0
110	106245002	University of Arkansas at Little Rock	University of Arkansas at Little Rock	University of Arkansas at Little Rock		1	Public, 4-year or above	4547	7077	11624	0	0	0	0	0	0	0				0	0	0
111	106245003	University of Arkansas at Little Rock	University of Arkansas at Little Rock	University of Arkansas at Little Rock		1	Public, 4-year or above	4547	7077	11624	0	0	0	0	0	0	0				0	0	0
112	106245004	University of Arkansas at Little Rock	University of Arkansas at Little Rock			1	Public, 4-year or above	4547	7077	11624	0	0	0	0	0	0					0	0	0
113	106263001	University of Arkansas for Medical Sciences	University of Arkansas for Medical Sciences	University of Arkansas for Medical Sciences	University of Arkansas for Medical Sciences	1	Public, 4-year or above	907	1929	2836	0	0	0	0	0	0	0	0	0	0	0	0	0
114	106263002	University of Arkansas for Medical Sciences				1	Public, 4-year or above	907	1929	2836	0	0									0	0
115	106281001		ABC Beauty College Inc	ABC Beauty College Inc	ABC Beauty College Inc									0	0	0	0	0	0	0
116	106306001	Arkansas Baptist College	Arkansas Baptist College	Arkansas Baptist College	Arkansas Baptist College	2	Private nonprofit, 4-year or above	403	190	593	0	0	0	0	0	1	0	0	0	0	0	0	0
117	106315001	Arkansas Beauty School-Little Rock	Arkansas Beauty School-Little Rock	Arkansas Beauty School	Arkansas Beauty School	9	Private for-profit, less-than 2-year	5	124	129	0	0	0	0	0	0	0	0	0	0	0	0	0
118	106324001	Arkansas Beauty College	Arkansas Beauty College	Arkansas Beauty College	Arkansas Beauty College	9	Private for-profit, less-than 2-year	2	25	27	0	0	0	0	0	0	0	0	0	0	0	0	0
119	106342001	Lyon College	Lyon College	Lyon College	Lyon College	2	Private nonprofit, 4-year or above	369	303	672	0	0	0	0	0	0	0	0	0	0	0	0	0
All Crime Data

 

[awiltz]

I manually modified several of the lines from Post #5 to show what I'm trying to do. I should note that the data starts in Row 2 with the header seen in row 98 in row 1. I added the header on row 98 so the labels were available (I already manually did everything prior to 98. The data also goes out to Column FX but because of the limit of cells using XL2BB I didn't put the all in but they are just numerical values like all the columns from K on.

 

[Fluff]

Cross posted Consolidate Rows Based on Partial Information in Column

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.