+ Reply to Thread
Results 1 to 8 of 8

Remove rows whose combination of entries are contained in other rows

  1. #1
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    166

    Remove rows whose combination of entries are contained in other rows

    Hi,

    I have a Table 1 containing three columns, where each row contains a combination of entries across the three columns. Some rows have three entries, while others have only two, one or also zero entries.

    I need a formula in Table 2 that removes rows which are contained within other rows in the table, meaning that blank cells function as some kind of "wildcard".

    E.g. in the following four rows, only the fourth row would be kept since this row contains row one, two and three:

    14384954
    14384954 14384960
    14384954 14384960 14381225
    14384954 14384960 14381225


    While in the following example, both rows two, three and four would be kept and row one would be removed:

    14384954
    14384954 14384960
    14384954 14384973
    14384954 14381225


    If this formula could be a dynamic array formula which spills the results across three columns in Table 2, that would be nice.

    Also, if this formula would be able to sort the results in Table 2 by column E, then column F and then column G in ascending order, but pushing blanks to the bottom - as can be seen in Table 3 - that would be very good. A formula to achieve both Table 2 and Table 3 in one go would be perfect.

    I have attached a an example workbook containing the relevant setup.

    Best regards,
    Marbleking
    Last edited by Marbleking; 09-09-2020 at 05:34 PM.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,963

    Re: Remove rows whose combination of entries are contained in other rows

    Try in E3:

    Please Login or Register  to view this content.
    F3:

    Please Login or Register  to view this content.
    G3:

    Please Login or Register  to view this content.
    I3 (array formula)

    Please Login or Register  to view this content.
    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

    Copy I3 across to J3 and K3

    Drag all down

  3. #3
    Forum Contributor
    Join Date
    03-18-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2016 / 2019
    Posts
    242

    Re: Remove rows whose combination of entries are contained in other rows

    Hi

    Attached Power Query Solution

    Please Login or Register  to view this content.
    Last edited by bluesky63; 09-10-2020 at 02:44 AM.
    Christopher Yap

  4. #4
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    166

    Re: Remove rows whose combination of entries are contained in other rows

    Thanks for the formulas, bebo021999, and the PQ solution, bluesky63! At some point I will learn more about harnessing the power of Power Query.

    I worked a bit more on the problem and arrived at these formulas:

    E3:

    Please Login or Register  to view this content.
    F3:

    Please Login or Register  to view this content.
    G3:

    Please Login or Register  to view this content.
    I3:

    Please Login or Register  to view this content.
    All formulas are dynamic array formulas creating spilled ranges in Excel for MS Office 365. Except for the one in cell I3, these formulas are quite large and difficult to read.

    Does anybody have a better dynamic array formula solution that perhaps do in one operation what the formulas in cell E3, F3 and G3 do together?

    Regards,
    Marbleking

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,845

    Re: Remove rows whose combination of entries are contained in other rows

    Please try

    =FILTER(A3:C687,(A3:A687<>A4:A688)+(B3:B687<>B4:B688)*(B3:B687<>"")+(C3:C687<>C4:C688)*(C3:C687<>""))

    or Power Query

    Please Login or Register  to view this content.
    Last edited by Bo_Ry; 09-10-2020 at 08:19 AM.

  6. #6
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    166

    Re: Remove rows whose combination of entries are contained in other rows

    Hi,

    Thanks, Bo_Ry! I have tested your formula on other data sets of the same type, but am not able to get the correct filtered results. The output seen in the green columns Q, R, S in the attachment is what I am trying to achieve. I have put your formula in the blue columns V, W, X for comparison. Could you please have a look?

    Best regards,
    Marbleking

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,845

    Re: Remove rows whose combination of entries are contained in other rows

    Please try

    =SORT(FILTER(I2#,COUNTIFS(INDEX(I2#,,1),IF(INDEX(I2#,,1)="","<>",INDEX(I2#,,1)),INDEX(I2#,,2),IF(INDEX(I2#,,2)="","<>",INDEX(I2#,,2)),INDEX(I2#,,3),IF(INDEX(I2#,,3)="","<>",INDEX(I2#,,3)))=1),{1,2,3})

  8. #8
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    166

    Re: Remove rows whose combination of entries are contained in other rows

    Hi Bo_Ry! Again, thank you so much. A big relief to see such a neat solution to this problem.

    Best regards,
    Marbleking

+ 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] Remove Duplicate Rows comparing entries in a single column
    By kdraghu in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 06-02-2020, 10:15 AM
  2. [SOLVED] Excel VBA to find Duplicate Entries with multiple column combination and Display Rows Numb
    By mchilapur in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-19-2018, 05:36 AM
  3. Remove Rows based upon list of search terms, copy removed rows to new sheet
    By sam103329 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-26-2013, 10:32 PM
  4. Need: Total Values for Duplicate Entries, Do Not Remove Rows
    By esmithqg in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-23-2013, 02:29 AM
  5. Hide rows when text is not contained on any cell in that row.
    By jruddell in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-02-2012, 09:53 AM
  6. Copy&Paste Certain Data Contained in Rows
    By Trmoon77 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-17-2011, 03:14 PM
  7. Loop to copy rows to another sheet if value not contained in both
    By hotrod6657 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-24-2011, 08:21 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