vba to find specific combination of chars in one column

gint32

Board Regular
Joined
Oct 8, 2014
Messages
139
Hi all
I have a column that contains free text, I need a way (using vba) to find specific chars in this column the format of the text chars will always be like .. one Alpha and 7 numeric(together).....Examples of this is are D1564567, A1235567, all the way through to Z2356457 and if and when found copy this alpha numeric to he adjacent cell on the right. Not all cells will have this so it needs to e able to skip over records that do not contain, any help appreciated
 
Applogies . The red should not have been omited.
I just was on a 12hrs shift today and inadvertently missed this one..sorry i am not In front of a computer much at work.

B22451610 yep only needs to appear once..
 
Last edited:
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Thanks for the clarifications.

Try this slightly modified version.

Code:
Sub Find_Matches_v2()
  Dim RX As Object, d As Object
  Dim a As Variant, m As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  With Sheets("Workbook 1")
    RX.Pattern = "(" & Join(Application.Transpose(.Range("A2", .Range("A2").End(xlDown))), "|") & ")(?=\D|$)"
  End With
  With Sheets("Workbook 2")
    a = Application.Index(.Cells, Evaluate("row(2:" & .Range("A" & .Rows.Count).End(xlUp).Row & ")"), Array(1, 6))
  End With
  For i = 1 To UBound(a)
    For Each m In RX.Execute(a(i, 2))
      d(a(i, 1) & ";" & m) = Empty
    Next m
  Next i
  Application.ScreenUpdating = False
  Sheets.Add After:=Sheets(Sheets.Count)
  With Sheets(Sheets.Count)
    With .Range("A2").Resize(d.Count)
      .Value = Application.Transpose(d.Keys)
      .TextToColumns DataType:=xlDelimited, Semicolon:=True, Other:=False
    End With
  End With
  Application.ScreenUpdating = True
End Sub
 
Upvote 0
Try and visualise workbook 1 as a nominees to come to a future venue that your are trying to orginase for your employees and then also visusluse workbook2 has all the empolyees who have had or have issues with other employees 《people》 or multiple people(this is in the free text...) ..and then i adk you to have a go and try and organise an upcoming venue without inviting employees together on the same day who have issues (so meaning ..if one employee has an issue with another. Then I dont need to be made aware of this 3times as once is enough ) ..workbook2 holds all (on every employee. But since not evetyone is invited as the venue room is to small) and in any case were only interested in the ones within workbook1 since these are the only one's the invites are being sent too....so we only need to evaluate on this.workbook1...
 
Last edited:
Upvote 0
Thanks for the clarifications.

Try this slightly modified version.

End Sub[/code]

Try and visualise workbook 1(unchecked for issues) as employees id’s that your boss’s wishes to come to a future venue that he’s tasked you to organise, then picture Workbook2 as holding all of the company’s employees id’s who have ever had or still have issues with other employees id’s or multiple employees id’s (the multiple peoples Id’s are all stuck within the free text along with the details of why’s and who’s) unchecked of course for issues.
How would you set about sending out the invitations?
More importantly, how could you be 100 % confident that within the small list of employees(Workbook1-list) that your boss gave you, that nobody on this list has an issue with another employee on that same list (Workbook1-list)?
The only way to know is to go through workbook2 and filter for only those on the small list employees(Workbook1-list), and see if then go through the free text of the filtered and check if they are also on the invites.
Manually, well that would be fine if it were 100 or 200 being asked to the event, but not practicle.
Take the following scenario:
John has an issue with more than one person and the people he has these issues with are all in that “ one row of data”True”! well we need the id’s of these people, but only if they are on the small list
John = A1234567 who has issues with the following .Andy B1234567 .Mike C1234567. Tam D1234567(freetext)
Your code still seems to only get John = A1234567 <>with Andy B123456 and does not get John = A1234567 <>with Mike C1234567 or / and John = A1234567 <> with Tam D1234567
Remember John has an issue with all three people(ids) and these people are also on the small list(invitee's) who need to be rescheduled to a later date(some other event where john will not be at)
Does this make sense. So its works for the first hit but not the rest
So at the event, Mike and Tam will beat the crap out of John! As we never picked that up.
 
Upvote 0
Try and visualise
So at the event, Mike and Tam will beat the crap out of John! As we never picked that up.
In Post 44,the scenario is actually the other way round, as its John that has the issue with Mike and Tam. So John would beat the crap out of Mike And Tam!
as Mike and Tam don't have an issue with John.
so in essence as shown in post #39..the output would look like
[TABLE="class: cms_table"]
<tbody>[TR]
[TD="bgcolor: #D9E1F2"]G1234567[/TD]
[TD="bgcolor: #D9E1F2"]E1367986[/TD]
[/TR]
[TR]
[TD="bgcolor: #D9E1F2"]G1234567[/TD]
[TD="bgcolor: #B4C6E7"]B2451610[/TD]
[/TR]
</tbody>[/TABLE]

If G1234567(John) had an issue with two people (Mike = E1367986 & Tam = 2451610).
 
Upvote 0
Your code still seems to only get John = A1234567 <>with Andy B123456 and does not get John = A1234567 <>with Mike C1234567 or / and John = A1234567 <> with Tam D1234567
I am not getting that issue, using the code from post 42, see below.
I am getting the issue of a conflict being repeated but only if it is reported both ways in Workbook 2. For example, the John/Andy is reported both ways because of the two yellow rows in Workbook 2 but the other conflicks with John are only reported once because they are only recorded one-way in Workbook 2.

The double-up of the John/Andy conflict in the report sheet will be easily fixed if the code otherwise does what you want.

In the sheets below, I have manually added the names in adjacent columns to help with my checking.

Excel Workbook
AB
2A1234567John
3K8956325Jen
4B1234567Andy
5D1234567Tam
6C1234567Mike
7Z4444444Todd
Workbook 1
Excel Workbook
ABF
1Id'sFreeText
2X4565434SimonThis person likes everybody
3C1234567MikeNo issues
4A1234567JohnJohn has issues with the following .Andy B1234567 .Mike C1234567. Tam D1234567(freetext)
5K8956325JenThis person likes everybody
6B1234567AndyProblem with John A1234567
7D1234567TamNo issues
8Z6666666JimProblem with Todd
9Z4444444ToddProblem with Jim
Workbook 2
Running the post 42 code with just the above data produces the sheet below.
Excel Workbook
ABCD
2A1234567B1234567JohnAndy
3A1234567C1234567JohnMike
4A1234567D1234567JohnTam
5B1234567A1234567AndyJohn
Sheet9
 
Last edited:
Upvote 0
Forgetting about the double clicking for a moment The code( your code ) otherwise works well..Fantastic job and well done you..I am amazed at how my life and workload will be freed up now thanks and hats off, especially when its all done and dusted. Cant wait.
 
Last edited:
Upvote 0
Your code still seems to only get John = A1234567 <>with Andy B123456 and does not get John = A1234567 <>with Mike C1234567 or / and John = A1234567 <> with Tam D1234567
.. So its works for the first hit but not the rest
.. your code otherwise works well ..
:confused: Hmm, these two things seem to contradict, but if you are happy so am I.

Is it a problem about any repeating conflicts like in rows 2 & 5 of the last screen shot in post 46?
 
Upvote 0
No. I can live with the repeats.
Also. You asked ...
The double-up of the John/Andy conflict in the report sheet will be easily fixed if the code otherwise does what you want...I was merely just answering this..thanks
 
Upvote 0
Ok, but while it is fresh in my mind, I will post the adjustment in case you change your mind and decide you don't want both yellow rows from the last screen shot in post 46 (Sheet9).
It would just need this line replacement in the existing code.
Rich (BB code):
<del>d(a(i, 1) & ";" & m) = Empty</del>
If Not d.exists(a(i, 1) & ";" & m) And Not d.exists(m & ";" & a(i, 1)) Then d(a(i, 1) & ";" & m) = Empty
 
Upvote 0

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

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