# Remove rows whose combination of entries are contained in other rows

1. ## 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

2. ## 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. ## 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.``

4. ## 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. ## 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.``

6. ## 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. ## 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. ## 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

#### Thread Information

##### Users Browsing this Thread

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

#### 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