+ Reply to Thread
Results 1 to 5 of 5

Consolidate Rows Based on Partial Information in Column

  1. #1
    Registered User
    Join Date
    06-06-2020
    Location
    Massachusetts
    MS-Off Ver
    Office365
    Posts
    2

    Angry Consolidate Rows Based on Partial Information in Column

    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

  2. #2
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,894

    Re: Consolidate Rows Based on Partial Information in Column

    It's best to attach a sample file that shows expected results. Please see the yellow banner at the top of this page and follow those instructions.

  3. #3
    Registered User
    Join Date
    06-06-2020
    Location
    Massachusetts
    MS-Off Ver
    Office365
    Posts
    2

    Re: Consolidate Rows Based on Partial Information in Column

    Attached is a sample of the data. Rows 87-107 have been manually processed and 108-126 need to be processed. Rows 108-111 would be the first example of rows that need to be combined because the first 6 numbers in Column A are the same.
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,049

    Re: Consolidate Rows Based on Partial Information in Column

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. As you are new her, I will provide thee link for you this time: https://www.mrexcel.com/board/thread...olumn.1136413/.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  5. #5
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,894

    Re: Consolidate Rows Based on Partial Information in Column

    For some reason I couldn't get the formulas to work as I wanted because of text vs. numbers, but here's what I did to get results.

    First, I put your data into a table. You don't have to do this but I think it's simpler so you don't have to change your formulas.
    Second, because I had trouble doing matches with wildcards because of numbers, I inserted a column to the left of your data to get the first 6 digits (=left(A2,6).

    Since you are using O365, I the used Dynamic Array Formulas to get your results. To get the unique first 6 characters, I used this formula:
    =UNIQUE(Table1[Column1]) (or UNIQUE(A2:A126)
    Then the formula next to that one is:
    =XLOOKUP($B128#,Table1[[Column1]:[Column1]],Table1[INSTNM],"Not found",0,1)
    you can then drag this formula across to the right before you want to start totaling the numbers.

    Where you total the numbers, I entered this formula:
    =SUMIFS(Table1[MURD17],Table1[[Column1]:[Column1]],$B128#)

    This formula you can drag all the way across the rest of your rows.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Consolidate Rows of Data Based on Single Column
    By orutulsa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-17-2014, 09:03 AM
  2. Deleting rows based on partial word in certain column
    By bmckenzie93 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-17-2014, 03:51 PM
  3. In excel how can I consolidate information in rows based on 2 critera?
    By kevin.willhoit in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-08-2013, 10:52 AM
  4. [SOLVED] Consolidate data in 2 rows into 1 row, based on column criteria
    By PeS in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-22-2012, 11:41 AM
  5. Matching the rows with the partial information
    By Dhruva101 in forum Excel General
    Replies: 0
    Last Post: 08-28-2006, 07:16 PM
  6. merging information from partial duplicate rows
    By Todd in forum Excel General
    Replies: 2
    Last Post: 08-17-2006, 04:50 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1