Return only one in the List

S

shonaart

Board Regular
Joined
Nov 1, 2004
Messages
214
Hi, Please can you help.

Hi, I have names in Column A1 to A9, some names repeated. I need a formula in A14 and down (A19) to look , at A1 to A9 and return only unique names. Only one name of each.
I can not use the menu's Data Filter option in this case. I need a formula to look at that range and return a name once of each person.
Thanks guys much appreciated.

A
1 John
2 peter
3 john
4 vaughan
5 gavin
6 paul
7 vaughan
8 george
9 gavin
10
11
12
13
14 John
15 Peter
16 Vaughan
17 Gavin
18 Paul
19 George
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
B1: =A1

B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""),0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""),0)))

B2 is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.

Copy B2 down as far as is possible.
 
Upvote 0
Unfortunatly, I do not understnad array formulas. I tried to adapt it to my needs and all I am comming up with is blank cells.
 
Upvote 0
Why do you need to adapt it, I provided an answer targetted at the info that you gave?
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Search a range of cells in another tab and return a value
Replies
6
Views
560
Peter_SSs
Peter_SSs
P
  • Solved
validate a character string within a cell to a list and return the value found
2
Replies
10
Views
943
Peter_SSs
Peter_SSs
R
  • Question
INDEX MATCH Non-Blank Cell
Replies
7
Views
352
ExceLoki
ExceLoki
B
  • Solved
macro to print selection when there is data in rows
2 3
Replies
24
Views
1K
bh24524
B
S
  • Question
Sequential Numbering Based on Other Columns
Replies
1
Views
258
Joe4
Joe4

Forum statistics

Threads
1,215,507
Messages
6,125,212
Members
449,214
Latest member
mr_ordinaryboy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top