I have names that are imported from lists. I would like to eliminate the duplicated rows for the names in reverse order.
A B
John Fred
Fred John
I only need one connection between them, not a connection for each direction.
Welcome! Please either expand the sample data or give a more complete description of the problem. What you have now put in the text of the question can be reduced to “clear column B” (because regardless of the order of the values in this column, they are all present in column A)
You may add a second column to count. Use COUNTIF either using adresses above the current line like $A$1:$A10 if you are in line 10 or count below $A10:$A$10000 . As this included the current line there should be no 0, if your formula is correct. Lines with value >1 are repeated elsewhere.
.
You can neither clear nor remove data by formula. If you don’t want to create a macro you have to do this yourself using filter (all lines >1) or sort by the counted number (Attention on sort order here: If COUNTIF looks below, you will need the bigger numbers on top, so reverse order). Then delete the lines yourself.
We may need a script for duplicate questions…
This doesn’t seem like a good idea to me. There is no need for such a script as long as those who remember how all these tricks appeared 5, 10, 20 years ago and even at the end of the last millennium are alive. In addition, smart people say that
Might not AI (ArtificialIgnorance) be helpful in this case as in many others?
Think less, do your work!
As long as there is more than enough “natural Ignorance”, we do not need “artificial Ignorance”.
…
For exactly the same reason we need “artificial Intelligence” !
(Be cautious! Psilosophy may endanger psychic stability.)
The idea “we” should solve our problems by introduction of new technologies “as we so often did” (cf Asimov, The Martian Way) may just be the “ultima irratio” (cf Willy Brandt) in this case. (Excuse my prehistoric references!)
However, very new things may emerge from the attempt. They will not be “Mankind is Rescued!”, expectably. In what future time frame can the strategy work over again?
There are a few very good recent examples of the kind in the forum.LibreOffice (Calc branch). I was prepared.
Oh sorry. I missed that the correct failure of the example you mentioned probably depends on perfect VBAsupport?
No. (And a few more characters. Proper AI will rcognize them as what they are?)
I have no idea - I’m also not going to touch this miracle with my hands: living people are better
Assuming data like:
| name | name |
|---|---|
| John | Fred |
| Fred | John |
| Adam | Smith |
| Smith | Adam |
| Adalbert | Duck |
should be reduced to:
| name | name |
|---|---|
| John | Fred |
| Adam | Smith |
| Adalbert | Duck |
from more_itertools import unique_everseen
def filter_unique_any_order(*_):
doc = XSCRIPTCONTEXT.getDocument()
sel = doc.CurrentSelection
sheet = sel.Spreadsheet
cursor = sheet.createCursorByRange(sel)
cursor.collapseToCurrentRegion()
data = cursor.DataArray
data = tuple( unique_everseen(data, set))
cursor.clearContents(15)
cursor.collapseToSize(len(data[0]), len(data))
cursor.DataArray = data
That is an example of what I am looking for. I have a spreadsheet of >60K lines that was aggregated from several csv files. With columns comparing the info from the source’s file to matching target’s. When combined to one spreadsheet the source/target info is reversed in columns so that there is a to and from each source/target. I just need one link row for each matched pair, whichever order is not pertinent.
Method I ended up using:
created four new columns
did an IF < and > to put the identifiers in the same order
sorted all columns by these columns
did an IF to see if consecutive rows had the same values in these columns (true or false)
copied the value of that to the last column
after verifying this worked sorted the columns by the value column
deleted all rows with true
There may be a more automated way, but I’m not that well versed with this