Hello!
I need help with writing a formula that compares column A with columns B-K. All of the columns contain about 100 rows of data, and the first row contains the name of the respective column.
The formula would have to compare A with B-K, and if any match (e.g. A20 matches B10), to display the name of the column (B), or rather the first row of the column that matched (B1)
Any help would be greatly appreciated!
maybe a simplified .ods to formalize what you expect ?
what if no match ?
what if multiple matches ?
etc
Hey,
every single cell in column A matches a cell in either of the other columns. There is always exactly one match.
Attaching a simplified example.
Next time upload the file itself instead of a picture, please. When a sample already has formulas, formattings and settings, it is impossible to analyze a problem from a screenshot.
I apologize, this was my first time posting and I wasn’t sure; since it was just a visual example without any formulas, I figured this would be enough. I’ll keep it in mind, thank you!
The assurance given replying to @fpy is essential. The image (please prefer example files!) also shows that the results are per row. This allows for a reasonable answer.
See this example sheet..
This was exactly it, thank you so much!
Would it be also possible to, then, sort the formula in either ascending or descending order, so the output columns are grouped together?
Thank you once more!
Suppose “sort the formula” is meant in the sense of “enhance the formula to get the output sorted”.
If so: Calc has the well known means to sort rows (sometimes columns) of data interactively (>Data>Sort...).
Sorting by formulae is not supported directly. Workarounds require a lot of preparation. Not recommended.
As is usually the case, the interactive method can become the basis of a “one-click solution” by writing custom user code…
As mostly again, going that way you will have to navigate between the Scylla of extreme specialization and the Charybdis of a great need for overhead effort for parameterization.
What about simply pivoting the table? If I use 2 columns (one being filled with the formulae you provided, and the other a copy of column A), I can sort of get it to work, but I am left with either gaps between data under the same named column (since it categorizes the numbers in order), or the numbers I’m working with aggregating into one row instead of simply displaying row by row.
I just need it to be a bit more presentable, and I can’t figure it out.
