Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-02-2021, 11:50 AM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help with a script to find Duplicate data in the same row of a table or tables Windows 10 Help with a script to find Duplicate data in the same row of a table or tables Office 2019
Competent Performer
Help with a script to find Duplicate data in the same row of a table or tables
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 123
Cendrinne is on a distinguished road
Default Help with a script to find Duplicate data in the same row of a table or tables

Hello Pros,


I found a fantastic macro, here, to highlight duplicate data in each rows, but for the first two columns, or the only two columns.

I've tried, so much, to modify, it, either I get an error message, or doesn't do anything.

This is the link of the script:
HTML Code:
https://www.msofficeforums.com/word-vba/28692-using-vba-compare-highlight-words-word-tables.html
I don't really understand it as it is, but tried many things, even modified the cell numbers for the columns I wanted to compare, but I got an error message. It did it, but I don't know how to get rid of that error message .

I need is to Highlight any variable (letters, symbol or numbers) in the same row, of one Table or any tables.

Any insights, would be greatly be appreciated.

Cendrinne
Reply With Quote
  #2  
Old 09-02-2021, 04:01 PM
Guessed's Avatar
Guessed Guessed is offline Help with a script to find Duplicate data in the same row of a table or tables Windows 10 Help with a script to find Duplicate data in the same row of a table or tables Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 2,638
Guessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to behold
Default

Do you have a sample document to test the macro on?
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #3  
Old 09-02-2021, 04:50 PM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help with a script to find Duplicate data in the same row of a table or tables Windows 10 Help with a script to find Duplicate data in the same row of a table or tables Office 2019
Competent Performer
Help with a script to find Duplicate data in the same row of a table or tables
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 123
Cendrinne is on a distinguished road
Default Hello, I might. I need to fix the doc first.

hang on a little. Sorry, Ive just finished a task, so let me try to fix that up for you. Thanks so much for any insights you may provide.
Reply With Quote
  #4  
Old 09-02-2021, 05:28 PM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help with a script to find Duplicate data in the same row of a table or tables Windows 10 Help with a script to find Duplicate data in the same row of a table or tables Office 2019
Competent Performer
Help with a script to find Duplicate data in the same row of a table or tables
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 123
Cendrinne is on a distinguished road
Default I'll log off here and try to use my iphone to send test doc

Quote:
Originally Posted by Guessed View Post
Do you have a sample document to test the macro on?
I'll be right back
Reply With Quote
  #5  
Old 09-02-2021, 05:35 PM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help with a script to find Duplicate data in the same row of a table or tables Windows 10 Help with a script to find Duplicate data in the same row of a table or tables Office 2019
Competent Performer
Help with a script to find Duplicate data in the same row of a table or tables
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 123
Cendrinne is on a distinguished road
Default

How do I send you a document without a link?
Reply With Quote
  #6  
Old 09-02-2021, 05:39 PM
Guessed's Avatar
Guessed Guessed is offline Help with a script to find Duplicate data in the same row of a table or tables Windows 10 Help with a script to find Duplicate data in the same row of a table or tables Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 2,638
Guessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to behold
Default

I'm going to assume that you have a regular table (no merged cells) and at least 2 columns and the code should be comparing the words in the first cell with all other cells on that row (like the original email). If these assumptions are correct then this modification to Paul's very elegant code should work.
Code:
Sub Demo()
  Application.ScreenUpdating = False
  Dim Tbl As Table, Rng As Range, strTxt As String, i As Long, j As Long
  Options.DefaultHighlightColorIndex = wdYellow
  For Each Tbl In ActiveDocument.Tables
    With Tbl
      For i = 1 To .Rows.Count
        Set Rng = .Rows(i).Range
        Rng.Start = .Cell(i, 2).Range.Start
        With .Cell(i, 1).Range
          For j = 1 To .Words.Count
            strTxt = Trim(.Words(j))
            With Rng.Find
              .ClearFormatting
              .Text = strTxt
              With .Replacement
                .ClearFormatting
                .Highlight = True
                .Text = "^&"
              End With
              .Format = True
              .Forward = True
              .MatchCase = False
              .MatchWholeWord = True
              .MatchWildcards = False
              .Wrap = wdFindStop
              .Execute Replace:=wdReplaceAll
            End With
          Next
        End With
      Next
    End With
  Next
  Application.ScreenUpdating = True
End Sub
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #7  
Old 09-02-2021, 05:42 PM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help with a script to find Duplicate data in the same row of a table or tables Windows 10 Help with a script to find Duplicate data in the same row of a table or tables Office 2019
Competent Performer
Help with a script to find Duplicate data in the same row of a table or tables
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 123
Cendrinne is on a distinguished road
Default OK Andrew, I'll test it right now and let you know :)

I hope one day, I'll get it, understand better. Well been better since day 1 in Word, 5 years ago. OK let me test it
Reply With Quote
  #8  
Old 09-02-2021, 05:56 PM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help with a script to find Duplicate data in the same row of a table or tables Windows 10 Help with a script to find Duplicate data in the same row of a table or tables Office 2019
Competent Performer
Help with a script to find Duplicate data in the same row of a table or tables
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 123
Cendrinne is on a distinguished road
Default

It didn't do anything. I've tried to reproduce a table here of 4 column. First 4 are supposed to be in ENG. Last 4 are supposed to be in French.
Now I've made up names with Lorem Ipsum texte. But it should give you an idea.

I would like to see highligted either what is similar, or different. I really don't mind. His original post was highlighted the 2nd column if they were similar.

Now below, I've did on purpose to make typo errors. So what ever you think it's better.
I hope if will show up nicely aligned once I press, Send.

Cendrinne


1 2 3 4 1 2 3 4
LOREM IPSUM [21.74%] LOREMA IPSUMO [83,74 %]
PORTTITOR [35.71%] PORTTITORI [37,52 %]
84,222,076 ELIT, 8% - 12% 302,648,222 304,626,822 84 222 076 ELITTE, 5 % - 17 % 302 648 222 304 626 822
99,333,586 AMET, 5% 22,228,886 22,228,886 99 333 586 AMETT, 3 % 22 228 886 22 228 886
824,263,446 806,302,222 824 263 446 806 302 222
Reply With Quote
  #9  
Old 09-02-2021, 05:58 PM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help with a script to find Duplicate data in the same row of a table or tables Windows 10 Help with a script to find Duplicate data in the same row of a table or tables Office 2019
Competent Performer
Help with a script to find Duplicate data in the same row of a table or tables
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 123
Cendrinne is on a distinguished road
Default

I'll redo it and put stars where it supposed to go to the next columns
Reply With Quote
  #10  
Old 09-02-2021, 06:09 PM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help with a script to find Duplicate data in the same row of a table or tables Windows 10 Help with a script to find Duplicate data in the same row of a table or tables Office 2019
Competent Performer
Help with a script to find Duplicate data in the same row of a table or tables
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 123
Cendrinne is on a distinguished road
Default

Ok if you do a Find and Replace, replace all ** for Tabs, you should get 9 columns, where the 5th one, is empty, it only separates ENG vs FR columns. I hope this is better.

1** 2** 3** 4** ** 1** 2** 3** 4
LOREM** IPSUM [21.74%]** ** ** ** LOREMA** IPSUMO [83,74 %]** ** xx
** PORTTITOR [35.71%]** ** ** ** ** PORTTITORI [37,52 %]** ** xx
84,222,076** ELIT, 8% - 12%** 302,648,222** 304,626,822** ** 84 222 076** ELITTE, 5 % - 17 %** 302 648 222** 304 626 822
99,333,586** AMET, 5%** 22,228,886** 22,228,886** ** 99 333 586** AMETT, 3 %** 22 228 886** 22 228 886
** ** 824,263,446** 806,302,222** ** ** ** 824 263 446** 806 302 222
Reply With Quote
  #11  
Old 09-02-2021, 06:19 PM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help with a script to find Duplicate data in the same row of a table or tables Windows 10 Help with a script to find Duplicate data in the same row of a table or tables Office 2019
Competent Performer
Help with a script to find Duplicate data in the same row of a table or tables
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 123
Cendrinne is on a distinguished road
Default

I've just reproduced it, put the fond such as Calibri at 7, then put a tab on each numbers 1 to 8 In Landscape.

I've removed the space before **, so now, for each ** = place a tab with the Find and Replace.

1**2**3**4****1**2**3**4
LOREM**IPSUM [21.74%]********LOREMA**IPSUMO [83,74 %]****xx
**PORTTITOR [35.71%]**********PORTTITORI [37,52 %]****xx
84,222,076**ELIT, 8% - 12%**302,648,222**304,626,822****84 222 076**ELITTE, 5 % - 17 %**302 648 222**304 626 822
99,333,586**AMET, 5%**22,228,886**22,228,886****99 333 586**AMETT, 3 %**22 228 886**22 228 886
****824,263,446**806,302,222********824 263 446**806 302 222
Reply With Quote
  #12  
Old 09-02-2021, 06:28 PM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help with a script to find Duplicate data in the same row of a table or tables Windows 10 Help with a script to find Duplicate data in the same row of a table or tables Office 2019
Competent Performer
Help with a script to find Duplicate data in the same row of a table or tables
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 123
Cendrinne is on a distinguished road
Red face Andrew, I'm so sorry, it did work!!!!!

Quote:
Originally Posted by Guessed View Post
I'm going to assume that you have a regular table (no merged cells) and at least 2 columns and the code should be comparing the words in the first cell with all other cells on that row (like the original email). If these assumptions are correct then this modification to Paul's very elegant code should work.
Code:
Sub Demo()
  Application.ScreenUpdating = False
  Dim Tbl As Table, Rng As Range, strTxt As String, i As Long, j As Long
  Options.DefaultHighlightColorIndex = wdYellow
  For Each Tbl In ActiveDocument.Tables
    With Tbl
      For i = 1 To .Rows.Count
        Set Rng = .Rows(i).Range
        Rng.Start = .Cell(i, 2).Range.Start
        With .Cell(i, 1).Range
          For j = 1 To .Words.Count
            strTxt = Trim(.Words(j))
            With Rng.Find
              .ClearFormatting
              .Text = strTxt
              With .Replacement
                .ClearFormatting
                .Highlight = True
                .Text = "^&"
              End With
              .Format = True
              .Forward = True
              .MatchCase = False
              .MatchWholeWord = True
              .MatchWildcards = False
              .Wrap = wdFindStop
              .Execute Replace:=wdReplaceAll
            End With
          Next
        End With
      Next
    End With
  Next
  Application.ScreenUpdating = True
End Sub
I'm so sorry, I forgotten that I had removed the option of showing highlights. Because others in the same document, uses a software where there's different colors of highlighted stuff. I get too confused, so I had removed it. I totally forgotten.

So you are right, it works.

I truly am sorry. You did an amazing job. I'll try to analyze it when I'll have more time.

again, humble appologies
Reply With Quote
  #13  
Old 09-02-2021, 06:39 PM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help with a script to find Duplicate data in the same row of a table or tables Windows 10 Help with a script to find Duplicate data in the same row of a table or tables Office 2019
Competent Performer
Help with a script to find Duplicate data in the same row of a table or tables
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 123
Cendrinne is on a distinguished road
Default

Wait, it half works.

on the 5th row, we see on column 3, 302,648,222 and under column 3 on the right side (or actual column8), 302, nor 648 isn't highlighted. Only 222 is highlighted.
Same on row 6 an 7th, with numbers 22,228,886 & 824,636,446. etc.

Why does it do half of it?

Unless it's not really made for many columns?

Cendrinne
Reply With Quote
  #14  
Old 09-02-2021, 07:55 PM
Guessed's Avatar
Guessed Guessed is offline Help with a script to find Duplicate data in the same row of a table or tables Windows 10 Help with a script to find Duplicate data in the same row of a table or tables Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 2,638
Guessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to beholdGuessed is a splendid one to behold
Default

Perhaps you haven't explained what you wanted the code to do.

The original compared the Words in the first column with the contents of the second cell. I changed it so it compares the Words in the first column with the contents of the rest of the row. It isn't comparing every word in every row to see if there is a duplicate somewhere.

Also, your sample code is predominantly numbers and the code is treating a number like 84,222,076 as five separate words (84 , 222 , 076). It then decides whether any of those five 'words' appear on the rest of the row.

If your requirements are for any repeats, anywhere in a row then a different methodology would work. Take a look at the macro I just did on another thread. That process could be adapted to work on numbers in a row instead of sentences in a document. https://www.msofficeforums.com/word/...sentenses.html
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #15  
Old 09-07-2021, 07:54 PM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help with a script to find Duplicate data in the same row of a table or tables Windows 10 Help with a script to find Duplicate data in the same row of a table or tables Office 2019
Competent Performer
Help with a script to find Duplicate data in the same row of a table or tables
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 123
Cendrinne is on a distinguished road
Default

Hello Andrew,
I'm wondering if my request is doable or not, bottom line, I need to compare two Tables or whole documents of Tables only but with different language format (ENGLISH vs FRENCH). All amounts should be alone in its cells. I'm trying to catch typo errors or amounts numbers that has NOT been updated/corrected.

I'm searching to compare the numbers only. Numbers are 95% of the time as of column 2.

This macro with the link I've provided in my initial request, uses two columns, and it highlights if the second columns have similar values to the first column on the same row, thus 222,512 (English) vs 222 512 (French), so the variables 222 & 512 were highlighted in the second column.

Maybe there's a better logical way to flag differences, that I don't know. Not sure how to go about doing this.

I can handle Find and Replace the punctuations (find all the following in a table: comas, non breaking space, period (for decimal) which could be flag with colors/highlights, I'm just new to understanding ''Duplicates'' or ''Differences'' with VBA.

Note, it will vary from table to table, so not always constant in each tables, and documents.

With my experience with VBA, I was thinking, I should trying to copy the English formatted numbers' columns unto a blank Word document, paste them on one side, and copy French formatted numbers on another side of a same table, while putting a column Gap to separate the two.

My thinking with my VBA programing experience, was to Flag/Highlight any variable on the same row (since I've copied English/French tables in the same table) that are either Similar, or is it better to flag if they are Different?
****However, as I'm thinking now, Maybe it would be better to Highlight cells Rows that are different. / to catch the errors.****

Is there a better way? Any insight would be appreciated.


Cendrinne
(I hope I wrote my question clearly?)
Reply With Quote
Reply

Tags
duplicate in tables, help please, highlight cells

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need Help to Script to align all the tables only as of a section to end of doc? Cendrinne Word VBA 4 04-05-2021 11:37 AM
Can Excel find Duplicate entries when only part of the cell's data is a duplicate of another cell? jsisley Excel 1 07-21-2017 09:20 AM
Help with a script to find Duplicate data in the same row of a table or tables VBA Table Search All Tables - Find & Replace Text in Table Cell With Specific Background Color jc491 Word VBA 8 09-30-2015 06:10 AM
Script starts nesting tables without reason selman555 Word VBA 1 10-17-2014 01:01 AM
Pivot Table Duplicate Data cnw Excel 0 08-31-2012 08:24 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:50 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft