help with extracting unique data

[lockdalf]

Hey guys,


I have a need for a formula, that would be fast and that would automatically extract 16 sorted unique text values from a table containing about 13k records. I have a table that contains a column(field) with week number in yyyy/ww format (e.g.: 2012/48; 2013/01,...). The file contains all the data about activity (sales, production, stock,...) plan for following 15 weeks + info about current week extracted from SAP, so formats are a bit of an issue.I need to create a short list of weeks in order.


I know about advanced filters and I have tried pivot and data connections. All of them are clumsy and not automatic. Plus when I update the source, it still keeps the values that are no more present in the data. I plan to hide the tab where this list should be stored and just refer to named range (lstWeeks) where needed.


I have tried to use =SMALL(range;#) but I get a #NUM! error (since the data is stored as text). I have also tried DMIN() but there is definitely something I am not getting about the criteria parameter, since it always returns #VALUE!. I would prefer to avoid array formulas solely due to large data...


I am getting desperate with this, since I am trying to create a file that will generate a report of activity from the entered data without having to do any more manual operations on the file, to prevent a lot of possible error.


thanks for any help

 

[Faseeh]

Hi lockdalf,


What is the criteria for getting the 16 values, can you upload a sample data (just few hundred rows)


Regards,

 

[lockdalf]

Hi Faseeh, as I mentioned it my range is tblNewExtract[Week] as as of this week it contains values 2012/49; 2012/50, 2012/51... 2013/12


I do not have any specific criteria, as this column contains only these 16 values and I need to extract them sorted and cleared of duplicates.


My table contains about 15 columns of information about activity based on a product, and since I have the data for 16 weeks, I have 16 lines for the same product. eg.


product prod. group week info1 info2...

product1 grp1 2012/49 xyz xyz...

product1 grp1 2012/50 xys xys...

.

.


since I have about 800 different products my table has over 13k lines with column [week] repeating the same 16 values...


what I need is a named formula (or series of 16 formulas for a cell range copied down) lstWeeks= 'unique list from tblNewExtract[Week] sorted chronologicaly'


a little extra info, the list is not sorted in any special way.


i have tried the array entered index(xyz(match(0;..... leing aroud web, but it almost killed my excel and did not return correct results...

 

[Faseeh]

You want a formula that takes a product and reproduces the result of 16 weeks down the rows??

 

[lockdalf]

no, just take the column [Week] and extract the 16 unique values from it... no special criteria or other conditions. Just that the list is sorted ascending...

 

[Faseeh]

Hi lockdalf,


OK understood partly, can you upload a sample file with an example? That will be of great help.


Regards,

 

[lockdalf]

see the attached link this is just the data sample... the data usialy is in no specific order...


https://docs.google.com/open?id=0BwckfCm1BHwnQlVFWklzTVdIWXM

 

[Faseeh]

Can you explain taking up an example how do you want to proceed?


Thanks.