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.
I don’t think that formula is the right tool to do this. You need to write a macro.
Try asking at [email protected]
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 :)
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.
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!
Any time!
Nice!
Still thinking about this, if the “alpha” value in column A is actually a date value, this formula would need more checks. Let me know if that’s the case, and I can work something out.
To clarify the first, yes to your example, I think so. I’m trying to optimize my scanning process, so, optimally, yes, I would only record all A numeric values in one go, and not come back to them again for the rest of the list. Good question; I never thought about this as a complication. So, I have shelves among shelves with a strict naming convention and containers with a different naming convention. I’m figuring out how to help me and my family be lazier and just capture the shelf name, then all the stuff there, then move on to the next place.
To clarify the second concern, yeah, I meant to make the comma denote two separate cells. I’m not sure how to make a table on lemmy through the Boost app.
Actually, shoot… After rereading your reply, I might have taken your example data too literally. Are the alpha values truly always alpha and your numeric values always numeric? Otherwise it won’t work, and I’ll have to find another solution.
No worries! I just like to be absolutely sure, because I’ve seen a lot of fringe cases in excel that really make you think hard about how you’re going to lay everything out. I just wanted to be sure before I worked on it. I love Excel, though, and wouldn’t have minded doing it over lol.
The formulas in my other reply should absolutely work. Let me know if you need help with that!
Also, I’d definitely be interested in a “Libre/OpenOffice help” group. I think it’s fun finding creative and clean ways to solve things that seem difficult.
How can you distinguish the the alpha cells from the numeric cells?
If you can fit that distinction in an “if” condition I would start by making an adjacent column along the lines of “if left cell is alpha then left cell else above cell”. Then you’ll have what you need except you’ll have some rows of AA BB etc. You could then either delete those rows (but only after copying and pasting values — not formulas) or better yet make a pivot table.
I usually divide a cell’s value by 1. A value that isn’t numeric will throw an error. Combining that with ISERROR or IFERROR, you can do some pretty powerful stuff.
Yeah, there are plenty of ways of doing that, but I was assuming OP’s sheet had other types of cells and that the A’s and 123s were a minimal working example.
And you were correct! I take sample data too literally lol
Also: this website shows up in my search results a lot. I know it’s not a Lemmy community, but maybe we should make a spreadsheets community if there isn’t one already!
A spreadsheet is always going to be a bad fit for a problem like this. You want something like the command line tools sed and awk (maybe combined with some simple regex) to parse a stream of input like this. These tools were literally built to solve this kind of problem. If you are stuck in windows, the Windows Subsystem for Linux will have these tools.
