Compare 2 sets of data by letter or word in Excel | ExcelTutorials
HTML-код
- Опубликовано: 13 дек 2014
- Compare 2 sets of data by letter or word in Excel | ExcelTutorials
Do you want to compare 2 columns and highlight mismatched characters, letters or words in Excel. This video explains how to do this using a simple macro. For example workbook, see below link:
chandoo.org/wp/?p=8930
Do Subscribe, Like & Share my video if you like!!
Click The Below Link To SUBSCRIBE:
/ @chandoo_
Awsome man, you can't even imagine how much this video helped me. May the god of IT bless your soul.
Thank you so much man I was searching for this
Extremely cool!
Great tutorial! Thank you. Don't know if you mentioned it but does the macro work on more columns and rows than the ones in the example?
This saved my ass. Thank you so much!
I only found this Video related to my query...
Really Awasome Video....
Keep it up.....
Glad you liked it
Hi. Thanks for this video
Nice and thanks
A very outstanding knowledge given in
this video. Can it be done without macro?
Can you please help me to write the same script for Google sheets or LibreOffice Calc?
its good, but what about if the other data set is just not sorted and also not adjacent to the first one..? looking forward to it
great tutorial! but i have another query if we wanna find a common word between two cells which function we need to use
use case: getting surname from given data where father name and child name is given only common in both will be surname how we can get the surname in different cell? please make a video on this 🙏
Hi, I am looking for a code to join all word's of a string in a cell. please post the code if it is possible means.
Is this VBA can be created only for 16 rowz?
Tips and explanation are very good, but if possible paste the codes in the description too.
Thanks Nimmi. The code is available in the link in the description.
I have 100 records and pasted in column A column B but result will be upto 16 records only . Please advise to get result for 100 records
Nice video , can you tell me how to pull the differences into a third column
Hi Etimfon.. you can examine the macro and change the code to extract the differences to a third column. If you are new to VBA, see my getting started video here - ruclips.net/video/5k-KpSkrROw/видео.html
question about: Range("list2").Cells(i)
why Cells(i)?
the cells property should have 2 coordinates, not 1, no?
That is Awasome....
Can u please tell me how can i highlight in the long sentences those words which are in Column A.
E. G
Col A has the word "Ways and Means" While as Column B has the sentence in which is this word is present eg "There are various ways and Means to do this work. "
I want to highlight and change color of only these words in Column B and not to whole cell...
Please inform....
Thanks ashiq. Please download the sample workbook and examine the macro. You may be able to customize it to do what you want. You can use either COUNTIFS or FIND to check if words in col A are found in the sentence.
Thank you all Indian people for these great efforts
Please, can I get the number of highlighted words
You are welcome bro... You can use formulas like countifs or word count to do this - chandoo.org/wp/count-words-excel-formula/
I need help
How to make it on any cell not only the "lis1" and "list2"?
I mean how to make on any cell I select on it ?
If column B, words are jumbled(not in order with column A).I am not able to get with vlookup also.can u suggest a method
Yes, I have the same question. What if the B column is jumbled
My son's name is Nishanth too :)
See this page for a technique to deal with jumbled lookups - ruclips.net/video/ELYrVwR7ydo/видео.html
i need to compare data from word and excel simultaneously how can i do it.
It might be easier to copy paste Word data into another tab in excel and compare
@@chandoo_ no the data is numaricals and alphabet but the word data is in alphabetical as well as numaricals that have to be matched with the data in word file.
Any formula is there
Hi Manju... This is all done by VBA.
is there a way we can only highlight only the changed part
like for example:
column 1 : Hi how are you?
column 2 : Hi i am good how are you?
so in the above example can we highlight only "i am good" instead of "i am good how are you?".
hope i was clear.
Hi Rakesh... this requires a bit more VBA than shown in this video. I will make a new post + video when I get around to this. Meanwhile, feel free to download the sample file and modify the macro to highlight the way you want.
chandoo.org/wp/compare-data-highlight-mismatched-letters-words/
@@chandoo_ thanks for replying, yeah sure will do that. :)
Your efforts and video is very appraisable.
but first i encountere many problem.s
1st. your excel sample file was not opening, due to macro security.
2nd. then when i tried it, by chaning one of your value with mine value with number. it gave error, it'll work for string only.
3rd. when i changed number into text... then Match Word function was useless for me... one number counts as one word.
4th... finally other match, worked.
5th. but then when i tried to copy pase your code with my sheet, it gives error "Method 'Range' of object '_Golbal" failed" :(((
(i didn't know how to solve it..)
6th... then i copy paste my data, in your sheet... (where it was working)... now it worked...but... only upto row#16... (while i had more than 300 rows)
7th. then i went to ChatGPT, and asked some help. he did some modifications in your code.
8th. But it again gave error. at Next Cell1..... i changed it to Next i.
9th. viola it workd... but i dont know... some digits it missed... :( (dont know why)
10...
some AI helped me... and gave me below code... which is even better...
(but value has to be converted into text)
.
Sub FindAllMismatches()
Dim i As Long, j As Long
Dim cell1 As Range, cell2 As Range
Dim str1 As String, str2 As String
Dim lastRow As Long
lastRow = Cells(Rows.Count, "J").End(xlUp).Row
For j = 2 To lastRow
Set cell1 = Range("J" & j)
Set cell2 = Range("K" & j)
str1 = cell1.Value
str2 = cell2.Value
For i = 1 To Len(str1)
If Not Mid(str1, i, 1) = Mid(str2, i, 1) Then
cell2.Characters(i, 1).Font.Color = vbRed
End If
Next i
Next j
End Sub
* my data is in J and K column
Hi
I have 100 records and pasted in column A column B but result will be upto 16 records only . Please advise to get result for 100 records
I have 100 records and pasted in column A column B but result will be upto 16 records only . Please advise to get result for 100 records