The Hardest Challenge Ever!...Need help with a formula that extract and sorts unique values from a data set.

[cwfromct]

Hi everyone, I have been searching for a formula that would do the following, extract unique values from a frequency chart..Please excuse me I am new to this form and limited knowledge of excel. (I am not a spreadsheet master..lol)

example of the chart:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]gas[/TD]
[TD]205[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]trv[/TD]
[TD]205[/TD]
[TD]302[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]gas[/TD]
[TD]320[/TD]
[TD]205[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]gas[/TD]
[TD]320[/TD]
[TD]205[/TD]
[TD]450[/TD]
[TD]302[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]gas[/TD]
[TD]220[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This what I want the end result to be.
[TABLE="class: grid, width: 30"]
<tbody>[TR]
[TD]code[/TD]
[TD]qty[/TD]
[TD][/TD]
[TD]code[/TD]
[TD]qty[/TD]
[/TR]
[TR]
[TD]gas[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]450[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]205[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]220[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]320[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]trv[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]302[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I know I can use the count formula in qty column, my problem is finding a formula that would extract the unique values from the data set in code column..I know this is complex because I have been searching everywhere for it. I can find one that would go in rows or columns but not in both direction..keep in mind I am using excel 2007 has to work dynamically and be compatible with other spreadsheets such as google spreadsheets if possible...I personally thank you in advance for your help,
Chris.

 

[shawnhet]

If I understand you correctly, your easiest path might be just to copy all of the individual columns in one column one on top of the other and then use Advanced Filter to remove dupes (ie if you have 1000 rows, column A will go from 1-1000,B from 1001-2000) etc...

Cheers,

 

[cwfromct]

That would the easiest if would it would work..I tried that and I get The extract range has missing or a illegal field name..I do not think that would work anyways many people would using the spreadsheet..A dynamic formula would work..

I have tried this Array formula =IF(ROWS(A$8:A8)>I$25,"",INDEX(A$1:A$5,SMALL(IF(FREQUENCY(IF(A$1:A$5<>"",MATCH(A$1:A$5,A$1:A$5,0)),ROW(A$1:A$5)-ROW(A$1)+1),ROW(A$1:A$5)-ROW(A$1)+1),ROWS($A$8:A8)))) somewhat does what I want..but it only does the one column...I need it to do all columns.

The I$25 is a cell that counts the unique values. the cell I$25 has formula =SUMPRODUCT(--(A1:e5<>""),1/COUNTIF(A1:e5,A1:e5&"")) in it.

 

[shawnhet]

I'm not sure how you are getting the error message(I can't imagine getting this error without typing something in wrong). I suggest if you need a dynamically updating list that you record a macro that will copy and paste the relevant columnar data one on top of the other, remove the duplicates using Advanced Filter then pasting it to the location where you want the unique list.

Cheers,

 

[cwfromct]

would that update automatically when additional data is add or taken off?

 

[Drrellik]

Getting unique values in Excel by using formulas only - Stack Overflow please take a look at solution 5 where you can add a user function to create a unique list for each column. I don't know how many columns you have or how often it will change. but this might get you closer to what you are trying to do. Don

 

[Drrellik]

two other links you might want to look at. Excel Magic Trick 473: Extract Unique Records with Formula (Complex Array Formula) - YouTube and Excel Magic Trick 1053: Unique Count With Criteria: Excel 2013 PivotTable Distinct Count Function - YouTube I located these two videos for you I hope they help.

 

[cwfromct]

that video is really good!emt 473. How do expand the formula to work in both directions(rows and columns) or maybe go through each columns then go through the rows?A pivot table is nice.but that wont work only because that has to be refreshed ever so often.

could this be expanded to work in both direction?
=IF(ROWS(A$8:A8)>I$25,"",INDEX(A$1:A$5,SMALL(IF(FREQUENCY(IF(A$1:A$5<>"",MATCH(A$1:A$5,A$1:A$5,0)),ROW(A$1:A$5)-ROW(A$1)+1),ROW(A$1:A$5)-ROW(A$1)+1),ROWS($A$8:A8)))) somewhat does what I want..but it only does the rows in one column...I need it to do all columns.

I will check out stack over flow...thanks