LinkBack URL
About LinkBacks
Hi
I am looking for a macro that will look at data in a worksheet "tab 1 column a" and if is duplicated in " tab 2 column a" then put the word match in "tab 2 column b" next to duplicated data.
Any help appreciated
Regards
Hi
I am looking for a macro that will look at data in a worksheet "tab 1 column a" and if is duplicated in " tab 2 column a" then put the word match in "tab 2 column b" next to duplicated data.
Any help appreciated
Regards
Last edited by shrubfact; 02-14-2017 at 09:50 PM.
Put this formula in Sheet2 B1 and copy down the column. Change the Sheet1 name to suit.
=IF(A1="","",IF(COUNTIF(Sheet1!A:A,A1),"Match",""))
Surround your VBA code with CODE tags e.g.;
[CODE]your VBA code here[/CODE]
The # button in the forum editor will apply CODE tags around your selected text.
hi thanks for the help. This worked perfectly but after some testing I am spotting some errors as its not showing some as a duplicate although it definitely is. Possibly where I am concatenating 5 cells and the formula is working with this ? I'm not sure.
Would a better way be to look at the 5 cells in the row separately ?
Hi shrubfact,
To make things easier for all of us, I would suggest you attach a sample workbook.
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Regards.
Please consider:
Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
@ AlphaFrog,
Maybe we should consider adding the Trim Function to your Formula?
Just a wild guess.
Regards.
I suspect for the duplicates that are missed, they are not an exact match. One of the two duplicates may have a trailing space character or something e.g.; "TextA" <> "TextA ".
You could do a simple test with a formula like
=A1=A10
Where A1 and A10 are duplicates. It should return TRUE if they are exact matches.
This formula would "Match" if any other cell contains the value from A1.
=IF(A1="","",IF(COUNTIF(Sheet1!A:A,"*" & A1 & "*"),"Match",""))
This could make false matches (TextA is contained in TextAB) depending on the nature of your data.
Hi winon
ok will try and upload over next few days
I did try trimming but still no luck
Hi alphafrog
I will try that tomorrow at work to see if exact match
Thankyou
The Trim function wouldn't help with this formula. It only would trim one of the duplicates for any given row.Originally Posted by shrubfact
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks