Is there a formula that can do this?

[gootroots]

This issue has alluded me for a while. Is there a formula that could be used
in "C" that will bring back unique values contained in "A" but only bring the
value back if that value has a row value showing in "B"

A1 = apples B1 = 1 C1 = apples
A2 = grapes B2 = C2 = pears
A3 = pears B3 = 3 C3 =
A4 = oranges B4 = C4 =
A5 = apples B5 = 5 C5 =

A hard one for sure, although I would like to know if there is such a formula.

Thank you for your time.

 

[Gary''s Student]

In C1 enter:

=IF(OR(B1="",COUNTIF($A$1:A1,A1)>1),"",A1) and copy down:

apples 1 apples
grapes
pears 3 pears
oranges
apples 5

There are a variety of ways to eliminate the blanks in column C.

 

[gootroots]

Interesting.

There are a variety of ways to eliminate the blanks in column C.

Which of these ways would best suit as a defined range to be used in a
dropdown list for example

 

[Ashish Mathur]

Hi,

Try this.

1. Assume that your data is in A2:B6 . Please ensure that the headings are
in row 1. Assume they are fruit and number
2. Select A1:B6 and assign a name to it (Ctrl+F3 > New), say dummy5
4. Select A1:B6 and convert it to a table (Ctrl+L)
5. Save the file
6. Click on a blank cell
7. Navigate to:
a. Excel 2007 - Data > Data Tools > From Other Sources > From Microsoft
Query
b. Excel 2003 - Data > Import External Data > New Database Query
8. Select Excel files and click on OK
9. Navigate to the folder where the file is saved- one the left hand side,
you should see the file which you saved in step 5 above
10. Click on Next
11. Select dummy and click on the greater then symbol to get all the columns
to the right
12. Click on Next 3 times
13. Select "Edit Data or view query in MS Query"
14. In View > Query Properties, check the box for Unique records only
15. Click on the SQL button and type the following. make obvious changes
such as file path

SELECT DISTINCT dummy.Fruit
FROM `C:\Users\Ashish\Desktop\try.xlsx`.dummy dummy
WHERE (dummy.Number Is Not Null)

When you click on OK, you will see the desire result

16. Go to File > Return Data to MS Office Excel
17. In the import data box, select the cell where you want the output

This will get you what you want

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com