How to create a summary list?

[tkroper]

Hi!

I have a list of unsorted codes in A1:A30. There are a total of only 5 unique codes represented, but most of the codes occur several times.

I'd like to produce a summary list in A35:A40 of each of the 5 codes represented in the range above.

Is there a formula that can be copied and pasted into A35:A40 that will search A1:A30 and return one of each code, or can this only be done using a macro?

Thank you for your help.

 

[Yogi Anand]

Hi Todd:

Following is a solution using UniqueVales function from MoreFunc Addin available for FREE ...

grid.xls
	A	B	C	D
1	UnsortedCode		UniqueValues
2	abc		jklm
3	12345		abc
4	A_4		A_4
5	abc		12345
6	abc		6789
7	abc
8	abc
9	abc
10	jklm
11	6789
12	12345
13	jklm
Sheet3

array formula in clls C2:C13 is ... =UNIQUEVALUES(A2:A13)

Would this work for you?

 

[tkroper]

Wow, Yogi...that looks like that does the trick. So is this some sort of an add-in or something?

 

[PaddyD]

"Is there a formula that can be copied and pasted into A35:A40 that will search A1:A30 and return one of each code, or can this only be done using a macro?"

Data | filter |advanced filter - unique values & copy to another location would do it, as would a formula of the form:

=INDEX($A$1:$A$30,SMALL(IF(FREQUENCY(IF($A$1:$A$30<>"",MATCH("~"&$A$1:$A$30,$A$1:$A$30&"",0)),ROW($A$1:$A$30)-ROW($A$1)+1),ROW($A$1:$A$30)-ROW($A$1)+1),ROWS($A$1:A1)))

...ented with control + shift + enter

 

[tkroper]

"Is there a formula that can be copied and pasted into A35:A40 that will search A1:A30 and return one of each code, or can this only be done using a macro?"

Data | filter |advanced filter - unique values & copy to another location would do it, as would a formula of the form:

=INDEX($A$1:$A$30,SMALL(IF(FREQUENCY(IF($A$1:$A$30<>"",MATCH("~"&$A$1:$A$30,$A$1:$A$30&"",0)),ROW($A$1:$A$30)-ROW($A$1)+1),ROW($A$1:$A$30)-ROW($A$1)+1),ROWS($A$1:A1)))

...ented with control + shift + enter

I've never used Data>Filter before and made a hasty attempt and didn't get it so I thought maybe it didn't even do what I was looking for and gave up on it. I'll read-up on that and try again now that I know I was heading in the right direction after all.

As for your formula, it works perfectly! I appreciate you for helping me solve another one PaddyD!

 

[PaddyD]

"...it works perfectly"

glad to hear it. your next job is to try & work out how

 

[tkroper]

True!

Thanks again PaddyD!

 

[Guanjin Peter]

if you prefer a shorter method (without using vba formulas), let me know. One of Jindon's code cover this.

 

[tkroper]

Sure, I'd be interested to see your solution. Thank you Peter!

 

[Guanjin Peter]

check out this post:
http://www.mrexcel.com/forum/showthread.php?t=326174