this post was submitted on 27 Dec 2023
88 points (95.8% liked)

Linux

48180 readers
966 users here now

From Wikipedia, the free encyclopedia

Linux is a family of open source Unix-like operating systems based on the Linux kernel, an operating system kernel first released on September 17, 1991 by Linus Torvalds. Linux is typically packaged in a Linux distribution (or distro for short).

Distributions include the Linux kernel and supporting system software and libraries, many of which are provided by the GNU Project. Many Linux distributions use the word "Linux" in their name, but the Free Software Foundation uses the name GNU/Linux to emphasize the importance of GNU software, causing some controversy.

Rules

Related Communities

Community icon by Alpár-Etele Méder, licensed under CC BY 3.0

founded 5 years ago
MODERATORS
 

Short term: I'm trying to automate transcribing a list from:
A
123
234
B
456
567
789
C
345

Into an array/table
123, A
234, A
456, B
567, B
789, B
345, C

Long term answer I'm looking for: where's a lemmy community I can ask questions like this (like about using formulas in Librecalc/Onlyoffice suite/maybe VBA with them too) in the future?

Or, if not yet established on lemmy, recommended librecalc/Onlyoffice forums?

Thank you for your time.

you are viewing a single comment's thread
view the rest of the comments
[–] [email protected] 4 points 10 months ago* (last edited 10 months ago) (10 children)

Are the numeric values always correctly sorted in the cells below the alpha data cells? IE, youd never have A numeric values, then B numeric values, then more A numeric values?

Also just to be clear, are you putting the results in a single cell, or does the comma in your example denote separation between two cells? I'm guessing two cells. I want to work on this, but I just want to make sure I'm working on the correct thing :)

[–] [email protected] 7 points 10 months ago* (last edited 10 months ago) (6 children)

Okay, I couldn't wait so here are my formulas based on the following parameters:

1.) Numeric data always follows in rows after the correct alpha value. IE Anything between A and B values in the A column belong to the A group. Anything between the B and C values in the A column belong to the B group.

2.) You want the output to be in two separate columns.

Given that your input data is in column A and the value "A" begins at A1, here is your formula for B2 (B1 will be empty as there is no numeric value to tie to A1)

=IF(ISERROR(A2/1),"",A2)

This will put numeric values only that are in column A into column B.

Again, given that your input data is in column A and the value "A" begins at A1, here is your formula for C2 (C1 will again be empty as there is no numeric value for your array in A1)

=IF(ISERROR(A2/1),"", IF(ISERROR(A1/1),A1,IF(ISERROR(C1/1),C1,1)))

This will put the correct alpha values from column A into column C in the same rows as the numeric values from column B. If something goes really wrong, it will place the number 1.

Given that there are no gaps in your data, you should just be able to fill down and it'll appropriately put the correct values into columns B and C. At that point, I'd just paste the values of columns B and C into a new sheet (or columns) and sort the data to remove the blank rows.

I just tested it in LibreOffice Calc, and it works perfectly (given those parameters).

Edit: Repeated a word.

[–] [email protected] 6 points 10 months ago (1 children)

I definitely feel like saying it again - it works! Thank you. For sure, I can modify the if statement for what I'm working with. Thank you so much!

[–] [email protected] 1 points 10 months ago
load more comments (4 replies)
load more comments (7 replies)