How do I exclude data from 1 column using conditions from another column

**Funky Gibbon** · 12-15-2013, 06:11 AM

Just a simplified version of what I want to do.

Cells A1:A50 contain various names in alphabetical order.
Cells B1:B50 contain number values, anything from 0 - 1,000,000
Cells C1:C5 contains 5 of the names from A1:A50 but are changed weekly

Now, in D1:D5, I want to show the names from A1:A50 that have the highest values in B1:B50. Here's the problem. The names in the exclusion list (C1:C5) will still have values in the B column. I want a formula that ignores the names from C1:C5, even if they have 1 of the highest 5 values in the B column.

Edit: Alternatively, display the top 5 values from B1:B50, again ignoring any value that is generated next to the excluded name.

**Winon** · 12-15-2013, 06:46 AM

Post deleted.

**Funky Gibbon** · 12-15-2013, 06:58 AM

Title changed, as requested.

Apologies and thank you.

**canapone** · 12-15-2013, 07:04 AM

Hi

maybe

Please Login or Register  to view this content.

Regards

**Funky Gibbon** · 12-15-2013, 07:13 AM

Thank you very much Canapone. That did just what I wanted.

**canapone** · 12-15-2013, 07:14 AM

Ciao,

thanks for your kind feedback.

**Funky Gibbon** · 12-15-2013, 07:38 AM

Just noticed a new problem. If 2 of the cells within the B column have the same values only the 1st name, reading down column A is shown on each occasion. In the example placed above, I changed B9 from 984 to 5000. This results in A9 being used twice. Is there a way to find multiple names that have the same values in B.

I apologise that this wasn't included in the original question. It was 1 of those things that I didn't even consider.

**Tony Valko** · 12-15-2013, 10:51 AM

Can you post a SMALL sample file that shows what results you expect?

About 20 rows worth of data is plenty.

**Funky Gibbon** · 12-15-2013, 02:45 PM

Hi Tony, sorry for the delay in getting back to you, busy time of the year. Anyway, the sample file I am using is the 1 posted by Canapone in his 1st post. The actual file did exactly what I asked. My problem, as described above is that if 2 values in the B column match, the result is the name from column A showing twice.

In Canapone's file I changed cell B9 from it's current value of 984 to 5000, same as B29. The result is the name from cell A9 appearing twice in the list generated in the D column. What I want is for individual names to show, even if they have the same value in the corresponding B cell

**Tony Valko** · 12-15-2013, 03:32 PM

Based on the data in CANAPONE's file...

This array formula** entered in D1:

=INDEX(A$1:A$29,MATCH(LARGE(IF(ISNA(MATCH(A$1:A$29,C$1:C$5,0)),B$1:B$29-ROW(B$1:B$29)/100000),ROWS(D$1:D1)),B$1:B$29-ROW(B$1:B$29)/100000,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down as needed.

**Funky Gibbon** · 12-15-2013, 04:11 PM

Thank you Tony. That works perfectly. Your time is much appreciated.

**Tony Valko** · 12-15-2013, 04:13 PM

You're welcome. Thanks for the feedback!

**Funky Gibbon** · 12-15-2013, 05:59 PM

Hello again. After transferring the above solution into my worksheet, I now have a 3rd problem. Under certain conditions, some of the values, that are in column B in the example used in this post, will be text.
For the sake of example, I substituted the value in B1 for the word 'Full'. This now returns a #VALUE! in D1:D5. It's the same for a blank cell in B:B too.
The blanks I'm not too worried about as they don't occur. The text cells do.

How do I change "=INDEX(A$1:A$29,MATCH(LARGE(IF(ISNA(MATCH(A$1:A$29,C$1:C$5,0)),B$1:B$29-ROW(B$1:B$29)/100000),ROWS(D$1:D1)),B$1:B$29-ROW(B$1:B$29)/100000,0))" to ignore the text cells?

**Tony Valko** · 12-15-2013, 06:09 PM

Post a SMALL sample file so I can see what the data looks like.

I won't download files >50kb so make it a SMALL file.

Like I said before, 20 rows worth of data is plenty.

**Funky Gibbon** · 12-15-2013, 06:13 PM

How do I post a sample file? The attachments button in the 'Go Advanced' section doesn't work.

Failing posting a sample file, I am still using the 1 from the top of the thread for experimenting. This is the 1 I changed the value in, as stated in my previous post.

**Tony Valko** · 12-15-2013, 06:30 PM

I'm not sure how to attach a file. I never need to do it!

Try this:

=INDEX(A$1:A$29,MATCH(LARGE(IF(ISNUMBER(B$1:B$29),IF(ISNA(MATCH(A$1:A$29,C$1:C$5,0)),B$1:B$29-ROW(B$1:B$29)/100000)),ROWS(D$1:D1)),B$1:B$29-ROW(B$1:B$29)/100000,0))

Still array entered.

**Funky Gibbon** · 12-15-2013, 06:42 PM

Great! Unfortunately I can't add any more reputation via the button on your post until I award it to someone else. All I can do is offer Kudos here.

Thank you again. Hopefully I can put this to bed now.

**Tony Valko** · 12-15-2013, 06:43 PM

Originally Posted by Tony Valko

I'm not sure how to attach a file. I never need to do it!

I should say:

I don't remember how to upload and attach a file.

When I have done it in the past I found it to be unintuitive so I just use another file hosting site.

**Tony Valko** · 12-15-2013, 06:44 PM

Good deal. Thanks for the feedback!

How do I exclude data from 1 column using conditions from another column

LinkBack

Thread Tools

Rate This Thread

Display

How do I exclude data from 1 column using conditions from another column

Re: Exclusion list

Re: Exclusion list

Re: How do I exclude data from 1 column using conditions from another column

Re: How do I exclude data from 1 column using conditions from another column

Re: How do I exclude data from 1 column using conditions from another column

Re: How do I exclude data from 1 column using conditions from another column

Re: How do I exclude data from 1 column using conditions from another column

Re: How do I exclude data from 1 column using conditions from another column

Re: How do I exclude data from 1 column using conditions from another column

Re: How do I exclude data from 1 column using conditions from another column

Re: How do I exclude data from 1 column using conditions from another column

Re: How do I exclude data from 1 column using conditions from another column

Re: How do I exclude data from 1 column using conditions from another column

Re: How do I exclude data from 1 column using conditions from another column

Re: How do I exclude data from 1 column using conditions from another column

Re: How do I exclude data from 1 column using conditions from another column

Re: How do I exclude data from 1 column using conditions from another column

Re: How do I exclude data from 1 column using conditions from another column

Thread Information

Users Browsing this Thread

Similar Threads

Exclusion Filter?

Excel 2008 : excel for MAC 2008 version; how to create an excel exclusion list

Cannot AutoFilter By Exclusion using VBA

Find and replace exclusion

Exclusion of Values in SUM/Percentage

Bookmarks

Bookmarks

Posting Permissions