Dynamic list of unique cells in a column

English
#1

I’ve succeeded in extracting the unique cells in a column, but the result is static. To accomplish that, I selected a range of cells, then Data > More Filters > Standard Filter…, set the criteria to be Not Empty, set other criteria for other columns, set checkmarks in No duplications, and in Copy results to, lastly clicked Ok.

The cells are filled with static content. I’d like this to be dynamic. The list would be in it’s own sheet, so I’m not concerned with overwriting any cells should the list lenghten.

#2

Just a tip:
You can use a Macro and an Event to launch the filter again and again, when the source range will be changed.

2 Likes
#3

Would the UNIQUE function be of use in this case? UNIQUE function

#4

UNIQUE(source) fails when a new value is inserted into the source list. Calc can not expand array formulas.

Since people hate to develop database applications, they have to struggle with StarBasic.

Sub SheetActivate()
doc = ThisComponent
sh = doc.getActiveSheet()
x = sh.RangeAddress.Sheet
for each dbr in doc.DatabaseRanges
    n = dbr.DataArea.Sheet
    if n = x then dbr.refresh()
next
for each pv in sh.PivotTables
    pv.refresh()
next
End Sub
#5

Before you apply the filter, you have to define a database range for the source data. Make sure “Insert and delete cells” is checked in the “Options” in the “Define Database Range” dialog. When you later change the content of the source data, set the cursor in the source data and use menu Data > Refresh Range and the result of your filter will update. However, if the result shrinks, the then no longer used cells are not automatically erased.

When you want to add new data to the source data, then do not simply write them in empty cells below the source data. Click on the row head of the last cell of the source data base range and then use “Insert row below” even if the cells below are already empty. Only then the database range is automatically extended.

#6

It’s worse than that. The crucial option to make this work is the global option
Tools>Options>Calc>General>"Expand references when new rows or columns are inserted". As a matter of course, this option is off by default, although it should be on (almost always).
If this option is off, no insertion directly below the referenced data will expand any reference, no matter if you insert rows below the last one or if you insert rows above the last+1.

#7

Ola @clear, tenho esta macro, faça adaptação para sua área ( fixando os valores x e y ) e a ligue a Eventos de planilha / Ao ativar o documento.

Hi @clear, I have this macro, adapt it to your area (fixing the x and y values) and link it to Sheet Events / On Document Activation.

Sub ToExtractUniqueInPutBox 
'fonte: http://www.clubedohardware.com.br/profile/697345-tiank/
Dim mCamposFiltro(0) As New com.sun.star.sheet.TableFilterField
Dim x as String
Dim y as String
Dim xi() as Variant
Dim yi() as Variant
x = inputbox("Extraction area: (Ex: Planilha1.D2:C24)")
y = inputbox("Initial cell for discharge: (Ex: Planilha2.C7)")
msgbox "Extraction area: "& X &" , Initial cell for discharge: "& y
xi = Split( x,"." )
yi = Split( y,"." )
oPlan = ThisComponent.Sheets.getByName( xi(0) )
oIntervalo = oPlan.getCellRangeByName( xi(1) )
'=> esta parte permanece idêntica <=
'Descritor do filtro
oDescFiltro = oIntervalo.createFilterDescriptor( True )
'Definir os campos
mCamposFiltro(0).Field = 0
mCamposFiltro(0).Operator = 1
'Estabelecer o destino
oPlan = ThisComponent.Sheets.getByName( yi(0) )
oDestino = oPlan.getCellRangeByName( yi(1) ).getCellAddress()
'=> Restante permanece igual <=
'Propriedades do filtro padrão
oDescFiltro.ContainsHeader = False
oDescFiltro.SkipDuplicates = True
oDescFiltro.CopyOutputData = True
oDescFiltro.OutputPosition = oDestino
oDescFiltro.FilterFields = mCamposFiltro
oIntervalo.Filter( oDescFiltro )
End Sub
#8

Indeed. There are some bug reports, e.g. bug 145751, 128846, 127738, all closed with ‘worksforme’.

#9

Today’s maintainers do not understand why StarOffice developers distinguished between named ranges (named expressions actually) and database ranges, what a clear majority of users tries to accomplish with the product (substitute databases), how an ever-growing set of options (with wrong defaults) becomes counterproductive.

#10

There is a package of routines which I started about 6 years ago, and completed since now and then. Its central idea is to create named variables by formulas, mainly for calculated arrays, and avoiding in this case the need of locked ranges.

If you are ionterested and find the time to study the example, I would be glad to get your comments.
bsm_example_disask_131249_related.ods (42.7 KB)
For scaling (resizing the source) there is used the function CONTIGUOUSPILEADDRESS().
Unfortunately I uploaded an immature version. See (hopefully better now):
bsm_example_disask_131249_related_B.ods (44.5 KB)

#11

A pivot table with one row field lists unique items.

  • Turn on “Expand references” in Calc’s general options (and keep it on forever).
  • Insert new records anywhere within your lists or directly below.
  • Refresh pivot table(s).
  • The attached sample refreshes the pivot when activating its containing sheet.

unique_Names_AutoRefresh.ods (17.6 KB)

#12

The UNIQUE() function allows by its third parameter to choose the correct meaning of unique or the misuse of the word when actually distinct is meant. (TRUE for unique, FALSE for distinct .

#13

This is an important distinction, indeed. I referred to what the OP was posting. He described a filter dumping DISTINCT values on a separate sheet and asked for some way to make it dynamic.
This can be accomplished with a database range and the following code assigned to sheet event “Deaktivate”.

Sub SheetDeactivate()
iSheet = ThisComponent.CurrentController.ActiveSheet.RangeAddress.Sheet
for each dbr in ThisComponent.DatabaseRanges
   if dbr.DataArea.Sheet = iSheet then dbr.refresh()
next
End Sub

Whenever I try a slightly “advanced” feature, I stumble upon regressions. This feature has been around since OOo1. Today I found it broken.
https://bugs.documentfoundation.org/show_bug.cgi?id=170481

#15

uniquifaction:

#16

Finally, this works:
AdvFilter_AutoRefresh.ods (13.7 KB)

First sheet has a database range with column header. It stores the current settings of sort order and filter.
There is a criteria range in column E. Same header as in A and a regular expression .+ (any content).
With the cell cursor in the database range, Data>Filter>Advanced Filter… calls a filter where E1:E2 is specified as criteria range and the additional options to dump distinct values at Target.A1. There is another database range “_Target” at Target.A1. Its size changes with the count of values dumped from database range “Data”.
Sheet2 has a sheet activation macro refreshing all database ranges.
After modifying data in “Data”, “_Target” adjusts properly when you select its sheet.

  • My first approach with the pivot table works as well.
  • The standard filter is broken.
  • UNIQUE is an array function. Array functions are dysfunctional when array dimensions are changing.
  • There are straight forward database solutions which can also dump distinct elements to spreadsheets.
1 Like
#17

Hi @Villeroy, excellent, I’ll study this solution, congratulations.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.