Extracting Data Based on Yes or No Condition

**leowyatt** · 04-15-2013, 09:12 AM

Hi there,

I am looking for a solution to my problem and I hope one of you guys can help me out. I am trying to pull data from a "main" sheet based on the condition of yes or no. If the cell in the list equals "Y" then that data is pulled. If it equals "N" then it isn't. Here is an example:

"Main" Sheet

Sites Metric 1 Metric 2 Metric 3 List 1 List 2 List 3 List 4
http://www.site1.com 96.99 100 7.98 Y Y Y Y
http://www.site2.com 91.83 96.8 6.71 Y N N N
http://www.site3.com 88.45 96.11 6.03 Y Y Y Y
http://www.site4.com 90.58 98.48 7 N N N N
http://www.site5.com 90.24 96.51 6.43 N Y Y Y
http://www.site6.com 74.74 96.19 6.07 Y Y N Y
http://www.site7.com 65.53 93.46 5.2 N N Y N
http://www.site8.com 82.81 95.45 5.82 Y Y Y Y
http://www.site9.com 60.19 81.11 3.95 N Y Y Y
http://www.site10.com 94.8 93.68 6.88 Y N N Y

"List 1" Sheet

Sites Metric 1 Metric 2 Metric 3

"List 2" Sheet

Sites Metric 1 Metric 2 Metric 3

"List 3" Sheet

Sites Metric 1 Metric 2 Metric 3

I need the data to be pulled across based yes or no (or true or false)

So.. site1.com would be displayed in all 4 list tabs
site2.com would only be displaying in list 1 tab
site3.com would be displayed in all 4 list tabs
site4.com would be displayed in none of the tabs
and so on.....

Please help as this is really causing me a headache...

I have attached 2 example sheets (I have 2010 version so I have xlsx... but saved as older one too .xls)

Thank you

**ChemistB** · 04-15-2013, 09:27 AM

One way is using Arrayed formulas (another way would be using 3 dummy columns)

In List1 sheet, A2 entered with CNTRL SHFT ENTER instead of a simple ENTER( you'll see {} around the formula if done properly)

=IFERROR(INDEX('Main List'!$A$2:$A$11,SMALL(IF('Main List'!$E$2:$E$11="Y",ROW($E$2:$E$11)-ROW($E$2)+1,5E+100),ROW(A1))),"")

Then in B2, you'd just need to change what you are Indexing
=IFERROR(INDEX('Main List'!$B$2:$B$11,SMALL(IF('Main List'!$E$2:$E$11="Y",ROW($E$2:$E$11)-ROW($E$2)+1,5E+100),ROW(A1))),"")
and so on.
I did List 1. Let me know if you have problems duplicating it.

**leowyatt** · 04-15-2013, 09:41 AM

Thank you so much for that, it seems to work great.

Would be able to do me a favor and explain why that formula works and how it works. Although it is much easier to be spoon fed and walk away, I do want to understand how and why that formula works.

Thank you.

**leowyatt** · 04-15-2013, 09:56 AM

Hi there,

Had a play with your spreadsheet and when I copied the formula across to sheet 2, I appear to have a "gap" where site one would be. I changed the Y to an N but it leaves a gap? Is this right?

thanks

**leowyatt** · 04-15-2013, 10:19 AM

Never mind I think I have sussed it out

But would still like you to explain how it worked

Thanks

**ChemistB** · 04-15-2013, 10:19 AM

In List2!A2, you did not enter it as an array. In your other columns, you are still referencing Col E of Main (List1 Y or N) instead of F.

How it works...
When you enter a formula as an array, Excel sets aside memory to calculate intermediate values.
i.e. SUM($A$1:$A$5-$B$1:$B$5) first calculates A1-B1, A2-B2 etc, stores those in memory and then sums them.

=IFERROR(INDEX('Main List'!$A$2:$A$11,SMALL(IF('Main List'!$E$2:$E$11="Y",ROW($E$2:$E$11)-ROW($E$2)+1,5E+100),ROW(A1))),"")

The IFERROR part is just there so when you drag down past the number of legimimate answers, you'll get a blank instead of an error. So lets work from the inside out;

IF('Main List'!$E$2:$E$11="Y",ROW($E$2:$E$11)-ROW($E$2)+1,5E+100)

This creates an array of numbers, going down column E, {Y,Y,Y,N,N,Y,N,Y,N,Y} returns {2-2+1, 3-2+1, 4-2+1, 5E+100, 5 E+100, 7-2+1, 5 E100, 9-2+1, 5 E100, 11-2+1} which simplifies to

{1,2,3, 5E100, 5E100, 6, 5E100, 8, 5E100, 10}
Then we have
SMALL({1,2,3, 5E100, 5E100, 6, 5E100, 8, 5E100, 10}, ROW(A1))
ROW(A1) simplifies to 1 so Excel looks for and returns the smallest value in that array which is 1

Now we have (moving outward)

INDEX('Main List'!$A$2:$A$11, 1) which returns the first value in the array A2:A11
Questions?

**leowyatt** · 04-15-2013, 10:37 AM

Quite complicated but I think I understand, I am glad I asked now because I would never of worked it out. I was trying to accomplish this with vlookup...

Thanks once again for your help

**leowyatt** · 04-19-2013, 06:50 AM

Right i have run into another Excel problem/issue and wondering if someone can help slightly expand this forumula so that i can use it to add a few values together and provide me with a total, rather than providing the first one in the list.

This would really make my life a whole lot easier!

I have attached the excel document, and there are two tabs (Spend and CPL Teams). I need a formula to look through spend and dependant on the department return the Cost cell value, however for some departments there is more than one entry and i'd like (if possible) it to total the values together and return the totalled value.

So far i have amended the formula so it returns the first value in the list, but i dont know how to make it total the values.

Excel Example: Excel Vlookup.xlsx

Formula in Sheet: CPL Teams Cell: B6
=IFERROR(INDEX(Spend!$D$4:$D$32,SMALL(IF(Spend!$C$4:$C$32="General",ROW($C$4:$C$10)-ROW($C$4)+1,5E+100),ROW(A1))),"")
I am testing it for the Team/Department: General

Returns 38.47, but i want it to return the total value of everything marked against General in Sheet: Spend

Thanks in Advance!!! Hope this is possible

**ChemistB** · 04-19-2013, 09:02 AM

For this problem, you want to use a different formula

=SUMPRODUCT(--(Spend!$C$4:$C$32=$A$1), --(TEXT(Spend!$A$4:$A$32,"mmm")=B$2),Spend!$D$4:$D$32)
this does not need to be entered with CNTRL SHFT ENTER

To explain;
--(Spend!$C$4:$C$32=$A$1) returns a 1 if the value in C = A1 (General) and a 0 if it doesnt'
--(TEXT(Spend!$A$4:$A$32,"mmm")=B$2 first converts the value in A to the 3 character abbreviation of the month and then compares it to the header (returning 1's and 0's) (I had to change two headers, July to Jul and Sept to Sep)
D4:D32 simply returns the value in D
So the Sumproduct first multiplies each row in the array together and then sums them
So in row 15 for example
=1 x 1 x 199 = 199
in Row 16
= 0 x 1 x 40 = 0
Hope that helps.

Extracting Data Based on Yes or No Condition

LinkBack

Thread Tools

Rate This Thread

Display

Extracting Data Based on Yes or No Condition

Re: Extracting Data Based on Yes or No Condition

Re: Extracting Data Based on Yes or No Condition

Re: Extracting Data Based on Yes or No Condition

Re: Extracting Data Based on Yes or No Condition

Re: Extracting Data Based on Yes or No Condition

Re: Extracting Data Based on Yes or No Condition

Re: Extracting Data Based on Yes or No Condition

Re: Extracting Data Based on Yes or No Condition

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions