Excel: Formula to list unique values in a range

Rollo Tamasi

Hi guys,

I hope one of you gents might be able to help with this small matter.

I need to list the unique entries from a column on sheet 1 from sheet 2. The screenshot below shows the ideal output:


If Australia appears in the Origin Country column it will output Australia in Sheet 1, etc... I will then count the number of occurrences of Australia and output that under sales.

Easy, right?

Victor

I don't know how to do it with a formula, but you could just do it by sorting the data by sales - highlight all the data then Data > Sort > select the Sales column > OK.

You might be able to copy it to the front page with

Cell Sheet1:A2 =if(Sheet2:Sales2>1,"",Sheet2:A2)

and copying that for what ever width is required. This is not how I would do it though - be very careful if you re-sort the data.

Northern Monkey

Would a "countif" formula do the job?

edit: or a pivot table?

Rollo Tamasi

Northern Monkey wrote: »

Would a "countif" formula do the job?

edit: or a pivot table?

I was trying to use a countif but I couldn't get the formula working. Any idea?

@Victor - Unfortunately, I have to use a formula rather than a feature in Excel so I can't use the advanced filter option.

amen

No programming required just knowledge of Excel
Steps are
1: Sort the columns by Origin Country (alphabetically)
2: Click on the data tab
3: Click on SubTotal
4: On the subtotal dialog
Under the "At Each change in" drop down select Origin country
Under the "Use function" drop down select SUM (depending on data what you are doing with your data Count may also be appropriate)
Under the "Add Subtotal to" check box select Sales
Click Ok
5: You should now have the subtotal of sales for each Origin Country and a toal for all Counties
6: There should be three new buttons 1,2,3 to the left of cell A1. These expand/collapse the level of detail.

There are loads of useful functions under the data column.
If you really wanted to you could the above steps into a Macro.

Have fun!

kincsem

With this countif in cells A1, A2, A3 on sheet 1, "australia" in B1, "spain" in B2, ireland in B3. ....... =COUNTIF(Sheet2!$A$1:$A$25,$B1)

Sheet 2 has the list of countries in column A.

Just put that countif formula in A1 and put the cursor on the bottom left of the A1 cell and drag the formula down into A2 and A3 and so on.

Rolo Tomasi was a good horse a few years back.

Edit: I see you have the countries in A1, A2, A3 on sheet 1. Just put the formula in B1 =COUNTIF(Sheet2!$A$1:$A$25,$A1)[/B]

shootermacg

This is what the VLOOKUP function in excel was made for, just modify it to add one to the cell in question rather than outputting a static value.

Procasinator

Would it not just be:

=SUMIF(Sheet2!A:A, Sheet1!A1, Sheet2!:B:B)

Where argument 1 is Country range, argument 2 is the current country (this changes per row) and argument 3 is the Sales range.