vba to find specific combination of chars in one column

[gint32]

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

 

[gint32]

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..

 

[Peter_SSs]

Thanks for the clarifications.

Try this slightly modified version.

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

 

[gint32]

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...

 

[gint32]

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.

 

[gint32]

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

G1234567	E1367986
G1234567	B2451610

<tbody>
</tbody>

If G1234567(John) had an issue with two people (Mike = E1367986 & Tam = 2451610).

 

[Peter_SSs]

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
	A	B
2	A1234567	John
3	K8956325	Jen
4	B1234567	Andy
5	D1234567	Tam
6	C1234567	Mike
7	Z4444444	Todd
Workbook 1

Excel Workbook
	A	B	F
1	Id's		FreeText
2	X4565434	Simon	This person likes everybody
3	C1234567	Mike	No issues
4	A1234567	John	John has issues with the following .Andy B1234567 .Mike C1234567. Tam D1234567(freetext)
5	K8956325	Jen	This person likes everybody
6	B1234567	Andy	Problem with John A1234567
7	D1234567	Tam	No issues
8	Z6666666	Jim	Problem with Todd
9	Z4444444	Todd	Problem with Jim
Workbook 2

Running the post 42 code with just the above data produces the sheet below.

Excel Workbook
	A	B	C	D
2	A1234567	B1234567	John	Andy
3	A1234567	C1234567	John	Mike
4	A1234567	D1234567	John	Tam
5	B1234567	A1234567	Andy	John
Sheet9

 

[gint32]

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.

 

[Peter_SSs]

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 ..

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?

 

[gint32]

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

 

[Peter_SSs]

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.

<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