Unique records dynamic formula

V

valenta79

Board Regular
Joined
Dec 31, 2009
Messages
75
Hi there,

I have seen several formulas and variations on how to create a unique list of records but I am having issues with it.
Can someone please post the easiest and shortest way to do this?
Basically I have a list of cities and sales numbers. Since every week cities are changing I need to create a dynamic formula that would show unique records in my summary tab.
What I need is a formula that would look at the data tab and find unique cities. So in summary tab column A I need to list all unique cities that appear numerous times in data tab column B. Can someone provide a formula for that??
Thanks a million and Happy New Year!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Hi & Welcome to the Board!

Have you considered using a Pivot Table for this? That would be my choice. Formula-wise, here's one possibility:
Excel Workbook
ABC
1Unique CitiesCity
2LondonLondon
3MadridMadrid
4New YorkLondon
5NewcastleNew York
6ParisNewcastle
7BerlinParis
8CopenhagenParis
9BeijingParis
10TokyoMadrid
11RomeBerlin
12MoscowCopenhagen
13StockholmBeijing
14OsloTokyo
15AthensRome
16JerusalemMoscow
17RiyadhMadrid
18WashingtonLondon
19#N/ACopenhagen
20#N/AStockholm
21#N/AOslo
22#N/AAthens
23#N/AJerusalem
24#N/ARiyadh
25#N/AOslo
26Rome
27Moscow
28New York
29Washington
30New York
Summary
Excel 2003
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
 
Upvote 0
Thanks for a quick response Richard!

Pivot table does not work for me in this case but the formula you have provided worked great!
The only thing to perferct it would be to get rid of N/A error message. I tried to incorporate iferror into this formula but could not make it happen.
Let me know if that is easily done.

Thanks so much. I am glad to join thiss forum since I am working as an analyst and I am sure i will have quite a few questions.
 
Upvote 0
do you know of a way to keep it so that the unique list will stay listed alphabetically?
 
Upvote 0
C.S. said:
do you know of a way to keep it so that the unique list will stay listed alphabetically?
Click to expand...


<TABLE style="WIDTH: 168pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=224><COLGROUP><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 66pt; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #a6aab6 1pt solid" class=xl65 height=20 width=88>London</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=72>London</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 66pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: #a6aab6 1pt solid" class=xl65 height=20 width=88>Madrid</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Madrid</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 66pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: #a6aab6 1pt solid" class=xl65 height=20 width=88>Paris</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>New York</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 66pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: #a6aab6 1pt solid" class=xl65 height=20 width=88>New York</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Newcastle</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 66pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: #a6aab6 1pt solid" class=xl65 height=20 width=88>Newcastle</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Paris</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 66pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: #a6aab6 1pt solid" class=xl65 height=20 width=88>Paris</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 66pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: #a6aab6 1pt solid" class=xl65 height=20 width=88>London</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 66pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: #a6aab6 1pt solid" class=xl65 height=20 width=88>Paris</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 66pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: #a6aab6 1pt solid" class=xl65 height=20 width=88>Madrid</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD></TR></TBODY></TABLE>

Assuming that A2:A10 contians the data, try...

C2, confirmed with CONTROL+SHIFT+ENTER:

=INDEX($A$2:$A$10,MATCH(0,COUNTIF($A$2:$A$10,"<"&$A$2:$A$10),0))

D2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(COUNTIF($A$2:$A$10,">"&C2),INDEX($A$2:$A$10,MATCH(COUNTIF($A$2:$A$10,"<="&C2),COUNTIF($A$2:$A$10,"<"&$A$2:$A$10),0)),"")
 
Upvote 0
Assuming that A2:A10 contians the data, try...

C2, confirmed with CONTROL+SHIFT+ENTER:

=INDEX($A$2:$A$10,MATCH(0,COUNTIF($A$2:$A$10,"<"&$A$2:$A$10),0))

D2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(COUNTIF($A$2:$A$10,">"&C2),INDEX($A$2:$A$10,MATCH(COUNTIF($A$2:$A$10,"<="&C2),COUNTIF($A$2:$A$10,"<"&$A$2:$A$10),0)),"")
-----------------------------------------------------------
when i enter the formula in C2 it returns London, but then when I copy the formula down it just keeps giving me London. Then when I enter the other formula in cell D2 and run it down it returns all Madrid's. Am I missing something?
 
Last edited:
Upvote 0
C.S. said:
Assuming that A2:A10 contians the data, try...

C2, confirmed with CONTROL+SHIFT+ENTER:

=INDEX($A$2:$A$10,MATCH(0,COUNTIF($A$2:$A$10,"<"&$A$2:$A$10),0))

D2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(COUNTIF($A$2:$A$10,">"&C2),INDEX($A$2:$A$10,MATCH(COUNTIF($A$2:$A$10,"<="&C2),COUNTIF($A$2:$A$10,"<"&$A$2:$A$10),0)),"")
-----------------------------------------------------------
when i enter the formula in C2 it returns London, but then when I copy the formula down it just keeps giving me London. Then when I enter the other formula in cell D2 and run it down it returns all Madrid's. Am I missing something?
Click to expand...

Sorry, my mistake. The first formula is entered in C2 only, and confirmed with CONTROL+SHIFT+ENTER. The second formula is entered in C3, confirmed with CONTROL+SHIFT+ENTER, and then copied down.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Dynamic Cross Tab Report - Unique items w/ criteria
Replies
2
Views
327
Nobes
N
D
  • Solved
Automation of Tabs
2
Replies
10
Views
597
kevin9999
K
S
  • Solved
Substitute for UNIQUE Dynamic Array to function properly in Excel 2019
Replies
6
Views
1K
kevin9999
K
C
  • Question
Unique list of records that have duplicate records
Replies
1
Views
291
CA Vijay Gupta
C
C
  • Solved
UNIQUE(FILTER(INDIRECT formula
Replies
5
Views
949
KRice
K

Forum statistics

Threads
1,215,530
Messages
6,125,347
Members
449,220
Latest member
Edwin_SVRZ

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