[Solved] Align data to keys in column A
[Solved] Align data to keys in column A
Hello
I have A B C D... in A1, A2, A3, A4, and A C D... in B1, B2, B3, B4
I want the results in A to be aligned with the results in B.
Please see added document for more clear description.
Thanking in advance,
I have A B C D... in A1, A2, A3, A4, and A C D... in B1, B2, B3, B4
I want the results in A to be aligned with the results in B.
Please see added document for more clear description.
Thanking in advance,
Last edited by sura on Tue Oct 26, 2021 7:41 pm, edited 3 times in total.
OpenOffice 3.1 on Windows Vista
Re: Sort and jump lines
Well, in the simplest version, like yours, this task is solved by a macro. This is if you don't need to shift the cells in the left columm. That is, the dataset on the right is the truncated dataset on the left. Otherwise, it is necessary to finalize the "If" block in the loop of row iteration.
Select a range sorted by two columns to align the entries.
Select a range sorted by two columns to align the entries.
Code: Select all
Sub AlignEntriesInSelection()
''' Let's assume that the data is already sorted by 2 columns.
'''
Dim sel As Object, c1 As Object, c2 As Object
Dim i&
sel = ThisComponent.CurrentSelection
For i = 0 To sel.Rows.Count - 1
c1 = sel.getCellByPosition(0, i)
c2 = sel.getCellByPosition(1, i)
If c2.Formula > c1.Formula Then 'Finalize it yourself
Call InsertCell(sel, c2)
End If
Next i
End Sub
Sub InsertCell(sel As Object, cell As Object)
Dim sht As Object
Dim addr As New com.sun.star.table.CellRangeAddress
sht = sel.Spreadsheet
With sht
addr.Sheet = sht.RangeAddress.Sheet
With addr
.StartColumn = cell.CellAddress.Column
.StartRow = cell.CellAddress.Row
.EndColumn = .StartColumn
.EndRow = .StartRow
End With
.insertCells(addr, com.sun.star.sheet.CellInsertMode.DOWN)
End With
End Sub
- Attachments
-
- xxx (1).ods
- (12.49 KiB) Downloaded 124 times
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
Re: Sort and jump lines
Hallo
Start in Cell C2 with Formula:
pull down as far you need, choose →Data→Calculate→Formula to Value
and finally remove Column B
Start in Cell C2 with Formula:
Code: Select all
=IFNA(IF(MATCH(A2;$B$2:$B$1000;0);A2);"")
and finally remove Column B
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Re: Sort and jump lines
Hallo
Just for fun with python (on selected 2_Column_Range ):
Just for fun with python (on selected 2_Column_Range ):
Code: Select all
def line_up(*_):
doc = XSCRIPTCONTEXT.getDocument()
sel = doc.CurrentSelection
data = sel.DataArray
out = []
a, b = list(zip(*data))
for entry in a:
out.append((entry, (entry if entry in b else '') ))
sel.DataArray = out
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Re: [Solved] Sort and jump lines
Now I'm facing an even more complicated problem.
Not only the content in B must move, but the content with C and D which is actually linked to B must move as well.
Perhaps you would be able to solve even this? The sheet is huge and trying to do all manually is literally killing me :=)
Thanking in advance,
Best regards
Not only the content in B must move, but the content with C and D which is actually linked to B must move as well.
Perhaps you would be able to solve even this? The sheet is huge and trying to do all manually is literally killing me :=)
Thanking in advance,
Best regards
- Attachments
-
- test2.ods
- (9.74 KiB) Downloaded 107 times
OpenOffice 3.1 on Windows Vista
- MrProgrammer
- Moderator
- Posts: 4904
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Sort and jump lines
No evil macros are needed. Read about functions MATCH and OFFSET. Learn about usage of $ in formulas in the link below. If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.sura wrote:Not only the content in B must move, but the content with C and D which is actually linked to B must move as well.
[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Sort and jump lines
Start in F2, G2, H2 with formulas:
select F2:H2 and pull down.
the rest as described in my first answer.
Code: Select all
=IFNA(IF(MATCH($A2;$B$2:$B$1000;0);A2);"")
=IFNA(IF(MATCH($A2;$B$2:$B$1000;0);C2);"")
=IFNA(IF(MATCH($A2;$B$2:$B$1000;0);D2);"")
the rest as described in my first answer.
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Re: Sort and jump lines
as usual the evil python-code:
@MrProgrammer: your Formulas look also a bit evil to me
Code: Select all
def line_up(*_):
doc = XSCRIPTCONTEXT.getDocument()
sel = doc.CurrentSelection
data = sel.DataArray
out = []
a, *r = list(zip(*data))
r = list(zip(*r))
l = len(r[0])
for entry in a:
has_key= list((filter(lambda x: x[0]==entry, r)))
if has_key:
out.append((entry, *has_key[0]))
else:
out.append((entry, *('',)*l))
sel.DataArray = out
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Re: Sort and jump lines
Big thanks again, both of you
It took me some time to understand how to use $ in formulas, but now I was finally able to replicate what you did.
Have a great evening
It took me some time to understand how to use $ in formulas, but now I was finally able to replicate what you did.
Have a great evening
OpenOffice 3.1 on Windows Vista