+ Reply to Thread
Results 1 to 20 of 20

How to find the majority value of a column until a value changes in another column.

  1. #1
    Registered User
    Join Date
    11-20-2022
    Location
    USA
    MS-Off Ver
    MS 365
    Posts
    12

    How to find the majority value of a column until a value changes in another column.

    I have a spreadsheet where I am trying to find the majority party until the the state value changes in the row. I have included a sample of my spreadsheet that I am working in, which also includes my novice attempt at the formula. So in essence I am trying to populate the majority party of each individual state. Any assistance is appreciated in advance!

    House_Sample.xlsx

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: How to find the majority value of a column until a value changes in another column.

    I don't see expected result but maybe this

    STATE ABBREVIATION PARTY Count
    CA Democrat
    42
    FL Republican
    15
    IL Democrat
    13
    FL Democrat
    12
    CA Republican
    11
    GA Republican
    8
    GA Democrat
    6
    AL Republican
    6
    CT Democrat
    5
    IL Republican
    5
    AZ Democrat
    5
    CO Democrat
    4
    AR Republican
    4
    AZ Republican
    4
    CO Republican
    3
    ID Republican
    2
    HI Democrat
    2
    AL Democrat
    1
    AK Republican
    1
    DE Democrat
    1
    DC Democrat
    1


    or

    STATE ABBREVIATION Party Count
    AL Republican,Democrat
    2
    AZ Democrat,Republican
    2
    CA Republican,Democrat
    2
    CO Democrat,Republican
    2
    FL Republican,Democrat
    2
    GA Republican,Democrat
    2
    IL Democrat,Republican
    2
    AK Republican
    1
    AR Republican
    1
    CT Democrat
    1
    DC Democrat
    1
    DE Democrat
    1
    HI Democrat
    1
    ID Republican
    1
    Last edited by sandy666; 11-21-2022 at 09:09 PM.

  3. #3
    Registered User
    Join Date
    11-20-2022
    Location
    USA
    MS-Off Ver
    MS 365
    Posts
    12

    Re: How to find the majority value of a column until a value changes in another column.

    yes, the second one is what I am trying to achieve. Is there any way if there is a tie to represent split?

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: How to find the majority value of a column until a value changes in another column.

    Quote Originally Posted by roquinn View Post
    yes, the second one is what I am trying to achieve. Is there any way if there is a tie to represent split?
    show what you mean

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: How to find the majority value of a column until a value changes in another column.

    Maybe this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    PHP Code: 
    State    Republican    Democrat    Majority
    Alabama    6    1    Republican
    Alaska    1    0    Republican
    Arizona    4    5    Democrat
    Arkansas    4    0    Republican
    California    11    42    Democrat
    Colorado    3    4    Democrat
    Connecticut    0    5    Democrat
    Delaware    0    1    Democrat
    District of Columbia    0    1    Democrat
    Florida    15    12    Republican
    Georgia    8    6    Republican
    Hawaii    0    2    Democrat
    Idaho    2    0    Republican
    Illinois    5    13    Democrat 
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: How to find the majority value of a column until a value changes in another column.

    Or just this:
    PHP Code: 
    Alabama    Republican
    Alaska    Republican
    Arizona    Democrat
    Arkansas    Republican
    California    Democrat
    Colorado    Democrat
    Connecticut    Democrat
    Delaware    Democrat
    District of Columbia    Democrat
    Florida    Republican
    Georgia    Republican
    Hawaii    Democrat
    Idaho    Republican
    Illinois    Democrat 
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    11-20-2022
    Location
    USA
    MS-Off Ver
    MS 365
    Posts
    12

    Re: How to find the majority value of a column until a value changes in another column.

    House_Example.xlsx- Here is an example of what I am trying to accomplish.

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: How to find the majority value of a column until a value changes in another column.

    sure

    with Power Query

    STATE ABBREVIATION PARTY Cnt
    CT Democrat
    5
    HI Democrat
    2
    DC Democrat
    1
    DE Democrat
    1
    AR Republican
    4
    ID Republican
    2
    AK Republican
    1
    CA Split
    53
    FL Split
    27
    IL Split
    18
    GA Split
    14
    AZ Split
    9
    AL Split
    7
    CO Split
    7

  9. #9
    Registered User
    Join Date
    11-20-2022
    Location
    USA
    MS-Off Ver
    MS 365
    Posts
    12

    Re: How to find the majority value of a column until a value changes in another column.

    I actually need to use the dist field as a join field to another table. So I need it to be formatted like this.
    Attachment 806053

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: How to find the majority value of a column until a value changes in another column.

    invalid attachment, try again

  11. #11
    Registered User
    Join Date
    11-20-2022
    Location
    USA
    MS-Off Ver
    MS 365
    Posts
    12

    Re: How to find the majority value of a column until a value changes in another column.

    Capture1.JPG Format of the table that I need

  12. #12
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: How to find the majority value of a column until a value changes in another column.

    you mean whole table???

  13. #13
    Registered User
    Join Date
    11-20-2022
    Location
    USA
    MS-Off Ver
    MS 365
    Posts
    12

    Re: How to find the majority value of a column until a value changes in another column.

    yes. I have it going all the way back to the year 2000 and joining this table by the dist field.

  14. #14
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: How to find the majority value of a column until a value changes in another column.


  15. #15
    Registered User
    Join Date
    11-20-2022
    Location
    USA
    MS-Off Ver
    MS 365
    Posts
    12

    Re: How to find the majority value of a column until a value changes in another column.

    Is there a formula to use? I need to replicate this to my senate and presidential tables as well.

  16. #16
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: How to find the majority value of a column until a value changes in another column.

    no formula, this is Power Query

  17. #17
    Registered User
    Join Date
    11-20-2022
    Location
    USA
    MS-Off Ver
    MS 365
    Posts
    12

    Re: How to find the majority value of a column until a value changes in another column.

    i've never used power query before. Is this able to be replicated on different tables?

  18. #18
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: How to find the majority value of a column until a value changes in another column.

    here is all in one with collapsed code
    Learn Power Query (link)

    you mean exactly the same structure of the another table?
    if the structure and headers of another table are EXACTLY the same like in your example just copy data from another table and paste into your data in this file then right click on the green table and select Refresh

    if you want formula you'll need to wait for someone else to do that
    Last edited by sandy666; 11-21-2022 at 11:10 PM.

  19. #19
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: How to find the majority value of a column until a value changes in another column.

    Thanks for the rep.

    Formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by TMS; 11-22-2022 at 05:39 AM. Reason: Make A:A absolute = $A:$A

  20. #20
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: How to find the majority value of a column until a value changes in another column.

    You don't have to use the new functions but it does get tedious.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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. vba code : find exact phrase in Column C using Column M and replace with Column N
    By daveexcelforum in forum Excel Programming / VBA / Macros
    Replies: 39
    Last Post: 04-02-2021, 01:05 PM
  2. Formula to find majority value with Excel/VBA
    By SmallJack in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-22-2019, 06:36 AM
  3. Find keyword in column B and print vaue of column E to column G not working
    By moorea21 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-30-2017, 04:58 AM
  4. Replies: 2
    Last Post: 05-23-2017, 08:28 AM
  5. Replies: 9
    Last Post: 06-20-2016, 07:39 AM
  6. Listing the Matching Majority in a Related Column
    By Rschwar23 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-11-2014, 02:19 PM
  7. Replies: 3
    Last Post: 05-28-2014, 12:01 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