[Solved] Align data to keys in column A

Discuss the spreadsheet application
Post Reply
sura
Posts: 26
Joined: Wed Jul 30, 2014 1:03 am

[Solved] Align data to keys in column A

Post by sura »

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,
Last edited by sura on Tue Oct 26, 2021 7:41 pm, edited 3 times in total.
OpenOffice 3.1 on Windows Vista
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: Sort and jump lines

Post by eeigor »

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.

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
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Sort and jump lines

Post by karolus »

Hallo

Start in Cell C2 with Formula:

Code: Select all

=IFNA(IF(MATCH(A2;$B$2:$B$1000;0);A2);"")
pull down as far you need, choose →Data→Calculate→Formula to Value
and finally remove Column B
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: Sort and jump lines

Post by eeigor »

@karolus, very good!
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
sura
Posts: 26
Joined: Wed Jul 30, 2014 1:03 am

Re: Sort and jump lines

Post by sura »

Very clever way to solve it.

Thanks guys !
OpenOffice 3.1 on Windows Vista
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Sort and jump lines

Post by karolus »

Hallo

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
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
sura
Posts: 26
Joined: Wed Jul 30, 2014 1:03 am

Re: [Solved] Sort and jump lines

Post by sura »

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
Attachments
test2.ods
(9.74 KiB) Downloaded 106 times
OpenOffice 3.1 on Windows Vista
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Sort and jump lines

Post by MrProgrammer »

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.
No evil macros are needed. Read about functions MATCH and OFFSET. Learn about usage of $ in formulas in the link below.
202110251127.ods
(11.77 KiB) Downloaded 101 times
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.

[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).
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Sort and jump lines

Post by karolus »

Start in F2, G2, H2 with formulas:

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);"")
select F2:H2 and pull down.

the rest as described in my first answer.
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Sort and jump lines

Post by karolus »

as usual the evil python-code:

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  
@MrProgrammer: your Formulas look also a bit evil to me ;)
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
sura
Posts: 26
Joined: Wed Jul 30, 2014 1:03 am

Re: Sort and jump lines

Post by sura »

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
OpenOffice 3.1 on Windows Vista
Post Reply