Is there a formula that can do this?



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
pears 3 pears
apples 5

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


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


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
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

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


Ashish Mathur
Microsoft Excel MVP

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question