+ Reply to Thread
Results 1 to 4 of 4

Excel formula to combine a list of girls and boys into one list

  1. #1
    Forum Contributor
    Join Date
    07-06-2020
    Location
    zimbabwe
    MS-Off Ver
    Excel 365
    Posts
    457

    Excel formula to combine a list of girls and boys into one list

    Hi

    I'm kindly asking for excel formula which can combine two different list of boys and girls into one list as shown on the desired outcome.

    Thank you.
    Attached Files Attached Files

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Excel formula to combine a list of girls and boys into one list

    Format both lists (Boys and Girls) as Tables, then you can use Power Query to easily combine:

    Please Login or Register  to view this content.
    Close and Load To a table, to get the combined output.

    See attachment for example.
    Attached Files Attached Files
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Excel formula to combine a list of girls and boys into one list

    Formula (there's probably a better way though)...

    I6
    =IF(J6="","",INDEX($A:$G,SUM(INDEX((TRIM($B$6:$F$100)=$J6)*ROW($B$6:$F$100),)),SUM(INDEX((TRIM($B$6:$F$100)=$J6)*COLUMN($B$6:$F$100),))-1))

    J6
    =SORT(FILTERXML("<A><B>"&TEXTJOIN("</B><B>",,B6:B100,F6:F100)&"</B></A>","//B"))

    K6
    =IF(J6="","",INDEX($A:$G,SUM(INDEX((TRIM($B$6:$F$100)=$J6)*ROW($B$6:$F$100),)),SUM(INDEX((TRIM($B$6:$F$100)=$J6)*COLUMN($B$6:$F$100),))+1))
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Forum Contributor
    Join Date
    07-06-2020
    Location
    zimbabwe
    MS-Off Ver
    Excel 365
    Posts
    457

    Re: Excel formula to combine a list of girls and boys into one list

    Hi Olly and Glenn

    Thank you for the formulas. There are working well.

+ 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. [SOLVED] Combine a formula and list in data validation
    By thecdnmole in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 01-10-2021, 11:25 PM
  2. Combine Product List and Attribute List
    By Stasi_B in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 03-31-2020, 04:02 PM
  3. Replies: 7
    Last Post: 02-25-2020, 10:04 AM
  4. Replies: 1
    Last Post: 02-07-2018, 08:00 AM
  5. [SOLVED] boys or girls or blank
    By makinmomb in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-30-2016, 12:00 PM
  6. [SOLVED] Segregating Boys from Girls
    By wedzmer in forum Excel General
    Replies: 13
    Last Post: 07-10-2014, 09:27 AM
  7. Boys versus Girls
    By gelandl in forum The Water Cooler
    Replies: 9
    Last Post: 03-24-2009, 06:10 AM

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