Calc: extract cell value next to cell with specific string

Hi all,

I have exported activity data from a sports device to CSV and opened it with LO Calc. Lines correspond to timestamps, uneven columns contain an identifier (string) whereas even columns contain the corresponding value. However, during recording additional cells have been inserted, hence if I’m looking for the identifier “cadence” it may be in column C most time (with D containing the recorded cadence value), but occasionally in other columns, with the corresponding value always being in the cell immediately to the right of it.

I’m now looking for a way to extract all cadence values, no matter in which column the identifier “cadence” is located.

Any suggestions how to solve this would be much appreciated!

Best,
r.

Welcome back!
May be something like this?

{=TEXTJOIN(";";1;IF(A1:G200="cadence";B1:H200;""))}

Thanks. For whatever reason, these array functions (I’ve tried several before) will always return #VALUE!. Any suggestion how to debug this? I’m on 7.6.7.2 under Linux.

I checked the proposed formula in 7.5.3.2 and 25.2.3.2 - it should work. Is it an array formula? If you edit the formula in the Function Wizard, is the Array checkbox checked?

image

Strange, very strange. If the error was in the range sizes, the error would be #N/A.

Let’s try two ways to solve the problem at once - let’s try to make the formula work and let’s try to read the original CSV file so that we don’t have to fish out these cadences in different columns, get them all in column D.

I’ll tell you about the formula, and you’ll try to adjust it according to your data.
The main trick is performed by the nested IF() function - it looks for the word cadence in each of the cells of the rectangular range. It is assumed that if a cell contains this word, then only this word, no spaces in front or behind, no other text. (If this is not the case, then the comparison - the first parameter of the IF() function - will need to be done differently).
If the cell contains anything other than the word cadence, then the IF() function returns an empty string. It’s simple, it’s clear. But if the word is found, then the value from the next cell is returned. And it’s done quite simply - the second range (in the screenshot it’s red) is exactly the same size as the range in which we’re looking for the word - blue - but shifted to the right by one column.


As a result of entering such a function IF() as an array formula (otherwise it will not work - after all, its parameters are arrays of values), we will get a rectangular array of the same size as the original ranges, filled with either empty strings or the found values.
Now we simply give this array to the TEXTJOIN() function and ask it to throw away empty values ​​(second parameter 1), and what remains to be concatenated into a long string through the separator from the first parameter. We enter all this using Ctrl+Shift+Enter (or using the Function Wizard and force the Array checkbox). This should work.
We could limit the ranges to one row, something like =TEXTJOIN(";";1;IF(C2:G2="cadence";D2:H2;"")) and copy that formula down - then we would have all the cadence values ​​in one column - each value in the same row where it was found.

You didn’t say what values ​​are in the cells next to the word cadence - strings? dates? integers?

Now let’s try to read the CSV file correctly. When we open it in Calc, we should see the Text Import settings window. I’m sure that if you correctly set the flags in this window, the file will be read as a neat table, the data will not be scattered across different columns. I know that you have been using the office for many years, but even veterans make mistakes. If it doesn’t work, then give a screenshot from this window, we’ll try to find the cause of the import error.

2 Likes

although just attaching your file would avoid lengthy tedious lengthy tedious lengthy tedious guesswork :innocent:

Ask/Guide - How to use the Ask site - The Document Foundation Wiki #More_details

Thanks, the Array Function checkbox obviously was the culprit, it is working now - thanks for pointing me at this! :slight_smile:

@fpy: Agreed, a MWE (or MNWE for that matter) would have made it way easier, I’ll keep that in mind :wink: