Extracting unique values and sorting to new column

D

dmjendor

New Member
Joined
Feb 17, 2010
Messages
2
Ok, I am sure I am doing something wrong that is causing my problem.

First off, I am using Excel 2010 Beta on a Windows 7 (64bit) machine.

The problem that I am having is I found a formula that looks like it will do exactly what Im looking for, extract information from one column, sort it alphabetically and remove duplicates and put it in a new column.

=INDEX(Week1,MATCH(0,COUNTIF($AI$2:AI2,Week1),0))

This is supposed to be added to the sheet using CTRL-SHIFT-Enter which from what I've read is putting it in as an array formula.

I Enter the formula in the first row then propagate it down to the bottom of the column, however the problem that I'm having is that the information showing up all the way down the column is the information in the first row.

I think part of the problem may be that my data has a lot of blanks that which is part of the reason that I wanted to sort it into a new column.

Basically, Im building a multifunction spread sheet, one function is to generate a shopping list. So i have a table with about 35 columns, 29 of which are ingredients.

So I have one column off to the side using formula's like this
=IF(VLOOKUP(AE2,Table1,6,FALSE)<>0,VLOOKUP(AE2,Table1,6,FALSE),"")
one for each ingredient, so a total of 29 of them for each recipe, then its stacked 7 tall, so a total of about 160 rows.

Now not each recipe has 29 ingredients so there are a lot of blanks, I was having other errors with blank cells so i filled all the blank cells with a single space.

Any help will be greatly appreciated.

As a side note, I have the sheet set to manual calculation and when refreshing the data, the information in the sorted column doesnt update.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
=INDEX(Week1,MATCH(0,COUNTIF($AI$2:AI2,Week1),0))
Click to expand...

The above formula cannot be used to return a unique list, and to sort in alphabetical order. There`s a formula (actually, a combination of 2 formulas) that can be used, which is somewhat of a variation on the above formula. However, it`s not robust. It can only be used on single type data. In other words, the data can only contain either text values or numerical values, but not both. And, it does not allow formula blanks.

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

C2, confirmed with CONTROL+SHIFT+ENTER:

=SUM(IF(FREQUENCY(IF(A2:A10<>"",MATCH("~"&A2:A10,A2:A10&"",0)),ROW(A2:A10)-ROW(A2)+1),1))

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

=IF(ROWS(D$2:D2)<=$C$2,INDEX($A$2:$A$10,MATCH(SMALL(IF($A$2:$A$10<>"",IF(ISNA(MATCH($A$2:$A$10,$D$1:D1,0)),MMULT((IF($A$2:$A$10<>"",$A$2:$A$10)>TRANSPOSE(IF($A$2:$A$10<>"",$A$2:$A$10)))+0,ROW($A$2:$A$10)^0))),1),MMULT((IF($A$2:$A$10<>"",$A$2:$A$10)>TRANSPOSE(IF($A$2:$A$10<>"",$A$2:$A$10)))+0,ROW($A$2:$A$10)^0),0)),"")

Adjust the ranges, accordingly. Note that if you`re using a version of Excel prior to 2007, you will likely exceed the number of functions that can be nested. If this is the case, you`ll need to use a defined name to refer to part of the formula. For example, define a name, let`s say Array, as follows...

Refers to:

=MMULT((IF($A$2:$A$10<>"",$A$2:$A$10)>TRANSPOSE(IF($A$2:$A$10<>"",$A$2:$A$10)))+0,ROW($A$2:$A$10)^0)

Then, replace both instances of this part of the formula with the name Array...

=IF(ROWS(D$2:D2)<=$C$2,INDEX($A$2:$A$10,MATCH(SMALL(IF($A$2:$A$10<>"",IF(ISNA(MATCH($A$2:$A$10,$D$1:D1,0)),Array)),1),Array,0)),"")
 
Last edited:
Upvote 0
Hrm.. I think I understand what you were saying and your right, the formula I posted earlier wouldnt sort, just extract the data to a new column removing the duplicates.

I entered the formula you gave me to my sheet, confirming it, but the cells turned out blank. When I tried to evaluate it, it looked like it was working, but at the end it shifted from values to blanks.

I assume this is something to do with you saying that it wouldn't do Formula Blanks, which If im understanding you correctly if the contents of the cell after a formula is calculated would be blank it wouldnt work.

I appreciate the help, is there any way to get it working to allow for blanks? Worst case I suppose I can replace the blank cells with something else but that just seem sloppy.

If you need more information from me let me know, i'll provide as much as I can. This is just a personal project/learning experience.
 
Upvote 0
Let's assume that A2:A10 contains the following data...

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64>Z</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>8</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>D</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>6</TD></TR></TBODY></TABLE>

As you can see, the data contains text and numerical values. Also, A4 and A7 contain formula blanks (""), and A9 is empty. First, enter the following formula in C2, and confirm with CONTROL+SHIFT+ENTER, not just ENTER...

=SUM(IF(FREQUENCY(IF(A2:A10<>"",MATCH("~"&A2:A10,A2:A10&"",0)),ROW(A2:A10)-ROW(A2)+1),1))

If done correctly, Excel will automatically place braces {...} around the formula. Then, enter the following formula in D2, confirm with CONTROL+SHIFT+ENTER, and copy down:

=IF(ROWS(D$2:D2)<=$C$2,INDEX($A$2:$A$10,MATCH(SMALL(IF($A$2:$A$10<>"",IF(ISNA(MATCH($A$2:$A$10,$D$1:D1,0)),MMULT((IF($A$2:$A$10<>"",$A$2:$A$10)>TRANSPOSE(IF($A$2:$A$10<>"",$A$2:$A$10)))+0,ROW($A$2:$A$10)^0))),1),MMULT((IF($A$2:$A$10<>"",$A$2:$A$10)>TRANSPOSE(IF($A$2:$A$10<>"",$A$2:$A$10)))+0,ROW($A$2:$A$10)^0),0)),"")

The result should be as follows...

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>6</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>8</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>D</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Z</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR></TBODY></TABLE>
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Static Timestamp
Replies
4
Views
614
Fluff
Fluff
M
  • Solved
Sort, reference and copy indeterminate cells
Replies
2
Views
127
MissT454
M
B
  • Question
Extracting variable amounts of characters from highly variable strings into two columns
Replies
7
Views
183
Peter_SSs
Peter_SSs
B
  • Solved
Macro to Open new tab and duplicate the data from the template, and rename new tab from cell value of template.
Replies
3
Views
503
mumps
mumps
T
  • Solved
Can I sort a table by applying columns in a different order to the sort request?
Replies
5
Views
108
TheRedCardinal
T

Forum statistics

Threads
1,221,904
Messages
6,162,743
Members
451,785
Latest member
DanielCorn

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