!

Hi

I am new to VBA with excel and I was wondering if anyone could help me with my problem.

I have a sheet with 800 rows. There are various columns. I am currently trying to do a test on 2 columns called "Tax Code" and "Company". Tax Code has 5 values only - S1,S2,P1,P2,P5.

The company has various company names.

If there are multiple entries for a company they should technically all have the same tax code. Say for example, there are 5 entries for Wipro, all five should have tax code say S1.
I need to find out the companies with multiple tax codes. Say 2 entries for Aviva has S2 and one has P1 hence, its an exception and i need to find them.

Attached an example

Thanks

Hi and welcome to the forum

I have looked at your file, and dont see any "Company" column, nor do I find any company called Wipro?

OK, try this....

in Q, copied down, use this...=M2&B2

Then copy column C to S and use "remove duplicates" to give you a list of unique companies

Tax Code has 5 values only - S1,S2,P1,P2,P5.

These are the actual codes you have in that file.....S1 S2 P1 P2 P5 P7

I added those as headings to T1:Y1, then in T2, copied down and across, I used this...
=COUNTIF($Q:$Q,$S2&T$1)
in column Z, use this, copied down...
=IF(COUNTIF(T2:Y2,"<>0")<=1,"",COUNTIF(T2:Y2,"<>0"))

You will see from this that out of 91 different companies, 21 have multiple tax codes

chhavi,

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.