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

 

[DanteAmor]

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

Forget the word "process".
You have these data:

Libro1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	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
2	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
3	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
4	105792003			South Mountain Community College													0
Hoja1

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)

You want to show how you put it on a 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.

You want a sum, but your example only has zeros, so what do you want to sum?

 

[awiltz]

The sum is what I want. The portion of data I put up is murder rates which are typically 0 or 1. Here is a set of data for just 1 college with the last row showing how I manually combine the rows above into 1 row. What I would typically do after this is delete all the rows but the last one.

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	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA	EB	EC	ED	EE	EF	EG	EH	EI	EJ	EK	EL	EM	EN	EO	EP	EQ	ER	ES	ET	EU	EV	EW	EX	EY	EZ	FA	FB	FC	FD	FE	FF	FG	FH	FI	FJ	FK	FL	FM	FN	FO	FP	FQ	FR	FS	FT	FU	FV	FW	FX
250	109785001	Azusa Pacific University	Azusa Pacific University	Azusa Pacific University	Azusa Pacific University	2	Private nonprofit, 4-year or above	3097	6829	9926	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	3	7	4	1	0	6	4	0	0	0	0	0	0	10	4	8	4	6	1	0	0	0	0	0	0	0	0	0	0	0	0	1	1	2	0	0	1	1	0	0	0	0	0	5	4	29	43	38	16	11	41	17	6	16	48	3	6	3	2	7	6	6	3	6	4	2	0	0	0	0	0	0	0	0	0	0	0	1	0	1	0	0	1	0	0	1	1	6	10	10	2	3	0	1	0	2	5	0	2	1	2	10	0	0	0	1	0	35	28	20	11	19	11	5	0	7	7	0	0	0	0	0	2	0	0	3	1	122	122	158	59	56	16	15	27	28	0	3	1	2	2	1	1	5	0	2	5	3	2
251	109785002	Azusa Pacific University	Azusa Pacific University	Azusa Pacific University	Azusa Pacific University	2	Private nonprofit, 4-year or above	3097	6829	9926	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	2	0	0	0	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0
252	109785004	Azusa Pacific University	Azusa Pacific University	Azusa Pacific University	Azusa Pacific University	2	Private nonprofit, 4-year or above	3097	6829	9926	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
253	109785005	Azusa Pacific University	Azusa Pacific University	Azusa Pacific University	Azusa Pacific University	2	Private nonprofit, 4-year or above	3097	6829	9926	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	2	0	0	0	0	0	0	1	0	1	0	1	1	0	0	0	0	0	0	3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
254	109785006	Azusa Pacific University	Azusa Pacific University	Azusa Pacific University	Azusa Pacific University	2	Private nonprofit, 4-year or above	3097	6829	9926	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
255	109785007	Azusa Pacific University	Azusa Pacific University	Azusa Pacific University	Azusa Pacific University	2	Private nonprofit, 4-year or above	3097	6829	9926	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	0	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
256	109785008	Azusa Pacific University	Azusa Pacific University	Azusa Pacific University	Azusa Pacific University	2	Private nonprofit, 4-year or above	3097	6829	9926	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0
257	109785009		Azusa Pacific University	Azusa Pacific University	Azusa Pacific University									0	0	0	0	0	0	0				0	0	0	0	0	0	0				0				0				0		0	0	0	0	0	0	0	0	0	0	0	0	0				0	0	0	0	0	0	0				0	0	0	0	0	0	0				1	0	2	0	0	0	0				0	0	0	0	0	0	0				0	0	0	0	0	0	0						0	0	0	0	0	0	0				0	0	0	0	0	0	0				0	0	0	0	0	0	0				0	0	0	0	0	0	0				0	0	0	0	0	0	0				0	0	0	0	0	0	0				0				0				0
258	109785	Azusa Pacific University									0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	3	7	4	1	0	6	4	0	0	0	0	0	0	10	4	8	4	6	1	0	0	0	0	0	0	0	0	0	0	1	0	2	1	2	0	0	1	2	0	0	0	1	2	5	4	29	44	38	18	12	43	21	7	18	49	5	7	3	2	7	6	12	4	6	4	2	0	0	0	0	0	0	0	0	0	0	0	1	0	1	0	0	1	0	0	1	1	6	10	10	2	3	0	1	0	2	5	0	3	1	4	10	0	0	0	2	0	35	28	20	11	19	11	5	0	7	7	0	0	0	0	0	2	0	0	5	1	122	122	158	59	56	16	15	27	28	0	3	1	2	2	1	1	5	0	2	6	4	2
All Crime Data

 

[DanteAmor]

Sorry, but I still don't understand.
You have this:

varios 07jun2020.xlsm
	A	B				F	G	H	I	J	K	L
1	UNITID_P	INSTNM				sector_cd	Sector_desc	men_total	women_total	Total
2	109785001	Azusa Pacific University				2	Private nonprofit, 4-year or above	3097	6829	9926	6	3
3	109785002	Azusa Pacific University				2	Private nonprofit, 4-year or above	3097	6829	9926	0	0
4	109785004	Azusa Pacific University				2	Private nonprofit, 4-year or above	3097	6829	9926	0	0
5	109785005	Azusa Pacific University				2	Private nonprofit, 4-year or above	3097	6829	9926	3	0
6	109785006	Azusa Pacific University				2	Private nonprofit, 4-year or above	3097	6829	9926	0	1
7	109785007	Azusa Pacific University				2	Private nonprofit, 4-year or above	3097	6829	9926	2	0
8	109785008	Azusa Pacific University				2	Private nonprofit, 4-year or above	3097	6829	9926	1	0
9	109785009										0	0
Sheet1

And you want this:

varios 07jun2020.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	UNITID_P	INSTNM	INSTNM14	INSTNM11	INSTNM08	sector_cd	Sector_desc	men_total	women_total	Total
2	109785	Azusa Pacific University									12	4
Sheet2

Obviously for all rows and for all columns.

 

[awiltz]

That's correct. Take all entries for 1 university and condense it down into 1 line just like you have it.

 

[DanteAmor]

Try this.
Your data on sheet1, the results on sheet2. Replace "Sheet1" and "Sheet2" in the macro with the names of your sheets.

Sub Sum_Columns()
  Dim i As Long, j As Long, k  As Long, lc As Long
  Dim a As Variant, b As Variant, dic As Object
  
  With Sheets("Sheet1")
    lc = .Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
    a = .Range("A2", .Cells(.Range("A" & Rows.Count).End(3).Row, lc)).Value2
    ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  End With
  Set dic = CreateObject("Scripting.Dictionary")
  
  For i = 1 To UBound(a, 1)
    If Not dic.exists(Left(a(i, 1), 6)) Then
      j = j + 1
      dic(Left(a(i, 1), 6)) = j
      b(j, 1) = Left(a(i, 1), 6)
      For k = 2 To 10
        b(j, k) = a(i, k)
      Next
    End If
    j = dic(Left(a(i, 1), 6))
    For k = 11 To UBound(a, 2)
      b(j, k) = b(j, k) + a(i, k)
    Next
  Next
  With Sheets("Sheet2")
    .Rows("2:" & Rows.Count).ClearContents
    .Range("A2").Resize(j, UBound(b, 2)).Value = b
  End With
End Sub

 

[awiltz]

Thank you for putting this together. I ran it on the data and it appears to have worked exactly how I wanted. I'm going to do some spot checking to make sure but if it all works out you saved me hundreds of hours. I cannot thank you enough. Please let me know if I can compensate you for your work.

 

[DanteAmor]

You already did it, thanking the work is enough. Let me know if you have any questions.