Link to home
Start Free TrialLog in
Avatar of nav2567
nav2567Flag for United States of America

asked on

macro to compare 2 Excel columns.

Hello,

I have a spreadsheet with the below layout:

group1            group2
computer 1    computer 5
computer 2    computer 6
computer 3    computer 4
computer 4    computer 1
.....                    ......

i need a macro to put the work MATCH in column 4 when computers in column 2 appears in column 1.  for example:

group1            group2
computer 1    computer 5    
computer 2    computer 6
computer 3    computer 4     MATCH
computer 4    computer 1     MATCH
.....                    ......

Please advise.  

Thanks.
ASKER CERTIFIED SOLUTION
Avatar of AL_XResearch
AL_XResearch
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of nav2567
nav2567
Flag of United States of America image

ASKER

It does not work.  

Group 1 in column A.  Group 2 in column B.  The MATCH will be put in column D.

thanks.
SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AL_XResearch
AL_XResearch
Flag of United Kingdom of Great Britain and Northern Ireland image
nav2567:

It does work, i have tested it, you just need to adjust the columns / rows to match your sheet
Avatar of nav2567
nav2567
Flag of United States of America image

ASKER

Al_XResearch

I am not follow your formula.  Do you mind modify it for me?  

I have Group 1 in column A.  Group 2 in column B.  The MATCH will be put in column D - see above again.

Shums,

I tried your formula.  I took out an entry in column A but column D still shows MATCH which is not right.  

Thanks.
Avatar of Shums Faruk
Shums Faruk
Flag of India image
Then try below:
=IF(COUNTIF(B:B,A2)>0,"Match","")
Avatar of nav2567
nav2567
Flag of United States of America image

ASKER

Shums, now, the compare column showing everything is blank instead of 90% match.
Avatar of Shums Faruk
Shums Faruk
Flag of India image
Try this:
=IF(ISNUMBER(MATCH(B2,$A:$A,0)),"Match","")
Avatar of nav2567
nav2567
Flag of United States of America image

ASKER

no good.  The first entry in b2 is somewhere in column A.  It shows blank in column C.  

Test it and you will see.
Avatar of Shums Faruk
Shums Faruk
Flag of India image
I did tested, its working see attached...
Screenshot_2017-04-21-03-18-17-625.jpeg
Avatar of nav2567
nav2567
Flag of United States of America image

ASKER

I have realized I have an extra space right after each name in column B.  My bad, my apologies to both of you!!!

Is it possible to remove that extra space after each name in column B?
Avatar of AL_XResearch
AL_XResearch
Flag of United Kingdom of Great Britain and Northern Ireland image
Nav256:

My formula and the version from 'shums' are basically the same (just written slightly differently) - "more than one way to skin an egg"

I have created a workbook that shows both methods working. Please see attached
Avatar of nav2567
nav2567
Flag of United States of America image

ASKER

Sorry, I do not see any attachment.

Is there a way to remove the extra space right after each name in column B?
Avatar of AL_XResearch
AL_XResearch
Flag of United Kingdom of Great Britain and Northern Ireland image
The only way you could remove the trailing space (or even a preceeding one) would to to add to normally hidden columns in between the current ones as 'helper columns' and use TRIM() on the previous columns value.

So Columns A & C would be the 'Group' data and columns B & D would be the trim formula to remove the preceding or trailing spaces such as B2 formula is '=TRIM(A2)' and then change the 'result' formula in column E (formally D) should be amended to work with columns B & D rather than A and B