unique list from list of duplicates

[darmis]

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]

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]

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
	A	B	C	D
1
2	a		a
3	s		s
4	a		d
5	a		ff
6	s		g
7	d		h
8	ff		j
9	g		k
10	h
11	j
12	k
Sheet7

 

[Norie]

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]

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
	A	B	C	D	E	F	G	H
1				0		4
2	Store	Qty	Amt	Count		StoreList	TotalQty	TotalAmt
3	StoreA	21	62	1		StoreA	44	202
4	StoreB	9	32	2		StoreB	30	58
5	StoreA	10	100			StoreC	13	66
6	StoreC	13	66	3		StoreD	17	82
7	StoreB	21	26
8	StoreA	13	40
9	StoreD	17	82	4
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]

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]

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]

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]

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]

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)