| View previous topic :: View next topic |
| Author |
Message |
hubertT
Power User
Joined: 03 Apr 2007
Posts: 50
Location: Birmingham, UK
|
 Posted: Mon Aug 06, 2012 6:25 am Post subject: SOLVED- In OOo Calc can I compact number list w/o duplicate? |
 |
|
I have a variable column of numbers which includes duplicate numbers and I need to compact the list of numbers without any duplicates. Any ideas please?
Last edited by hubertT on Tue Aug 07, 2012 6:03 am; edited 1 time in total |
|
| Back to top |
|
 |
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1851
Location: Sydney, Australia
|
| Posted: Mon Aug 06, 2012 7:23 am Post subject: |
|
|
DataPilot or Standard Filter are best tools for removal of duplicates.
Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
hubertT
Power User
Joined: 03 Apr 2007
Posts: 50
Location: Birmingham, UK
|
| Posted: Mon Aug 06, 2012 7:42 am Post subject: |
|
|
| ken johnson wrote: | DataPilot or Standard Filter are best tools for removal of duplicates.
Ken Johnson |
Thanks Ken - I tried Standard Filter and may not fully understand DataPilot but do you agree that both those tools need manual intervention each time my 'duplicated' list changes?
I am trying to find a dynamic means. I concocted an auto means adopting 'match', 'max' and 'index' functions and wondered if there is already a maths/statistic/text function to do the task. |
|
| Back to top |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1851
Location: Sydney, Australia
|
| Posted: Tue Aug 07, 2012 3:13 am Post subject: |
|
|
See attached file where A2:A1001 has random numbers with duplicates...
http://www.mediafire.com/view/?69u9m5kvpr5n95t
Column C has this helper formula that returns the row number only when the current column A value has not already appeared in the previous rows...
| Code: | | =IF(A2="";"";IF(COUNTIF(A$2:A2;A2)>1;"";ROW())) |
This can be filled down column C as far as you like provided it at least reaches down to the last column A row with a number.
This formula in B2, filled down column B, uses the values in column C to return all of column A's unique values...
| Code: | | =IF(ROW(C2)-ROW(C$2)+1>COUNT($C$2:$C$65536);"";INDEX($A$1:$A$65536;SMALL($C$2:$C$65536;ROW(C2)-ROW(C$2)+1))) |
Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Last edited by ken johnson on Sat Sep 22, 2012 9:55 pm; edited 1 time in total |
|
| Back to top |
|
|
hubertT
Power User
Joined: 03 Apr 2007
Posts: 50
Location: Birmingham, UK
|
| Posted: Tue Aug 07, 2012 5:50 am Post subject: |
|
|
Ken,
Thank you very much - perfect solution.
I cut and pasted your formulae and they worked perfectly.
Shall try to fathom how to mark my problem thread, as now solved.
I will adapt your column C formula with the help of the address-function, so it will be specific as to the address-range of my column of figures.
I was not sure about and did not use the 4share process at:
http://www.4shared.com/file/d_l774g2/Remove_duplicates_via_helper_c.html
because twas not clear which 'download' click was the one to your help-file.
Thanks again.
Kind regards,
Hubert
ps
Do you know of any threads/info regarding loss of named cells/ranges/tables when 'copying individual sheets from one document to another document (whether new or existing)? |
|
| Back to top |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1851
Location: Sydney, Australia
|
| Posted: Tue Aug 07, 2012 7:26 am Post subject: |
|
|
| hubertT wrote: |
twas not clear which 'download' click was the one to your help-file. |
I agree. It is annoying.
| hubertT wrote: |
Do you know of any threads/info regarding loss of named cells/ranges/tables when 'copying individual sheets from one document to another document (whether new or existing)? |
Sorry I don't.
All I know is that unlike Excel, Calc does not include named ranges when a sheet is copied to another document. I am still using OOo 3.3 so I don't know about Apache OOo 3.4.
Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
hubertT
Power User
Joined: 03 Apr 2007
Posts: 50
Location: Birmingham, UK
|
| Posted: Tue Aug 07, 2012 7:48 am Post subject: |
|
|
| hubertT wrote: |
Do you know of any threads/info regarding loss of named cells/ranges/tables when 'copying individual sheets from one document to another document (whether new or existing)? |
| ken johnson wrote: |
All I know is that unlike Excel, Calc does not include named ranges when a sheet is copied to another document. I am still using OOo 3.3 so I don't know about Apache OOo 3.4.
|
Seems OOo 3.4 intends transporting named cell/ranges/table with a copied sheet. However, my experience (which could be due to 'my fault'), is that sometimes some named ranges are mysteriously lost on-route - including cells, short-column-sections and small-area ranges (all set-up using the 'insert' menu. Copying tried by 'cut & paste', by 'sheet insert' menu option, and by right-click on sheet-tab.
Thanks again.
Hubert |
|
| Back to top |
|
|
|
