unique list from list of duplicates

darmis

New Member
Joined
Nov 25, 2004
Messages
2
I have a worksheet containing incoming and outgoing payments, over a reasonable time period. Each item contains a date, a store name, and the amount.
I want to create a summary worksheet, where first column is "store name", for example. At present I create the list of stores manually and do the conditional lookups, sums, averages, etc.. based on this first column. However, I can never be sure if I've missed anything, and the list of stores changes from time to time.

How do I get a list of unique store names from this list of duplicated store names? In short, how do I create the first column?

Thanks!
 

ginodatreviso

Board Regular
Joined
Nov 23, 2004
Messages
62
One solution might be creating a Pivot table from your data, put stores in rows and something else in the center of the table. In the first column of the pivot table you have the list you are looking for.
Ciao
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Welcome!

Also you could use advanced filter to filter for unique records only

or, if list is not too big, you could use a formula:
Book1
ABCD
1
2a
3s
4a
5a
6s
7d
8ff
9g
10h
11j
12k
Sheet7
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,288
As gino suggested this sounds like a good candidate for a pivot table with the store name as a row field and the averages/sums as data fields.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,125
darmis said:
I have a worksheet containing incoming and outgoing payments, over a reasonable time period. Each item contains a date, a store name, and the amount.
I want to create a summary worksheet, where first column is "store name", for example. At present I create the list of stores manually and do the conditional lookups, sums, averages, etc.. based on this first column. However, I can never be sure if I've missed anything, and the list of stores changes from time to time.

How do I get a list of unique store names from this list of duplicated store names? In short, how do I create the first column?

Thanks!
If you stay working with formulas, the formula system for creating a unique list of stores would be efficient enough with a large range of stores...
Book1
ABCDEFGH
10
2StoreQtyAmtCountStoreListTotalQtyTotalAmt
3StoreA2162
4StoreB932
5StoreA10100
6StoreC1366
7StoreB2126
8StoreA1340
9StoreD1782
10
Sheet1


Formulas for the distinct/unique list of stores...

D1 must house a 0.

D3, copied down:

=IF((A3<>"")*ISNA(MATCH(A3,$A$2:A2,0)),LOOKUP(9.99999999999999E+307,$D$1:D2)+1,"")

F1:

=LOOKUP(9.99999999999999E+307,D3:D9)

which is also the number of distinct stores.

F3, copied down:

=IF(ROW()-ROW(F$3)+1<=$F$1,INDEX($A$3:$A$9,MATCH(ROW()-ROW(F$3)+1,$D$3:$D$9)),"")

If you're on Excel 2003, all this can be done with Lists, lifting up the necessity of copying down the formulas with every new record to the source data.
 

darmis

New Member
Joined
Nov 25, 2004
Messages
2
I've tried the suggestions from Aladin Akyurek, splitting the formulae over two sheets - and have a unique list of stores. I have added just one column to the original sheet, so I can add the 'id' against which the references are made. Now I just need to do the statistical stuff.

Excellent - thanks!
 

drewdy77

New Member
Joined
Jun 8, 2011
Messages
18
I know this is an old post, but for some reason, I am unable to extract the formulas from the example that Aladin placed here two posts ago. Can anyone click on these?
I would like to see the contents of cells G3 and H3, if anyone is willing to assist. Fortunately, the other formulas were posted in the body of the post - and worked perfectly for what I am attempting to do. I'm mostly sure that these other two columns will be just the thing, too.

Thank you in advance!

Chris
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
I know this is an old post, but for some reason, I am unable to extract the formulas from the example that Aladin placed here two posts ago. Can anyone click on these?
I would like to see the contents of cells G3 and H3, if anyone is willing to assist. Fortunately, the other formulas were posted in the body of the post - and worked perfectly for what I am attempting to do. I'm mostly sure that these other two columns will be just the thing, too.

Thank you in advance!

Chris
I can't get that thing to work but it looks like the formulas in columns G and H are SUMIFs.

G3 and copied down:

=SUMIF(A$3:A$9,F3,B$3:B$9)

H3 and copied down:

=SUMIF(A$3:A$9,F3,C$3:C$9)
 

drewdy77

New Member
Joined
Jun 8, 2011
Messages
18
That worked like a charm. I thank you again.

lol I especially like you referring to it as "that thing". Is it typically problematic?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,125
I know this is an old post, but for some reason, I am unable to extract the formulas from the example that Aladin placed here two posts ago. Can anyone click on these?
I would like to see the contents of cells G3 and H3, if anyone is willing to assist. Fortunately, the other formulas were posted in the body of the post - and worked perfectly for what I am attempting to do. I'm mostly sure that these other two columns will be just the thing, too.

Thank you in advance!

Chris
G3, just enter and copy down:

=SUMIF($A$3:$A$9,F2,$B$3:$B$9)

H3, just enter and copy down:

=SUMIF($A$3:$A$9,F2,$C$3:$C$9)
 

Forum statistics

Threads
1,078,520
Messages
5,340,931
Members
399,399
Latest member
SravanaSandhya

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top