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, 2022 in Others by Kithuzzz
• 38,010 points
257 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, 2022 by narikkadan
• 63,700 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, 2022 in Others by narikkadan
• 63,700 points
610 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, 2022 in Others by narikkadan
• 63,700 points
3,253 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, 2022 in Others by narikkadan
• 63,700 points
381 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 Nov 21, 2022 in Others by narikkadan
• 63,700 points
514 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,740 points
980 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,090 points
900 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,090 points
4,175 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
3,967 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, 2022 in Others by narikkadan
• 63,700 points
557 views
0 votes
1 answer

Is there any way in python to auto-correct spelling mistake in multiple rows of an excel files of a single column?

Use Spellchecker for doing your stuff: import pandas ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,700 points
1,707 views
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