Match Text string and Remove Duplicates

[Pete2020]

I thank each and every Mr. Excel forum members who are helping hundreds of Productivity Issues daily.

I am looking for a solution to remove similar duplicates by identifying the Pattern which every text ends with a Number.

Based on Column A (primary key) - the macro should run on column B data, by matching the similar text name ends with a Number.It should trim the extra spaces and delete the numbers and special characters around it.

Later macro should delete the Column B duplicates based on column A and results should be populated in column D and E.

Sample data

Book7
	A	B	C	D	E
1	ID	RAW DATA		Primary	Remove duplicate and Keep Unique
2	CD-001	Toggle Method - 1		CD-001	Toggle Method
3	CD-001	Toggle Method - 2		CD-002	Update Action -part
4	CD-002	Update Action - Part 1		CD-003	Add Form - Part
5	CD-002	Update Action - Part 2		CD-004	Code Implementation
6	CD-003	Add Form - Part (1)		CD-005	Create and run a while loop, part
7	CD-003	Add Form : Part (2)		CD-006	VBA Framework Lecture
8	CD-004	Code Implementation I		CD-007	VBA Project Video
9	CD-004	Code Implementation II		CD-008	VBA Command Video
10	CD-005	Create and run a while loop, part 1		CD-009	Player Setup P
11	CD-005	Create and run a while loop, part 2		CD-010	The registerNewUser method part
12	CD-006	VBA Framework Lecture:-1		CD-011	HTML Form @ Part
13	CD-006	VBA Framework Lecture:-2		CD-012	VBA Function
14	CD-007	VBA Project Video - i		CD-013	Let's create Macro Pt
15	CD-007	VBA Project Video- ii		CD-014	Source Code Lesson
16	CD-008	VBA Command Video1		CD-015	VBA Functions - Chapter
17	CD-008	VBA Command Video 2		CD-016	Inbuilt Time Function
18	CD-009	Player Setup P.1		CD-016	Inbuilt Date Functions
19	CD-009	Player Setup P.2		CD-016	Inbuilt Text Functions
20	CD-010	The registerNewUser method part1
21	CD-010	The registerNewUser method part2
22	CD-011	HTML Form @ part2
23	CD-011	HTML Form @ part3
24	CD-012	VBA Functions 01
25	CD-012	VBA Functions02
26	CD-013	Let's create Macro Pt.1
27	CD-013	Let's create Macro Pt.2
28	CD-014	Source Code Lesson 28
29	CD-014	Source Code Lesson 29
30	CD-015	VBA Functions - Chapter-1
31	CD-015	VBA Functions - Chapter-2
32	CD-016	Inbuilt Time Function
33	CD-016	Inbuilt Date Functions
34	CD-016	Inbuilt Text Functions
Sheet1

 

[Pete2020]

So is mine - that's why I asked for more samples of that and the expected results after removing the end bits and removing duplicates.

Red rows are just for observation whether macro eliminates the numbers at the end. Nothing much to focus on red colour.

After removing the numbers and it should remove duplicates on a combination of Column A and B

 

[Pete2020]

To try with a Small sample i giving you some data after reviewing many patterns in my raw data.

Few additional pattern were added from my initial sample data given in post #1.

Once done - Kindly apply the macro on sample which i sent in post#6.

Book3
	A	B
1	Course ID	Topics
2	ID-123	Technical Indicators - Part 1
3	ID-123	Technical Indicators - Part 2
4	ID-124	3 - Double entry bookkeeping I
5	ID-124	4 - Double entry bookkeeping II
6	ID-125	International Financial Reporting Standards-I
7	ID-125	International Financial Reporting Standards-II
8	ID-125	International Financial Reporting Standards-III
9	ID-126	CHAPTER 6,PART 1
10	ID-126	CHAPTER 6, PART 2
11	ID-126	CHAPTER 6, PART 3
12	ID-126	CHAPTER 6. PART 4
13	ID-126	CHAPTER 6, PART 5
14	ID-126	CHAPTER 7 PART 1
15	ID-126	CHAPTER 7 PART 2
16	ID-126	CHAPTER 7 PART 3
17	ID-126	CHAPTER 7 PART 4
18	ID-126	CHAPTER 7 PART 5
19	ID-126	CHAPTER 7 PART 6
20	ID-127	MULTI MODAL TRANSPORT 1
21	ID-127	MULTI MODAL TRANSPORT 2
22	ID-127	MULTI MODAL TRANSPORT 3
23	ID-127	MULTI MODAL TRANSPORT 4
24	ID-127	MULTI MODAL TRANSPORT 5
25	ID-128	Intangible Assets_3
26	ID-128	Intangible Assets_4
27	ID-129	AE-1
28	ID-129	AE-2
29	ID-130	Showme Problem #4
30	ID-130	Showme Problem #5
31	ID-130	Showme Problem #6
32	ID-131	Use QuickBooks File Q1
33	ID-131	Use QuickBooks File Q2
34	ID-132	Importing and Organizing Data in Python – part I
35	ID-132	Importing and Organizing Data in Python – part II
36	ID-132	Importing and Organizing Data in Python – part II
37	ID-132	Importing and Organizing Data in Python – part III
38	ID-133	Formations - 14 (a)
39	ID-133	Formations - 14 (b)
40	ID-133	Formations - 14 (c)
41	ID-133	Formations - 14 (d)
42	ID-133	Formations - 14 (e)
43	ID-133	Formations - 14 (f)
44	ID-133	Formations - 14 (g)
45	ID-133	Formations - 14 (h)
Sheet1

 

[Peter_SSs]

Could we have the expected results for post #12 sample data please?

 

[Pete2020]

Here is the Out Put.

ID-123 Technical Indicators - Part
ID-124 3 - Double entry bookkeeping
ID 125 International Financial Reporting Standards
ID 126 CHAPTER 6,PART
ID 126 CHAPTER 7 PART
ID 127 MULTI MODAL TRANSPORT
ID 128 Intangible Assets
ID 129 AE
ID-130 Showme Problem
ID 131 Use QuickBooks File
ID 132 Importing and Organizing Data in Python – part
ID 133 Formations

 

[Peter_SSs]

Thanks for that.

Sorry for all the questions but I am trying to avoid spending time developing a solution only to find "Oh, that was a mistake" or "That worked fine on that sample but with my real data it didn't work" etc.
Therefore I am trying to come up with a set of logical rules about what to remove at the end and it keeps getting trickier. So I have some more comments/questions that you might be able to clarify.

1. The earlier examples seemed to have removable endings that were either normal numbers or roman numerals and they were either by themselves or enclosed in parentheses, and any non-letter characters preceding. Now we also have (a), (b) etc following a number. Makes me wonder could there also be things like "abc - 14 (iv)" and, if so, what would the result be?

2. In the data below (from your samples and expected results) we have three different formations in column B (as noted in column C) but they all combine to one formation in column E (Expected Results). Is the original data correct? If so, how do we determine that all three of those format should combine into one (and which one?). Or is the sample data incorrect?

Pete2020 2020-05-09 1.xlsm
	A	B	C	D	E
9	ID-126	CHAPTER 6,PART 1	This has no space after the comma	ID 126	CHAPTER 6,PART
10	ID-126	CHAPTER 6, PART 2	This has a space after the comma
11	ID-126	CHAPTER 6, PART 3
12	ID-126	CHAPTER 6. PART 4	This has a period "." instead of a comma
13	ID-126	CHAPTER 6, PART 5
Sheet3

3. In the earlier data we had "HTML Form @ part2". All that was removed was the "2" at the end, not any letters beforehand. Now we have "Use QuickBooks File Q1" and this time not only the number at the end but the letter beforehand is also removed. What logic can we apply there, or is one of them wrong?

4. Given the above point about possible inconsistencies about what to remove, I am also interested in this example from your 'large data' (row 207): "How to navigate around MT4 (Metatrader 4)"
This has a number at the end, followed by a closing parenthesis, like cell B6 in post 1, so is anything removed from this one? If so, how much is removed & what is the logic?

5. In rows 4 & 5 below (from your sample data), the text before the roman numerals at the end is not the same in each row. How can that get combined into a single row as a duplicate?
If it does, then do rows 6 & 7 (my data) also get combined since they also have the same ID as each other with different text before the roman numerals?

Pete2020 2020-05-09 1.xlsm
	A	B	C	D	E
1	Course ID	Topics			Expected Results
4	ID-124	3 - Double entry bookkeeping I		ID-124	3 - Double entry bookkeeping
5	ID-124	4 - Double entry bookkeeping II
6	ID-999	Major Currency Pairs I		ID-999	????
7	ID-999	Forex market time frame II
Sheet3

 

[Pete2020]

Great Thinking. Yes, Your Questions Point in a right Way

1. The earlier examples seemed to have removable endings that were either normal numbers or roman numerals and they were either by themselves or enclosed in parentheses, and any non-letter characters preceding. Now we also have (a), (b) etc following a number. Makes me wonder could there also be things like "abc - 14 (iv)" and, if so, what would the result be?

Ans: Yes deleting that Number 14 (iv)

2. In the data below (from your samples and expected results) we have three different formations in column B (as noted in column C) but they all combine to one formation in column E (Expected Results). Is the original data correct? If so, how do we determine that all three of those format should combine into one (and which one?). Or is the sample data incorrect?

Ans: Its is from Actual data.
In this case, If last character is a number then remove special characters around it
After removing just compare word match by ignoring all special characters in rest of the cell, and show one amoung them based on most repetitive pattern
Your result on Column E is right

3. In the earlier data we had "HTML Form @ part2". All that was removed was the "2" at the end, not any letters beforehand. Now we have "Use QuickBooks File Q1" and this time not only the number at the end but the letter beforehand is also removed. What logic can we apply there, or is one of them wrong?

Ans: "HTML Form @ part2" - In this case part is a word with 4 characters and in case of Use QuickBooks File Q1 - Q is single character hence to me removed

4. Given the above point about possible inconsistencies about what to remove, I am also interested in this example from your 'large data' (row 207): "How to navigate around MT4 (Metatrader 4)"This has a number at the end, followed by a closing parenthesis, like cell B6 in post 1, so is anything removed from this one? If so, how much is removed & what is the logic

Ans Should not be removed - Retain because in closing parenthesis if it a consist only number then to be removed - Any word extended by number no deletion required .

5. In rows 4 & 5 below (from your sample data), the text before the roman numerals at the end is not the same in each row. How can that get combined into a single row as a duplicate? If it does, then do rows 6 & 7 (my data) also get combined since they also have the same ID as each other with different text before the roman numerals?

Ans Its not a duplicate - Its a mistake from my end -Just remove the Roman number at the end and it to out put column


Thank You for your close observation on the data.I am very much impressed the way to think and save your valuable time before actually start writing the Code.

 

[Peter_SSs]

The answer to point 2 does not make sense to me.
Firstly, can you precisely define what is a "special character"?
Secondly I cannot see how "CHAPTER 6,PART" fits the description of "most repetitive pattern" since there is one of those and three that say "CHAPTER 6, PART"
However, even if we decide that "CHAPTER 6, PART" is the most common in this example, what are we to do if there are 6 similar cells and
2 of them say "CHAPTER 6,PART"
2 of them say "CHAPTER 6, PART"
2 of them say "CHAPTER 6. PART"
That is, we have three equally most common variations??

In relation to point 5 you say "Its a mistake from my end "
What is the mistake?
a) Incorrect sample data (eg it shouldn't have "3- " and then "4 -") or
b) Incorrect expected results (eg there should be two result rows for that Course ID?

Just looking ahead, I'm not sure that I will have a workable solution for you as there seems to be just so many slight variations & imprecise 'rules'. Still, we will see when the latest answers arrive.

 

[Pete2020]

The answer to point 2 does not make sense to me.
Firstly, can you precisely define what is a "special character"?
Secondly I cannot see how "CHAPTER 6,PART" fits the description of "most repetitive pattern" since there is one of those and three that say "CHAPTER 6, PART"
However, even if we decide that "CHAPTER 6, PART" is the most common in this example, what are we to do if there are 6 similar cells and
2 of them say "CHAPTER 6,PART"
2 of them say "CHAPTER 6, PART"
2 of them say "CHAPTER 6. PART"
That is, we have three equally most common variations??

In relation to point 5 you say "Its a mistake from my end "
What is the mistake?
a) Incorrect sample data (eg it shouldn't have "3- " and then "4 -") or
b) Incorrect expected results (eg there should be two result rows for that Course ID?

Just looking ahead, I'm not sure that I will have a workable solution for you as there seems to be just so many slight variations & imprecise 'rules'. Still, we will see when the latest answers arrive.

1. Special character means Comma, ":"; "." "-","@" and most repetitive pattern means the macro trim extra spaces and and delete all special characters(Delimiters) and show the Only text. - Then Output will be " CHAPTER 6 PART"- Let it be simple

2. As we are dealing with removing numbers at the end of the cell and not the beginning of the cell. Hence output would as below.They are not duplicate to each other and no need to adjust the spaces and delimiters if they start at the beginning. From your words they should be Incorrect expected results (eg there should be two result rows for that Course ID?

OutPut
3- Double entry bookkeeping
4 -Double entry bookkeeping

Thank

 

[DanteAmor]

d = [ROW(1:100)] For i = 1 To UBound(d) d(i, 1) = WorksheetFunction.Roman(d(i, 1)) Next

My bad. Change ubound(b)
for ubound(d)

 

[Peter_SSs]

1. Special character means Comma, ":"; "." "-","@"

From your sample data and expected results, it look like "special characters" also includes "_" and "#"

See how this goes for both accuracy and speed. Report any erroneous results (with the incorrect result and the corrected result)
Assumption is that there are no more than 65,536 rows in the results, otherwise a further modification would be required.

Sub AggregateParts()
  Dim RX As Object, d As Object
  Dim a As Variant
  Dim i As Long
  
  Const Patt1 As String = "[\,:;\.\-@_#]"
  Const Patt2 As String = "([^a-z0-9][a-z])?((\d+|[civxl]+)|(\(\d+|[civxl]+\)))( *\([a-z]+\))?$"
  
  Set d = CreateObject("Scripting.Dictionary")
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.IgnoreCase = True
  a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    RX.Pattern = Patt1
    a(i, 2) = Application.Trim(RX.Replace(a(i, 2), " "))
    RX.Pattern = Patt2
    If RX.Test(a(i, 2)) Then a(i, 2) = RTrim(RX.Replace(a(i, 2), ""))
    d(a(i, 1) & ";" & a(i, 2)) = Empty
  Next i
  Application.ScreenUpdating = False
  With Range("D2").Resize(d.Count)
    .Value = Application.Transpose(d.Keys)
    .TextToColumns DataType:=xlDelimited, Semicolon:=True, Comma:=False, Space:=False, Other:=False
  End With
  Application.ScreenUpdating = True
End Sub

My sample data and results:

Pete2020 2020-05-09 1.xlsm
	A	B	C	D	E
1	Course ID	Topics
2	ID-123	Technical Indicators - Part 1		ID-123	Technical Indicators Part
3	ID-123	Technical Indicators - Part 2		ID-124	3 Double entry bookkeeping
4	ID-124	3 - Double entry bookkeeping I		ID-124	4 Double entry bookkeeping
5	ID-124	4 - Double entry bookkeeping II		ID-125	International Financial Reporting Standards
6	ID-125	International Financial Reporting Standards-I		ID-126	CHAPTER 6 PART
7	ID-125	International Financial Reporting Standards-II		ID-126	CHAPTER 7 PART
8	ID-125	International Financial Reporting Standards-III		ID-127	MULTI MODAL TRANSPORT
9	ID-126	CHAPTER 6,PART 1		ID-128	Intangible Assets
10	ID-126	CHAPTER 6, PART 2		ID-129	AE
11	ID-126	CHAPTER 6, PART 3		ID-130	Showme Problem
12	ID-126	CHAPTER 6. PART 4		ID-131	Use QuickBooks File
13	ID-126	CHAPTER 6, PART 5		ID-132	Importing and Organizing Data in Python – part
14	ID-126	CHAPTER 7 PART 1		ID-133	Formations
15	ID-126	CHAPTER 7 PART 2
16	ID-126	CHAPTER 7 PART 3
17	ID-126	CHAPTER 7 PART 4
18	ID-126	CHAPTER 7 PART 5
19	ID-126	CHAPTER 7 PART 6
20	ID-127	MULTI MODAL TRANSPORT 1
21	ID-127	MULTI MODAL TRANSPORT 2
22	ID-127	MULTI MODAL TRANSPORT 3
23	ID-127	MULTI MODAL TRANSPORT 4
24	ID-127	MULTI MODAL TRANSPORT 5
25	ID-128	Intangible Assets_3
26	ID-128	Intangible Assets_4
27	ID-129	AE-1
28	ID-129	AE-2
29	ID-130	Showme Problem #4
30	ID-130	Showme Problem #5
31	ID-130	Showme Problem #6
32	ID-131	Use QuickBooks File Q1
33	ID-131	Use QuickBooks File Q2
34	ID-132	Importing and Organizing Data in Python – part I
35	ID-132	Importing and Organizing Data in Python – part II
36	ID-132	Importing and Organizing Data in Python – part II
37	ID-132	Importing and Organizing Data in Python – part III
38	ID-133	Formations - 14 (a)
39	ID-133	Formations - 14 (b)
40	ID-133	Formations - 14 (c)
41	ID-133	Formations - 14 (d)
42	ID-133	Formations - 14 (e)
43	ID-133	Formations - 14 (f)
44	ID-133	Formations - 14 (g)
45	ID-133	Formations - 14 (h)
Sheet3

@DanteAmor
I encountered errors when running your code. Particularly this line y = Trim(Mid(c.Value, InStrRev(c.Value, " "), 5)) as some rows of the provided sample data (eg row 27 from post #12) do not contain a space character.