Sorry, I can't figure out what to search for using the correct terminology, I have a table with Multiple B values for the same A value. At the moment it looks like this:

I want to use the top row as "filterable" menus though only show (visibly) only one of each A keeping separate values for B's. I want to be able to filter for any A and get all the B's or any B and see the associated A's. As well as sorting values without loosing the association between A's and B's. Can this be done without going "across" with B-values?

I tried merging A's though when I filter for say B6 among the B's I, of course, see no A's value and this of course also messes up the sorting. Just leaving cells empty "as in above" does not work either (goes without saying).

Hope I managed to explain myself well enough. Any concrete solutions/suggestions or a reference to information I can use to figure this out would be greatly appreciated.

Best regards

CodePudding user response：

if you only need this for the visual aspect, I suggest you use conditional formatting instead to hide the A's you don't want. just check if the cell is equal to the cell above and use either same color as background color (or use custom number format ,,,"") to "hide" the consecutive A's.

Another option is to use borders (bottom border) with using formula $A2<>$A3 across the entire table.