Match Text string and Remove Duplicates

P

Pete2020

Board Regular
Joined
Apr 25, 2020
Messages
68
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDE
1IDRAW DATA PrimaryRemove duplicate and Keep Unique
2CD-001Toggle Method - 1CD-001Toggle Method
3CD-001Toggle Method - 2CD-002Update Action -part
4CD-002Update Action - Part 1CD-003Add Form - Part
5CD-002Update Action - Part 2CD-004Code Implementation
6CD-003Add Form - Part (1)CD-005Create and run a while loop, part
7CD-003Add Form : Part (2)CD-006VBA Framework Lecture
8CD-004Code Implementation ICD-007VBA Project Video
9CD-004Code Implementation IICD-008VBA Command Video
10CD-005Create and run a while loop, part 1CD-009Player Setup P
11CD-005Create and run a while loop, part 2CD-010The registerNewUser method part
12CD-006VBA Framework Lecture:-1 CD-011HTML Form @ Part
13CD-006VBA Framework Lecture:-2 CD-012VBA Function
14CD-007VBA Project Video - iCD-013Let's create Macro Pt
15CD-007VBA Project Video- iiCD-014Source Code Lesson
16CD-008VBA Command Video1CD-015VBA Functions - Chapter
17CD-008VBA Command Video 2CD-016Inbuilt Time Function
18CD-009Player Setup P.1CD-016Inbuilt Date Functions
19CD-009Player Setup P.2CD-016Inbuilt Text Functions
20CD-010The registerNewUser method part1
21CD-010The registerNewUser method part2
22CD-011HTML Form @ part2
23CD-011HTML Form @ part3
24CD-012VBA Functions 01
25CD-012VBA Functions02
26CD-013Let's create Macro Pt.1
27CD-013Let's create Macro Pt.2
28CD-014Source Code Lesson 28
29CD-014Source Code Lesson 29
30CD-015VBA Functions - Chapter-1
31CD-015VBA Functions - Chapter-2
32CD-016Inbuilt Time Function
33CD-016Inbuilt Date Functions
34CD-016Inbuilt Text Functions
Sheet1
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Try the following macro, it works with all the examples you put in, if there is another pattern, then you can tell me and I modify the macro.

Put all the code in a module and run the Remove_Duplicates macro

VBA Code: 
Sub Remove_Duplicates()
  Dim dic As Object, c As Range, bCont As Boolean
  Dim x As Variant, b As Variant, y As String, m As String
  Dim i As Long, j As Long, lr As Long
  '
  lr = Range("B" & Rows.Count).End(3).Row
  ReDim b(1 To lr, 1 To 2)
  '
  Set dic = CreateObject("Scripting.Dictionary")
  For Each c In Range("B2:B" & lr)
    bCont = False
    For i = Len(c.Value) To 1 Step -1
      x = Mid(c.Value, i, 1)
      y = Trim(Mid(c.Value, InStrRev(c.Value, " "), 5))
      Select Case True
        Case IsNumeric(x), x = "-", x = ")", x = "(", x = " ", x = ":", x = "."
        Case bCont = False
          If IsRoman(y) Then
            i = InStrRev(c.Value, " ")
            bCont = True
          Else
            Exit For
          End If
        Case Else
          Exit For
      End Select
    Next
    m = Replace(Mid(c.Value, 1, i), ":", "-")
    If Not dic.exists(c.Offset(, -1).Value & "|" & m) Then
      dic(c.Offset(, -1).Value & "|" & m) = Empty
      j = j + 1
      b(j, 1) = c.Offset(, -1).Value
      b(j, 2) = m
    End If
  Next
  Range("D2").Resize(dic.Count, 2).Value = b
End Sub

Function IsRoman(sItem As String)
  Dim b As Variant, i As Long, m As Variant
  b = [ROW(1:100)]
  For i = 1 To UBound(b)
    b(i, 1) = WorksheetFunction.roman(b(i, 1))
  Next
  m = Application.Match(sItem, b, 0)
  If Not IsError(m) Then IsRoman = True
End Function
 
Upvote 0
Consider the following code, I changed it a bit, now it should be faster.

VBA Code: 
Sub Remove_Duplicates()
  Dim dic As Object, c As Range, bCont As Boolean, y As String, m As String
  Dim x As Variant, b As Variant, d As Variant, n As Variant
  Dim i As Long, j As Long, lr As Long
  '
  lr = Range("B" & Rows.Count).End(3).Row
  ReDim b(1 To lr, 1 To 2)
  'Load roman numbers
  d = [ROW(1:100)]
  For i = 1 To UBound(b)
    d(i, 1) = WorksheetFunction.Roman(d(i, 1))
  Next
  '
  Set dic = CreateObject("Scripting.Dictionary")
  For Each c In Range("B2:B" & lr)
    bCont = False
    For i = Len(c.Value) To 1 Step -1
      x = Mid(c.Value, i, 1)
      y = Trim(Mid(c.Value, InStrRev(c.Value, " "), 5))
      Select Case True
        Case IsNumeric(x), x = "-", x = ")", x = "(", x = " ", x = ":", x = "."
        Case bCont = False
          n = Application.Match(y, d, 0)
          If Not IsError(n) Then
            i = InStrRev(c.Value, " ")
            bCont = True
          Else
            Exit For
          End If
        Case Else
          Exit For
      End Select
    Next
    m = Replace(Mid(c.Value, 1, i), ":", "-")
    If Not dic.exists(c.Offset(, -1).Value & "|" & m) Then
      dic(c.Offset(, -1).Value & "|" & m) = Empty
      j = j + 1
      b(j, 1) = c.Offset(, -1).Value
      b(j, 2) = m
    End If
  Next
  Range("D2").Resize(dic.Count, 2).Value = b
End Sub
 
Upvote 0
Thank You so much @DanteAmor and Code is working on small sample

When i tried to run on 70k rows in column B it is showing the following error

dante.JPG
based on Column A match
 
Upvote 0
Can you give us the sample data with XL2BB so that we can copy it for testing?

Also, when reporting an error, please always give the full error message (as well as the line it occurred on).
 
Upvote 0
Thanks Peter

Sample Pattern.xlsx
AB
1Primary Coursetopics
2Course 1Introduction and Overview
3Course 1Introduction and Overview
4Course 1Money Management
5Course 1Candlestick Analysis
6Course 1Technical Indicators - Part 1
7Course 1Technical Indicators - Part 2
8Course 1Market Structure
9Course 1Trade Entries, Stops and Targets - Part 1
10Course 1Trade Entries, Stops and Targets - Part 2
11Course 1Identify and Prepare for Trading Opportunities
12Course 1The Psychology of Trading
13Course 1ECO11: Principles of Economics I
14Course 1Economic Goods,Value, Utility, and Marginal Analysis
15Course 1Human Action
16Course 1Time, Capital, and Technology
17Course 1Economic Exchange
18Course 1Indirect Exchange
19Course 1Prices
20Course 1The Market Order
21Course 1Profit and Loss
22Course 1Violent Intervention
23Course 1Economic Progress
24Course 1ECO21: The Bitcoin Standard
25Course 1Money
26Course 1Primitive Money
27Course 1Monetary Metals
28Course 1Government Money
29Course 1Money and Time Preference
30Course 1Capitalism's Information System
31Course 1Sound Money & Individual Freedom
32Course 1Digital Money
33Course 1What Is Bitcoin Good For?
34Course 1Bitcoin Questions
35Course 1Concept
36Course 1Trend Strategies
37Course 1Range Strategies
38Course 1Breakout Strategies
39Course 1Know Your Currencies
40Course 1Trading the News
41Course 1What The Broker Don't Tell You
42Course 1Trading Psychology
43Course 1Market Structure
44Course 1Goal Setting and Trading Plan
45Course 1Live Trading II
46Course 1Live Trading I
47Course 1BPP F3 FA Revision Kit
48Course 1The context and purpose of financial reporting
49Course 12 - The qualitative characteristics of financial information
50Course 13 - Double entry bookkeeping I
51Course 14 - Double entry bookkeeping II
52Course 15 - Sales tax
53Course 16 - Inventory
54Course 17 - Tangible non-current assets I
55Course 18 - Tangible non-current assets II
56Course 19 - Intangible non-current assets
57Course 110 - Accruals and prepayments
58Course 111 - Receivables and payables
59Course 112 - Provisions and contingencies
60Course 113 - Capital structure and finance costs
61Course 114 - Trial Balance (15 Mark question)
62Course 1Understanding Financial Accounting
63Course 1Understanding Financial Statements
64Course 1Journal and Ledger
65Course 1Preparing Profit and Loss Account and Balance Sheet from Trial Balance
66Course 1Illustration- Cash Flow Statement -1
67Course 1Illustration- Cash Flow Statement -2
68Course 1Analysing financials of Starbucks Corp
69Course 1Analysis of Financial Statement_Common Size Statements_Ratio Analysis
70Course 1Case Study_Ratio Analysis
71Course 1Management Accounting
72Course 1Budgeting
73Course 1Case Study _Break-even Analysis
74Course 1Investment Appraisal | NPV | IRR | Payback Period
75Course 1Pricing for Profit
76Course 1Working Capital Management
77Course 1Getting started
78Course 1Week 1
79Course 1Week 2
80Course 1Week 3
81Course 1Week 4
82Course 1week 5
83Course 1week 6
84Course 1week 7
85Course 1week 8
86Course 1week 9
87Course 1week 10
88Course 1week 11
89Course 1week 12
90Course 1week 13
91Course 1week 14
92Course 1Introduction to Forex
93Course 1The Importance of a Trading Plan
94Course 1Risk: What is it?
95Course 1Price Action & Supply and Demand
96Course 1Trading Support and Resistance
97Course 1Technical Indicators
98Course 1Candlesticks
99Course 1Intermediate Risk Management
100Course 1Framing The Market
101Course 1Chart Patterns
102Course 1Advanced Technicals
103Course 1Index Trading
104Course 1Fundamentals
105Course 1Breakout Trading
106Course 1Bad Habits
107Course 1Introduction to Forex trading
108Course 1Introduction to Meta Trader 4
109Course 1Candlesticks and special candlestick patterns
110Course 1Market Structure
111Course 1Support and Resistance
112Course 1Trendlines and Channels
113Course 1Fibonacci Trading
114Course 1Quarters theory trading
115Course 1Breakout trading
116Course 1Chart Patterns
117Course 1Supply and Demand trading
118Course 1The Awesome Oscillator
119Course 1Divergence
120Course 1Harmonic Pattern Trading
121Course 1Multiple time frame analysis
122Course 1Camarilla Pivot Points
123Course 1Money management and trading psychology
124Course 1Business organisations and their stakeholders
125Course 1The business environment
126Course 1The macro-economic environment
127Course 1Micro-economic factors
128Course 1Business organsation, structure and strategy
129Course 1Organisational culture and committees
130Course 1Corporate governance and social responsibility
131Course 1The role of accounting
132Course 1Control, security and audit
133Course 1Identifying and preventing fraud
134Course 1Leading and managing people
135Course 1Recruitment and selection
136Course 1Diversity and equal opportunities
137Course 1Individuals, groups and teams
138Course 1Motivating individuals and groups
139Course 1Training and development
140Course 1Performance appraisal
141Course 1Personal effectiveness and communication
142Course 1Ethical considersations
143Course 1Quiz
144Course 120 questions
145Course 1Audit and other assurance engagements
146Course 1Statutory audit and regulation
147Course 1Corporate governance
148Course 1Ethics
149Course 1Internal audit
150Course 1Risk assessment
151Course 1Audit planning and documentation
152Course 1Introduction to audit evidence
153Course 1Internal control
154Course 1Tests of control
155Course 1Audit procedures and sampling
156Course 1Non-current assets
157Course 1Inventory
158Course 1Receivables
159Course 1Cash and bank
160Course 1Liabilities, capital and directors' emoluments
161Course 1Not-for-profit organisations
162Course 1Review and reporting
163Course 1Reports
164Course 120 Question quiz
165Course 120 questions
166Course 1Complete course of Consignment accounting and problem solving.
167Course 1What is consignment? Introduction to consignment
168Course 1Basic Terminologies of Consignment | Consignment Accounting
169Course 1Types of Commissions on Consignment Sales | Consignment Accounting
170Course 1Difference between Consignment and Sales | Consignment Accounting
171Course 1Consignment Accounting Cycle | Consignment Accounting
172Course 1Basic Journal Entries | Consignment Accounting
173Course 1Advance Journal Entries | Consignment Accounting
174Course 1Journal Entries of Del Credere Commission | Consignment Accounting
175Course 1Valuation of Stock on Consignment | Consignment Accounting
176Course 1Valuation Of Goods-in-Transit stock | Consignment Accounting
177Course 1Consignment Accounting | Basic Journal Entries
178Course 1Consignment Accounting | Problem Solving | Ledger Question
179Course 1Consignment Accounting Problem Solving
180Course 1Goods Sent at Invoice Price on Consignment | Journal Entries
181Course 1Consignment Accounting Problem Solving
182Course 1Consignment Accounting Problem Solving
183Course 1Consignment Accounting | Problem Solving
184Course 1Consignment Accounting | Problem Solving
185Course 1Advance Received on Consignment as Security Money | Consignment
186Course 1Mistakes to Avoid in Consignment Accounting | Tips & Tricks of Consignment
187Course 1What is Forex?
188Course 1Major Currency Pairs
189Course 1What is Leverage?
190Course 1Forex market time frame
191Course 1How to read a Forex quote
192Course 1Long vs Short; Bid & Ask price
193Course 1Market orders
194Course 1Pips and Pipettes
195Course 1Types of Market Analysis
196Course 1Types of charts
197Course 1Basic candlestick patterns
198Course 1Market trend types
199Course 1Support and Resistance
200Course 1Forex indicators
201Course 1MACD: Moving Average Convergence Divergence
202Course 1Moving Average
203Course 1Stochastics Oscillator
204Course 1RSI (Relative Strength Index)
205Course 1Trading Systems
206Course 1Create demo account
207Course 1How to navigate around MT4 (Metatrader 4)
208Course 1How to place market orders
209Course 1How to Calculate pips
210Course 1TOFS Fundamental Methodology
211Course 1TOFS MA Strategy
212Course 1Bonus - Live trades on Silver
213Course 1Bonus - Live trades on GbpAud
214Course 1Bonus - Live trades on EurAud
215Course 1Accounting and Financial Analysis
216Course 1Definition,Objectives,Functions & Nature of Accounting
217Course 1Branches of Accounting,Merits & Demerits of Accounting
218Course 1Orientation in Accounting
219Course 1Accounting Principles
220Course 1Accounting Concepts
221Course 1Accounting Conventions
222Course 1Double Entry System
223Course 1Traditional & Modern Accounting System
224Course 1Journal & Ledger Posting
225Course 1Cash Book
226Course 1Preparation of Trial Balance
227Course 1Preparation of Manufacturing & Trading Account
228Course 1Preparation of Profit & Loss Account
229Course 1Preparation of Balance Sheet Under Sole Proprietorship
230Course 1Adjustment Entries
231Course 1Form of Company's Profit & Loss Account
232Course 1Form of Balance Sheet of Companies
233Course 1Segment Reporting
234Course 1International Financial Reporting Standards-I
235Course 1International Financial Reporting Standards-II
236Course 1International Financial Reporting Standards-III
237Course 1Meaning,Nature & Types of Financial Statements
238Course 1Significance & Limitations Of Financial Statements
239Course 1Concept of Financial Analysis
240Course 1Vertical VS Horizontal & Internal VS External Financial Analysis
241Course 1Accounting Ratios
242Course 1Computation & Interpretation of Liquidity & Turnover Ratios
243Course 1Long Term Financial Analysis & Capital Structure Ratios
244Course 1General & Overall Profitability Ratios
245Course 1Fund Flow Statement
246Course 1Sources and Application of Funds
247Course 1Cash Flow Statement
248Course 1Valuation of Shares
249Course 1Methods of Valuation Of Shares
250Course 1Goodwill
251Course 1Valuation of Goodwill
252Course 1Valuation Of Inventory
253Course 1FIFO and LIFO
254Course 1Approaches to Price Level
255Course 1Forensic Accounting,Money Laundering,Financial Intelligence
256Course 1Accounting Simplified
257Course 1App Installation
258Course 13 App Overview
259Course 14 App Settings
260Course 15 Money Capital Invested
261Course 16 Income Classification
262Course 17 Cash Sales & Refund
263Course 18 Expense Classification
264Course 19 Cash Expense & Refund
265Course 110 Dashboard - Overview
266Course 111 Transport Refund
267Course 112 Cash Deposit & Withdraw
268Course 113 Personal Cash from Business
269Course 114 Disallowed Expenses
270Course 115 Cash & Traditional Accounting
271Course 116 Laptop Purchase
272Course 117 Opening Balances
273Course 118 Creditor Opening Balances
274Course 119 Creditor Invoice & Payment
275Course 120 Creditors Facilities
276Course 121 Debtors Facilities
277Course 122 Cash Register
278Course 123 Bank Statement & Reconciliation
279Course 124 Review Bank Reconciliation
280Course 125 Previous Year Tax
281Course 126 Closing Stock
282Course 127 Documents & Basics
283Course 128 Chart of Accounts
284Course 129 Correcting Procedure
285Course 130 Backup & Restore
286Course 131 Final Trial Balance
287Course 132 Previous Year Comparison
288Course 133 Accounting Cycle
289Course 134 Profit and Loss Account plus Notes
290Course 135 Tax and Income Calculations
291Course 136 Tax Recording
292Course 137 Balance Sheet plus Notes
293Course 138 Incomes, Expenses and Equity Reports
294Course 139 Assets and Liability Reports
295Course 140 Printing & Naming Reports
296Course 141 Course Evaluation
297Course 1Chapter on History of Letter of Credit
298Course 1CHAPTER 4, ARTICLE 1 &2
299Course 1How a Letter of Credit works
300Course 1CHAPTER 4,COMPLETE WITH ARTICLE 2
301Course 1CHAPTER 6,PART 1
302Course 1CHAPTER 6, PART 2
303Course 1CHAPTER 6, PART 3
304Course 1CHAPTER 6. PART 4
305Course 1CHAPTER 6, PART 5
306Course 1CHAPTER 7 PART 1
307Course 1CHAPTER 7 PART 2
308Course 1CHAPTER 7 PART 3
309Course 1CHAPTER 7 PART 4
310Course 1CHAPTER 7 PART 5
311Course 1CHAPTER 7 PART 6
312Course 1CHAPTER 8 AMENDMENTS
313Course 1CHAPTER 9 PART 1
314Course 1CHAPTER 9 PART 2
315Course 1CHAPTER 11 AMENDMENTS
316Course 1CHAPTER 13
317Course 1CHAPTER 17 PART 1
318Course 1CHAPTER 17 PART 2
319Course 1CHAPTER 17 PART 3
320Course 1CHAPTER 18
321Course 1CHAPTER 19
322Course 1CHAPTER 20
323Course 1CHAPTER 22
324Course 1MULTI MODAL TRANSPORT 1
325Course 1MULTI MODAL TRANSPORT 2
326Course 1MULTI MODAL TRANSPORT 3
327Course 1MULTI MODAL TRANSPORT 4
328Course 1MULTI MODAL TRANSPORT 5
329Course 1BILL OF LADING
330Course 1CHARTER PARTY BILL OF LADING
331Course 1AIR TRANSPORT
332Course 1ROAD TRANSPORT
333Course 1COURIER, POST AND CERTIFICATE OF POSTING
334Course 1BILL OF EXCHANGE PART 1
335Course 1BILL OF EXCHANGE PART 2
336Course 1INSURANCE
337Course 1COMMERCIAL DOCUMENT
338Course 1CERTIFICATE OF ORIGIN
339Course 1INCO TERMS
340Course 1E UCP
341Course 1OVER RIDING CONSIDERATIONS
342Course 1DISPUTE RESOLUTION
343Course 1MULTI MODAL TRANSPORT 6
344Course 1MULTI MODAL TRANSPORT 7
345Course 1The 5 Keys to Money and Values
346Course 1I Don’t Believe in Budgets
347Course 1The Importance of Building an Emergency Account
348Course 1Prioritizing Your Wants, Desires and Living Within Your Means
349Course 1The Ten Commandments of Couple Communication With Money
350Course 1How to Buy a Car
351Course 1How to Select a Mortgage Consultant
352Course 1How to Buy a Home With Caralee Gurney
353Course 17 Areas of Spending You Should Audit for Financial Waste
354Course 1How to Select a Financial Advisor
355Course 1A Key Quality in Selecting a Financial Planner
356Course 1How to Trust a Financial Advisor
357Course 1Why You are Financially Stressed and What You Can Do About It
358Course 1Introduction to Set Expectations
359Course 1Debt Free Mindset and Developing New Habits
360Course 1Habits to Get You Out of Debt
361Course 1The Abusive Secrets of the Credit Card Industry
362Course 1Getting Control of Your Spending - The Boundary System
363Course 1How to Improve Your Credit Score
364Course 1Getting Organized With Your Debt Inventory
365Course 1The Debt Solution Deception
366Course 1Creating Your Own Debt Consolidation Plan
367Course 1Debt Collector Threats, Harassment, and How to Fight Back
368Course 1Surviving and Beating the Perfect Debt Storm – The 5 Categories of Debt
369Course 1Dealing With Student Loan Debt
370Course 1Strategies for Lowering Your Interest Rates and Getting Out Of Debt
371Course 1Debt - Summing it All Up
372Course 1The Dangers of Pop Culture Finance
373Course 1How the Stock Market Really Works
374Course 1Why Your Advisor Probably Doesn't Understand Your Risk Level
375Course 1Saving More Money Won't Get You to Retirement
376Course 1How to Be a Quadrant II Investor
377Course 1Is Investing in a 401k Plan Always a Good Idea
378Course 1The 10 Don’ts of 401 K Investing
379Course 1How to Invest Your 401k Plan
380Course 1The Secrets of Selecting a Good Mutual Fund
381Course 1How to Invest in a Declining Stock Market
382Course 1The Risks in Investing in Bonds and Funds
383Course 1Navigating Through the Annuity Mind Field
384Course 1The Coming Collapse of the Economy and the Dollar
385Course 1Young Adults Guide: Prioritize Your Wants, Desires & Live Within Your Means
386Course 111 Pieces Of Wisdom I Wish That I Knew When I Was In My 20s
387Course 110 Steps to Take 5 Years Out From Financial Independence (Retirement)
388Course 1What They Don’t Tell You About Life Insurance
389Course 1Estate Planning 101
390Course 1Creative Ways to Create a Long-Term Care Plan
391Course 1Protect Yourself From Identity Theft When (Not If) It Happens
392Course 1Insider Secrets of The IRS
393Course 1How to Win a Tax Audit With Dan Pilla
394Course 1Bonus Video - Q and A of Most Frequently Asked Questions
395Course 1Contenido
396Course 1What is the difference between BITCOIN vs FIAT currency vs Gold
397Course 1How to get debit card VISA for bitcoin and pay anywhere
398Course 1Wallet for almost all crypto currencies bitcoin etc Win/mac/ios/Android
399Course 1How to solve problem with the credit card to buy bitcoins
400Course 1Best 3 websites to buy cryptocurrency bitcoin etc
401Course 1Best places to buy bitcoin online or with paypal or cash or gift cards
402Course 1Mining with a PC VS a mining with an antminer
403Course 1Is it worth it to mine with your cellphone?
404Course 1Is it worth it to buy an used bitcoin miner or any other crypto miner
405Course 1Is Egearbit.com a scam? Bitman has no resellers
406Course 1Is pandaminer a scam? How to avoid seller that are scammer in miners
407Course 1Relation between speed and cost of bitcoin transaction
408Course 1Guide and precaution Antminer installation
409Course 1How to setup an antminer for beginners step by step (super tutorial)
410Course 1Alternative HP power supplies for Antminer Bitmain
411Course 1How to fix blinking RED LED in antminer - Troubleshooting
412Course 1Looking inside of ibelink dm22g for dash
413Course 1What are the differences in price Genesis mining Hash Flare Bitcoin pool
414Course 1Unboxing ibelink dm22g ghs for dash mining
415Course 1Unboxing introduction antminer s9 for bitcoin 14th
416Course 1What differences between antminer pool amateur vs profesional
417Course 1How to mine with f2pool with dash antminer d3
418Course 1How to upgrade any antminer S9 L3 D3 etc
419Course 1Review of f2pool for dash antminer d3
420Course 1Is it worth it to buy at outrageous prices antminers or mine in the Cloud
421Course 1How to fix error socket connect failed antminer
422Course 1Secure pool vs unsecure pools for mining crypto currencies
423Course 1Can you mine altcoin with your cellphone?
424Course 1Best places to buy antminers in USA
425Course 1How much $$ can you earn with minergate to mine altcoins
426Course 1How much can you make antminer s9 December 2017
427Course 1How to setup a pool in an antminer L3 Bitmain for Litecoin
428Course 1How to fix heat sink of any antminer S9 L3 D3
429Course 1How to replace fan's power supply antminer Bitmain
430Course 1How to replace the antminer board controller
431Course 1How to exchange crypto currency with your cellphone in few steps
432Course 1Are Antminer power supply any good?
433Course 1How to tell if the vendor is a scammer of antminers bitcoin
434Course 1Let's see Antminer with water damage or environment with high humidity
435Course 1110v or 220v for antminer best option to savee electricity
436Course 1How to connect all the antminer to internet mini bitcoin farm
437Course 1Is Halong mining Dragon Mint a SCAM?
438Course 1What is a crypto wallet? How to use it? Tutorial
439Course 1Power supply Bitmain vs Generic Chinese brand - Power consumption testing
440Course 13 reasons why you should NOT buy antminer A3
441Course 1Comparison Whatsminer vs Bitmain Which one is the best miner
442Course 1Unboxing antminert9 miner for Bitcoin algorithm sha 256
443Course 1Super motherboard for 19 GPU ideal for mining
444Course 1How to purchase in Bitmain Antminer step by step with advice inclused
445Course 1Super compact Fire Extinguisher for miners computers and electronics
446Course 1Difference between mining in the cloud vs mining on my own
447Course 1Rig with raisers vs raiserless for mining (motherboard octominer)
448Course 1Maintenance & temperature of miners antminer
449Course 1Baikal comparisson with antminer
450Course 1How to mount in an antminer a self for PSU (power supply Bitmain)
451Course 1Computer case enclosed & PSU octominer for Riserless mining
452Course 1How to build a profesional RIG enclosed case + ETHOS tutorial
453Course 1How to build a riserless rig with enclosed computer case 8 GPU
454Course 1How to earn $1000 daily in Bitcoin Scam exposed
455Course 1How to setup Baikal bk x to mine multiple coins & unboxing
456Course 1Big mistakes sending eth RIGS via USPS or Fedex
457Course 1Asset Accounting Course Simplified
458Course 1Overview of Asset Accounting
459Course 1Lecture 2 - on Asset Accounting
460Course 1Lecture 3 - Depreciation and Its Method of Calculation.
461Course 1Lecture 4- on Asset Accounting ( Refer Description for content )
462Course 1Lecture 5 - On Asset Under Construction
463Course 1Lecture 6- Configuration (SPRO) Setup
464Course 1Lecture 7-Period and Year End Activities for Asset Accounting
465Course 1Lecture 8- Asset Data Transfer
466Course 1Multi tax - Corporate tax, VAT and SDLT
467Course 1Corporate tax - Liquidation
468Course 1Corporate tax - PE incorporation, De-grouping charges, SSE
469Course 1Corporate tax - Anti-avoidance: CFCs, DPT, Transfer pricing
470Course 1Corporate tax - PE v Sub; Personal tax - Residency; Business Structuring
471Course 1Business Restructuring - Incorporation
472Course 1Personal taxes -CGT, IHT and Trusts
473Course 1Personal taxes - Residency
474Course 1Financial Accounting and Reporting
475Course 1Single Entity Accounts
476Course 1Explain Accounting Treatment
477Course 1Cash Flow Statement
478Course 1Groups: Income Statement and SFP extracts
479Course 1Groups SFP 1
480Course 1Groups SFP 2
481Course 1Groups: IS and SOCE
482Course 1Cash Flow Statement 2
483Course 1IFRS Technical Tuition and Explain Accounting Treatment (Assets and Leases)
484Course 1Background of Accounting
485Course 1INTRODUCTION TO THE COURSE
486Course 1SIGNIFICANCE OF BUSINESS AND ACCOUNTING
487Course 1THE SEPARATE BUSINESS ENTITY
488Course 1THE BUSINESS EQUATION AND ACCOUNT TITLES
489Course 1TRANSACTIONAL ANALYSIS - EFFECTS OF TRANSACTIONS TO THE BUSINESS EQUATION
490Course 1Lecture 5; iLLUSTRATIONS OF VALUE RECEIVED AND VALUE PARTED WITH
491Course 1Stages of Accounting
492Course 1FIRST PHASE IN ACCOUNTING - THE JOURNAL ENTRY
493Course 1SECOND STAGE IN ACCOUNTING - POSTINGS IN THE LEDGER
494Course 1THIRD STAGE IN ACCOUNTING - TRIAL BALANCE
495Course 1FOURTH STAGE IN ACCTG - INTERPRETING BUSINESS RESULTS AND FINANCIAL CONDITION
496Course 1PDF - 10 Introduction
497Course 110 Partnerships Introduction
498Course 1Accounting Comic Break
499Course 1Set up New Partnership
500Course 12 Set up New Partnership
Sheet1
 
Upvote 0
That is bigger than necessary. In future try to make you samples big enough to convey any variety but not enormous.

Speaking of variety, is that data representative? Every single row has "Course 1" in column A :cautious:

Column B is very different to the initial data style and does not have many examples of the variety of numbers/roman numeral styles at the end & I think they will be where the difficulties lie.

Any chance you could do another set of say 30-40 rows, taking into account what I have just said, and also include the expected results in columns D:E like you did initially?
 
Upvote 0
Yes I modified the Column A to Course 1. Its just like any other Number usually Course ID-0001

My worry is on Column B, any text which ends with Number and any special characters around it to be removed.

But when we do that around 2-5% would go wrong deletion never mind- but rest of the 95% number extensions is merry a scrap and duplicated with text like Part 1,2 or example 1 and 2 etc .
 
Upvote 0
BTW, was there anything special about the red rows in that last sample?
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Comparing two excel files for matching / mismatching rows (long text strings - match() wont work
Replies
4
Views
999
ahaye
A
D
  • Question Question
Issue with EntireRow.Delete
2
Replies
11
Views
1K
RoryA
RoryA
P
  • Question Question
VBA to perform Text Diff (but with >2500 Char per cell)
Replies
0
Views
294
potatoooooooo
P
I
  • Question Question
Compare all rows with the same A$, then, for those row results, compare all L$, if all the L$ match, then "Completed"
Replies
1
Views
421
IfAndOrWhatAmIDoing
I
T
  • Question Question
Merge duplicate and similar rows into new sheet
Replies
0
Views
706
tjdickinson
T

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top