comparing tables and highlighting the equal cells

comparing tables and highlighting the equal cells
Kindly see the sample file
Avinash SinghAsked:
Who is Participating?
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.

Rob HensonFinance 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.
0
Avinash SinghAuthor Commented:
Kindly see the attachment file
Everything is mentioned
Sample.xlsm
Sample.xlsm
0
Avinash SinghAuthor Commented:
Both attachments are same by mistakely i uploaded twice
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rob HensonFinance AnalystCommented:
Sorry can't download xlsm while at work.
0
Avinash SinghAuthor Commented:
this sample file is in excel workbook format
sample-file-in-excel-workbook-forma.xlsx
0
Rob HensonFinance 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
0
Avinash SinghAuthor 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
0
Rob HensonFinance 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.
0
Avinash SinghAuthor Commented:
sir i need vba to do this
0
Rob HensonFinance AnalystCommented:
Why use VBA when there is potentially an option to do it with standard features?
0
Avinash SinghAuthor 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
0
Rob HensonFinance 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?
0
Avinash SinghAuthor Commented:
yes i can mention that no problem
0
Avinash SinghAuthor 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
0
Rob HensonFinance 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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
Avinash SinghAuthor 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
0
Rob HensonFinance AnalystCommented:
Glad to help
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.