+ Reply to Thread
Results 1 to 7 of 7

Invert List by Groups

  1. #1
    Registered User
    Join Date
    08-22-2017
    Location
    Virginia, United States
    MS-Off Ver
    16.0.8326.2076
    Posts
    16

    Invert List by Groups

    I have data in a column that is dynamic and that may be either text or number. The values within the column are "grouped" by cells containing "" values resulting from an IF condition, i.e., ISBANK=FALSE, but the cell shows as empty.

    An example data set would be the following:

    7
    5
    3

    11
    9A

    15
    13

    25B
    23
    21
    19
    17

    I would like to find the most efficient way to go to a new column in a new sheet and reverse the numbers within each group, but keep the order of the groups unchanged. The result would be:

    3
    5
    7

    9A
    11

    13
    15

    17
    19
    21
    23
    25B

    I'm at a bit of a loss here. I've been toying with it for a while now, but without much luck.

    Can anyone get me headed in the right direction?

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Invert List by Groups

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    08-22-2017
    Location
    Virginia, United States
    MS-Off Ver
    16.0.8326.2076
    Posts
    16

    Re: Invert List by Groups

    No problem. See attached. Column A on the SS10 sheet has the source data. I am trying to find a way to populate column A on the HGL10 sheet so that it returns the values in the order that I've manually entered them for this example.

    I'm familiar with this...

    =INDEX(List,COUNTA(List)+ROW(List) - ROW(),1)

    ...but this flips the list in it's entirety. I want to flip the list per section. I'm hoping there's a clever way to modify this so that it searches for the breaks and can sort between them.

    All help appreciated! Thanks.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-22-2017
    Location
    Virginia, United States
    MS-Off Ver
    16.0.8326.2076
    Posts
    16

    Re: Invert List by Groups

    Not sure what the rules are for bumping... so, uh... BUMP?

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Invert List by Groups

    This proposal employs two helper columns (U:V) on the SS10 sheet.
    The helper columns may be moved and/or hidden for aesthetic purposes.
    Column U is populated using: =IF(A3="","",IF(OR(ISTEXT(VALUE(LEFT(A2,1))),A2=""),SUM(MAX(U$2:U2),1),U2))
    Column V is populated using: =IF(U3="",SUM(MAX(V$2:V2),0.1),SUM(U3,(COUNTIFS(U$3:U$16,U3)-COUNTIFS(U$3:U3,U3)+1)/10))
    The inverted list per group column (B on the HGL10 sheet) is populated using: =INDEX('SS10'!A$3:A$16,AGGREGATE(15,6,(ROW(A$3:A$16)-ROW(A$2))/('SS10'!V$3:V$16=SMALL('SS10'!V$3:V$16,ROWS(A$1:A1))),1))
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Registered User
    Join Date
    08-22-2017
    Location
    Virginia, United States
    MS-Off Ver
    16.0.8326.2076
    Posts
    16

    Re: Invert List by Groups

    A thing of beauty! Works like a charm, thanks so much!

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Invert List by Groups

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. Invert list of IP addresses
    By tizzen33 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-18-2016, 11:19 PM
  2. [SOLVED] Sorting List / Groups
    By boothexcel in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-22-2015, 12:54 PM
  3. [SOLVED] Counting groups in a list
    By ghellers in forum Excel General
    Replies: 4
    Last Post: 12-12-2012, 12:36 PM
  4. how to invert a list of data with macro?
    By rukia in forum Excel General
    Replies: 5
    Last Post: 10-20-2009, 08:00 AM
  5. Splitting a list into groups
    By tay4432 in forum Excel General
    Replies: 2
    Last Post: 07-12-2008, 04:54 PM
  6. Permutations of a list (groups of 3)
    By xlwho in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 09-20-2007, 06:51 PM
  7. [SOLVED] Ranking a List which must be within GROUPS
    By Gary Jordan in forum Excel General
    Replies: 0
    Last Post: 05-24-2006, 09:15 AM

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