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

awiltz

New Member
Joined
Jun 6, 2020
Messages
9
Office Version
365
Platform
Windows
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

awiltz

New Member
Joined
Jun 6, 2020
Messages
9
Office Version
365
Platform
Windows
UNITID_PINSTNMMURD17MURD16MURD15MURD14MURD13MURD12MURD11MURD10MURD9MURD8NEG_M17NEG_M16NEG_M15NEG_M14NEG_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

Well-known Member
Joined
Dec 3, 2018
Messages
11,709
Office Version
2007
Platform
Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,709
Office Version
2007
Platform
Windows
Hi and welcome to MrExcel.
Now carry out the process with those data that you put, to see the result that you want.
 

awiltz

New Member
Joined
Jun 6, 2020
Messages
9
Office Version
365
Platform
Windows
All Crime Data.xlsx
ABCDEFGHIJKLMNOPQRSTUVW
98UNITID_PINSTNMINSTNM14INSTNM11INSTNM08sector_cdSector_descmen_totalwomen_totalTotalMURD17MURD16MURD15MURD14MURD13MURD12MURD11MURD10MURD9MURD8NEG_M17NEG_M16NEG_M15
99105677001Roberto-Venn School of LuthieryRoberto-Venn School of LuthieryRoberto-Venn School of LuthieryRoberto-Venn School of Luthiery9Private for-profit, less-than 2-year231240000000000000
100105701001Hair Academy of SaffordHair Academy of SaffordHair Academy of Safford0000000
101105747001Scottsdale Community CollegeScottsdale Community CollegeScottsdale Community CollegeScottsdale Community College4Public, 2-year4367509194580000000000000
102105792001South Mountain Community CollegeSouth Mountain Community CollegeSouth Mountain Community CollegeSouth Mountain Community College4Public, 2-year1714240641200000000000000
103105792002South Mountain Community CollegeSouth Mountain Community CollegeSouth Mountain Community CollegeSouth Mountain Community College4Public, 2-year171424064120000000000000
104105792003South Mountain Community College0
105105792004South Mountain Community College4Public, 2-year171424064120000000
106105899001Arizona Christian UniversityArizona Christian UniversityArizona Christian UniversityArizona Christian University2Private nonprofit, 4-year or above4932937860000000000000
107105987001Tucson CollegeTucson CollegeTucson College0000000
108106041001Universal Technical Institute of Arizona IncUniversal Technical Institute of Arizona IncUniversal Technical Institute of Arizona IncUniversal Technical Institute of Arizona Inc6Private for-profit, 2-year17897118600000000000000
109106102001Western International UniversityWestern International UniversityWestern International University0000000
110106102002Western International UniversityWestern International University0000
111106102003Western International UniversityWestern International University0000
112106102004Western International UniversityWestern International University0000
113106102007Western International UniversityWestern International University0000
114106111001Empire Beauty School-NW PhoenixEmpire Beauty School-NW PhoenixEmpire Beauty School-NW PhoenixEmpire Beauty School-NW Phoenix9Private for-profit, less-than 2-year61031090000000000000
115106148001Yavapai CollegeYavapai CollegeYavapai CollegeYavapai College4Public, 2-year3172419873700000000000000
116106148002Yavapai CollegeYavapai CollegeYavapai CollegeYavapai College4Public, 2-year3172419873700000000000000
117106148003Yavapai CollegeYavapai CollegeYavapai CollegeYavapai College4Public, 2-year3172419873700000000000000
118106148004Yavapai CollegeYavapai CollegeYavapai CollegeYavapai College4Public, 2-year3172419873700000000000000
119106148005Yavapai CollegeYavapai CollegeYavapai CollegeYavapai College4Public, 2-year3172419873700000000000000
120106148006Yavapai CollegeYavapai CollegeYavapai CollegeYavapai College4Public, 2-year3172419873700000000000000
121106245001University of Arkansas at Little RockUniversity of Arkansas at Little RockUniversity of Arkansas at Little RockUniversity of Arkansas at Little Rock1Public, 4-year or above45477077116240000000000000
122106245002University of Arkansas at Little RockUniversity of Arkansas at Little RockUniversity of Arkansas at Little Rock1Public, 4-year or above45477077116240000000000
123106245003University of Arkansas at Little RockUniversity of Arkansas at Little RockUniversity of Arkansas at Little Rock1Public, 4-year or above45477077116240000000000
124106245004University of Arkansas at Little RockUniversity of Arkansas at Little Rock1Public, 4-year or above4547707711624000000000
125106263001University of Arkansas for Medical SciencesUniversity of Arkansas for Medical SciencesUniversity of Arkansas for Medical SciencesUniversity of Arkansas for Medical Sciences1Public, 4-year or above907192928360000000000000
126106263002University of Arkansas for Medical Sciences1Public, 4-year or above907192928360000
127106281001ABC Beauty College IncABC Beauty College IncABC Beauty College Inc0000000
128106306001Arkansas Baptist CollegeArkansas Baptist CollegeArkansas Baptist CollegeArkansas Baptist College2Private nonprofit, 4-year or above4031905930000010000000
129106315001Arkansas Beauty School-Little RockArkansas Beauty School-Little RockArkansas Beauty SchoolArkansas Beauty School9Private for-profit, less-than 2-year51241290000000000000
130106324001Arkansas Beauty CollegeArkansas Beauty CollegeArkansas Beauty CollegeArkansas Beauty College9Private for-profit, less-than 2-year225270000000000000
131106342001Lyon CollegeLyon CollegeLyon CollegeLyon College2Private nonprofit, 4-year or above3693036720000000000000
All Crime Data
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,709
Office Version
2007
Platform
Windows
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

New Member
Joined
Jun 6, 2020
Messages
9
Office Version
365
Platform
Windows
I'm not sure what process you are talking about?
 

awiltz

New Member
Joined
Jun 6, 2020
Messages
9
Office Version
365
Platform
Windows
All Crime Data.xlsx
ABCDEFGHIJKLMNOPQRSTUVW
98UNITID_PINSTNMINSTNM14INSTNM11INSTNM08sector_cdSector_descmen_totalwomen_totalTotalMURD17MURD16MURD15MURD14MURD13MURD12MURD11MURD10MURD9MURD8NEG_M17NEG_M16NEG_M15
99105677Roberto-Venn School of LuthieryRoberto-Venn School of LuthieryRoberto-Venn School of LuthieryRoberto-Venn School of Luthiery9Private for-profit, less-than 2-year231240000000000000
100105701Hair Academy of SaffordHair Academy of SaffordHair Academy of Safford0000000
101105747Scottsdale Community CollegeScottsdale Community CollegeScottsdale Community CollegeScottsdale Community College4Public, 2-year4367509194580000000000000
102105792South Mountain Community College0000000000000
103105899001Arizona Christian UniversityArizona Christian UniversityArizona Christian UniversityArizona Christian University2Private nonprofit, 4-year or above4932937860000000000000
104105987001Tucson CollegeTucson CollegeTucson College0000000
105106041001Universal Technical Institute of Arizona IncUniversal Technical Institute of Arizona IncUniversal Technical Institute of Arizona IncUniversal Technical Institute of Arizona Inc6Private for-profit, 2-year17897118600000000000000
106106102Western International University0000000
107106111Empire Beauty School-NW PhoenixEmpire Beauty School-NW PhoenixEmpire Beauty School-NW PhoenixEmpire Beauty School-NW Phoenix9Private for-profit, less-than 2-year61031090000000000000
108106148Yavapai College0000000000000
109106245001University of Arkansas at Little RockUniversity of Arkansas at Little RockUniversity of Arkansas at Little RockUniversity of Arkansas at Little Rock1Public, 4-year or above45477077116240000000000000
110106245002University of Arkansas at Little RockUniversity of Arkansas at Little RockUniversity of Arkansas at Little Rock1Public, 4-year or above45477077116240000000000
111106245003University of Arkansas at Little RockUniversity of Arkansas at Little RockUniversity of Arkansas at Little Rock1Public, 4-year or above45477077116240000000000
112106245004University of Arkansas at Little RockUniversity of Arkansas at Little Rock1Public, 4-year or above4547707711624000000000
113106263001University of Arkansas for Medical SciencesUniversity of Arkansas for Medical SciencesUniversity of Arkansas for Medical SciencesUniversity of Arkansas for Medical Sciences1Public, 4-year or above907192928360000000000000
114106263002University of Arkansas for Medical Sciences1Public, 4-year or above907192928360000
115106281001ABC Beauty College IncABC Beauty College IncABC Beauty College Inc0000000
116106306001Arkansas Baptist CollegeArkansas Baptist CollegeArkansas Baptist CollegeArkansas Baptist College2Private nonprofit, 4-year or above4031905930000010000000
117106315001Arkansas Beauty School-Little RockArkansas Beauty School-Little RockArkansas Beauty SchoolArkansas Beauty School9Private for-profit, less-than 2-year51241290000000000000
118106324001Arkansas Beauty CollegeArkansas Beauty CollegeArkansas Beauty CollegeArkansas Beauty College9Private for-profit, less-than 2-year225270000000000000
119106342001Lyon CollegeLyon CollegeLyon CollegeLyon College2Private nonprofit, 4-year or above3693036720000000000000
All Crime Data
 

awiltz

New Member
Joined
Jun 6, 2020
Messages
9
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,654
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,055
Messages
5,466,305
Members
406,474
Latest member
osama beskales

This Week's Hot Topics

Top