Searching for multiple values in a range
Posted by Alan G on April 24, 2001 7:24 AM
I am looking for a way to find multiple entries of the same value in an array. For example, if I wanted to create a shoping list of items, I might use the the following data.
Column A Column B
Row 1 Apples yes
Row 2 Banannas no
Row 3 Peaches no
Row 4 Cherrys yes
Row 5 oranges yes
Row 6 grapefruit no
Row 7 strawberrys yes
In a separate sheet, I would like to compile the information as follows:
Column A
Row 1 Apples
Row 2 Cherrys
Row 3 oranges
Row 4 strawberrys
I can use MATCH to find 'yes' in column B, and use INDEX to record the item from Column A. Unfortunately, I can't use INDEX to search for the second location of 'yes', nor do I know how to change my range from b1:b7 to b2:b7 in order to grab the first 'yes' of a new location.
Is there a way to do this without sorting on column b or writing a macro?
Posted by Big Bob on April 24, 2001 7:56 AM
:::::::::::Have you considered using AUTOFILTER.Create titles for your two columns.
Click a single cell inside your shopping list then click on DATA move down to FILTER and then across to AUTOFILTER .There should now be a drop down arrow by each column heading .Click the one in column B and you should get a list with yes and no as the only two items.Click on yes and the list will be filtered to show only those rows where yes appears in column B.To copy these to another worksheet you need to use PASTE SPECIAL VISIBLE CELLS ONLY
HTH
Posted by IML on April 24, 2001 8:27 AM
This looked interesting. This probably isn't a good way, but it was fun trying:
In my crazy way, you need some hidden columns. Next to your yes-nos, put the following in column C
=IF(B2="yes",1+(MAX($C$1:C1)),0) and copy it down.
also count the total number of yes's. I did it in F1 on sheet one:
=COUNTIF(B1..B7,"=yes")
On your second sheet here is the formula for cell A1
=IF(ROW(A1)<=Sheet1!$F$1,INDEX(Sheet1!A1:C7,MATCH(LARGE(Sheet1!C1:C7,Sheet1!$F$1),Sheet1!C1:C7,0),1),"")
The second formula to copy to put in A2 and copy down is
=IF(ROW(A2)<=Sheet1!$F$1,INDEX(Sheet1!A2:C8,MATCH(LARGE(Sheet1!C2:C8,Sheet1!$F$1-ROW(A1)),Sheet1!C2:C8,0),1),"")
good luck and check back for a sane way to do this!
Posted by IML on April 24, 2001 9:13 AM
Oops
Sorry, the first formula I gave you is fine for cell C2 and to copy down. In cell c1, you need to put
=IF(B1="yes",1,0)
Posted by Alan G on April 24, 2001 1:34 PM
Re: Oops
Works Great!!! Thanks Sorry, the first formula I gave you is fine for cell C2 and to copy down. In cell c1, you need to put =IF(B1="yes",1,0)
