I’m not sure if Calc can do this, but I’ll explain it as best I can.
I have Column A of Names. Column B, C and D contain data to be searched. This data is from a static list of 20 values. For now I’ll use integers from 1 to 20 for this data. However, the actual data is a static list of strings. i.e. no regex/wildcard pattern matching needed.
A B C D
Joe 5 13 10
John 4 10 7
Jane 16 5 18
Mary 10 7 4
Now, I create a cell on a different sheet or same sheet, doesn’t matter. This cell is a dropdown of the values 1-20.
I want to select a value, lets say 10. I then want the cells below the selected value to display Joe, John and Mary, but not Jane since she does not have 10 in her row, with each in their own row in the column, not concatenated together in one cell.
Where A1 is the selected value from the dropdown.
Now, the data cannot be sorted so all matching data is in the same column, the position of the original data is important and can’t be moved, but the results are not. For example, I do not need to know which column Joe has a value of 10 in, only that Joe has a 10, i.e. his name is on the new list, but I can’t move the 10 value to column B from D.
I have a feeling the answer will be: put it all in Base and query from there with data-sources.