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.

Is there a formula that can do this?
 

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.
--
Gary''s Student - gsnu200909


"gootroots" wrote:

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.

Is there a formula that can do this?
 

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



"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200909


"gootroots" wrote:

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.

Is there a formula that can do this?
 

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

"gootroots" wrote in message
...
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.