Many cells to a single reference in excel

0 votes

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

Nov 5 in Others by Kithuzzz
• 20,660 points
19 views

1 answer to this question.

0 votes

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.

enter image description here

answered Nov 5 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24 in Others by narikkadan
• 37,660 points
57 views
0 votes
1 answer

How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

answered Nov 17 in Others by narikkadan
• 37,660 points
17 views
0 votes
1 answer

Export many small DataFrames to a single Excel worksheet

Try  this: from pandas import ExcelWriter def dfs2xlsx(list_dfs,xls_path = ...READ MORE

answered Nov 17 in Others by narikkadan
• 37,660 points
21 views
0 votes
1 answer

How to fix a circular reference error by if condition in excel file?

Circular reference in this context refers to ...READ MORE

answered 5 days ago in Others by narikkadan
• 37,660 points
14 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
449 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 58,100 points
394 views
0 votes
1 answer

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

answered Oct 15, 2018 in RPA by Priyaj
• 58,100 points
3,365 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
2,816 views
0 votes
1 answer

In Excel, how to find a average from selected cells

If one has the dynamic array formula ...READ MORE

answered Oct 9 in Others by narikkadan
• 37,660 points
47 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP