# comparing tables and highlighting the equal cells

comparing tables and highlighting the equal cells
Kindly see the sample file
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Finance AnalystCommented:
Avinash - no attachment of sample file.

To attach a file, select Attach File, Browse to the required file and click upload. A description box will appear which needs to be completed before clicking Submit.

Unless the attachment when uploaded gives more detail, you will have to expand on your requirement.
Author Commented:
Kindly see the attachment file
Everything is mentioned
Sample.xlsm
Sample.xlsm
Author Commented:
Both attachments are same by mistakely i uploaded twice
Finance AnalystCommented:
Author Commented:
this sample file is in excel workbook format
sample-file-in-excel-workbook-forma.xlsx
Finance AnalystCommented:
You can do that with a couple of Conditional Formatting rules.

Conditional Formatting is applied from the Home Ribbon, Styles group. Click the button and choose Manage Rules. The CF Manager will show, in the dropdown at the top select "This Worksheet" and you will see four lines with the rules.

The rules are split into two groups:
- checking row 1 with rows 8, 13 & 19
- checking row 2 with rows 9, 14 & 20

The rules on rows 1 & 2 use the following formulas:
Row 1 =AND(A\$1<>"",OR(A\$1=A\$8,A\$1=A\$13,A\$1=A\$19))
Row 2 =AND(A\$2<>"",OR(A\$2=A\$9,A\$2=A\$14,A\$2=A\$20))

The rules on the other rows use formulas:
Rows 8, 13 & 19 =AND(A8<>"",A8=A\$1)
Rows 9, 14 & 20 =AND(A9<>"",A9=A\$1)

See attached.
sample-file-in-excel.xlsx
Author Commented:
i have thousand of rows sir
thnx for giving ur precious time and great efforts  and interest in this post
But it will be ur great help if u make the vba code for the same
Finance AnalystCommented:
Do your tables have consistent headings for each of the rows, ie Row 1 heading in table 1 is same as Row 1 heading in tables 2, 3, 4, 5, etc.

If so, the Rules could be tweaked to allow for the row heading comparison and applied to an area as a whole to include all tables rather than applying to each individual table.
Author Commented:
sir i need vba to do this
Finance AnalystCommented:
Why use VBA when there is potentially an option to do it with standard features?
Author Commented:
i don't have headings which u said
kindy look my sample file sir and do what u want to do without affecting the data and solve it
and send me the attachment of the same as i am new to excel
Finance AnalystCommented:
Your sample had Row 1 and Row 2 as headings in column K; would these be there for the real file and against all tables?
Author Commented:
yes i can mention that no problem
Author Commented:
but plz note that we have to compare table1 with table2 and highlight the data
and table1 with table3 and highlight the data
And nothing to do with table4
Finance AnalystCommented:
OK, amended the file as attached.

You can achieve it with two rules.

Rule 1, apply to the whole of Table 1:
=COUNTIFS(A\$8:A\$18,A1,\$K\$8:\$K\$18,\$K1)>1

You will need to change the references to A8:A18 and K8:K18 so that the 18 is the row above table 4 so that it doesn't count entries in table 4.

Rule 2, apply to whole range to include all tables below as required, I have applied to range from top of table 1 to row above table 4:
=AND(A8<>"",A8=INDEX(\$A\$1:\$J\$2,MATCH(\$K8,\$K\$1:\$K\$2,0),COLUMN(A8)))

Once the formatting is applied, if rows are inserted into the range between rows 8 and 18 (or whatever in real file) the new rows will get the formatting automatically.

Hope that helps.
sample-file-in-excel.xlsx

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
thnx Rob Henson sir u r  the best of best & thnx for understanding me and helping me from various ways for this problem u r very supportive hats off sir
Finance AnalystCommented:
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.