Many cells to a single reference in excel

Question

I have a table with multiple B values for the same A value, but I'm having trouble figuring out what to search for using the right language. At the present, it appears as follows:

A's	B's
A1	B1
A1	B2
A1	B3
A2	B1
A2	B3
A2	B4
A3	B5
A4	B2
A5	B1
A5	B6

I want to use the top row as "filterable" menu, but I only want to display one of each A at a time so that B values are kept distinct. Any A and all the Bs, or any B and all the associated As should be able to be filtered for. Additionally, values can be sorted without losing the relationship between A and B values. Is it possible to do this without using B-values "across"?

A's	B's
A1	B1
	B2
	B3
A2	B1
	B3
	B4
A3	B5
A4	B2
A5	B1
	B6

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).

Answer 1

Use conditional formatting to hide the A's you don't want if you only require this for the visual aspect. To "hide" the consecutive As, simply determine whether the cell is equivalent to the cell above and use the same colour as the backdrop (or a custom number format like,,,"") to do so.

Another choice is to utilize borders (bottom border) throughout the entire table using the formula $A2>$A3.