Assistance with excluding cells during an INDEX Search

[AceForSale]

Good Afternoon.
Could anyone see if they could assist with excluding cell during an index search.

Currently my spreadsheet has 3 sections.

The first section is the raw data. In Column C is the data I'd like to exclude from the next two sections.

Section Two counting the duplicate hosts total and assigning an ID of which order they will go in.

Section Three will put them in order.

Currently my spreadsheet is working. The only thing is, its including the EXCLUDES. I don't want it to add the excludes to the 2nd and 3rd sections at all.

I've attached the spreadsheet as an example. The cells in red I'm hoping to hide from the spreadsheet and have them not show up at all.

Thank you.

[dflak]

Could't you use a pivot table - Filter out the excluded, sort by count of IP address.

[AceForSale]

Unfortunately I need to get it working in the formula's (non pivot table)

[jason.b75]

Try this array formula in E2, once confirmed, fill down as needed.

=IF(E1="","",IFERROR(INDEX($A:$A,SMALL(IF($C$1:$C$30="",IF(MATCH($A$1:$A$30,$A$1:$A$30,0)=ROW($A$1:$A$30),ROW($A$1:$A$30))),ROWS($E$2:$E2))),""))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[AceForSale]

All set. I figured it out. I just had to add +($C$2:$C$30="Yes")
Thank you to all who assisted.

[kvsrinivasamurthy]

ARRAY formula (see note below) in P2 then drag across up to Column Q. Pl see file.

Please Login or Register  to view this content.

In R2 then drag down.

Please Login or Register  to view this content.