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

 

[Peter_SSs]

Most likely much of the complication is because we don't know exactly what is, or might be, in your sheet ...

.. and exactly what you are trying to do. From your latest written description I am still in the same boat.

Would it be possible to make up some smallish dummy data (but big enough to demonstrate any variety in the actual data) for workbook1, sheet1 columns A & F and workbook2, sheet1 (I don't know what column) then also show us the expected results, where those results should go and how you got them, manually?

 

[hiker95]

gint32,

It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com.

Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

Include a detailed explanation of what you would like to do referring to specific cells and worksheets.

If the workbook contains confidential information, you could replace it with generic data.

 

[gint32]

.. and exactly what you are trying to do. From your latest written description I am still in the same boat.

Would it be possible to make up some smallish dummy data (but big enough to demonstrate any variety in the actual data) for workbook1, sheet1 columns A & F and workbook2, sheet1 (I don't know what column) then also show us the expected results, where those results should go and how you got them, manually?

Hi , Your supplied vba routine works as well as I expected, so thanks very much for that, So, I now have an Excel file with test data that I have sorted with a simple Vlookups, I'll upload if I can.
When you look at it you see I now need a way of finding if any of the columns values within each (named vba1,vba2 etc) are in the (colA ) ie Workbook1.A1-A45 and if so then identify these somehow, by using different colours (I used brown in the test) or whatever you suggested so as they stand out.

It would also be a really nice touch if I double clicked on the specific Id number with the cells(vba1,vba2 etc) to bring up all of the corresponding free text cell in a message box, but I guess that would be stretching things to far. So just finding and exposing these values would be great feat!

 

[gint32]

gint32,

It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com.

Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

Include a detailed explanation of what you would like to do referring to specific cells and worksheets.

If the workbook contains confidential information, you could replace it with generic data.

try this...took ages to download drop box...so finally here goes ...
https://www.dropbox.com/s/xx15odhehi2l6ou/TestArts.xlsm?dl=0&m=

 

[Peter_SSs]

To clarify ..

1. Do each pair need to be a different colour? eg you have both J1074420 values green and both E1367986 values brownish. Or are those different colours just to show us the pairing and all pairs coloured green would be fine?
If they need to be coloured differently in pairs, why are the C2727956 values coloured differently to each other? Also, if each ID number has to be a different colour, about how many different colours might we need in your actual data?

2. Will the numbers always only be in pairs? For example could C2727956 appear once in the ID column and 5 times in the Vba columns?

3. "It would also be a really nice touch if I double clicked on the specific Id number with the cells(vba1,vba2 etc) to bring up all of the corresponding free text cell in a message box," So if a value you double-clicked on occurred in 25 rows in Workbook 2, the message box would contain 25 lots of the column F free text?

 

[gint32]

To clarify ..

1. Do each pair need to be a different colour? eg you have both J1074420 values green and both E1367986 values brownish. Or are those different colours just to show us the pairing and all pairs coloured green would be fine?
If they need to be coloured differently in pairs, why are the C2727956 values coloured differently to each other? Also, if each ID number has to be a different colour, about how many different colours might we need in your actual data?

2. Will the numbers always only be in pairs? For example could C2727956 appear once in the ID column and 5 times in the Vba columns?

3. "It would also be a really nice touch if I double clicked on the specific Id number with the cells(vba1,vba2 etc) to bring up all of the corresponding free text cell in a message box," So if a value you double-clicked on occurred in 25 rows in Workbook 2, the message box would contain 25 lots of the column F free text?

Thanks for getting back so quickly.
To answer your questions,

1. Each pair does not need to be a different colour it was just to highlight for easier reading, having thought and read your logic, I think a better approach would be (if not too difficult) to pull the matches into a new sheet. As I am only interested in the matches(records combinations) that are found both in the the vba columns that are also in Column A, the rest are all redundant. Also, C2727956 should have both been the same colour.

1. Will the numbers always only be in pairs?...If the numbers appear more than once in VBA’s and the #’s are in the same row-(record) then only need the first instance of that, but if the #’s are in multiple rows then each of these need to be with the corresponding #’s on the new sheet.

1. Corresponding free text - message box, If we are using a new sheet to show each match/ occurrence, then double-click would be only the unique corresponding free text for just that record in that’s in relation to the “ID’s” Col.. so as the user can read the through the details(free text) and make an informed decision about what to do next, ie skip or drill in and amend the data.

 

[gint32]

Something similar to the below would be great!
ID#'s VBA's
[TABLE="width: 304"]
<tbody>[TR]
[TD]C2727956
[/TD]
[TD]E1367986 -1
[/TD]
[/TR]
[TR]
[TD]D1911054[/TD]
[TD]E1367986 -2
[/TD]
[/TR]
[TR]
[TD]F2449706[/TD]
[TD]E1367986 -3
[/TD]
[/TR]
[TR]
[TD]E1367986[/TD]
[TD]C2727956
[/TD]
[/TR]
[TR]
[TD]B2451610[/TD]
[TD]J1074420[/TD]
[/TR]
[TR]
[TD]J1074420[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

1. Will the numbers always only be in pairs?...If the numbers appear more than once in VBA’s and the #’s are in the same row-(record) then only need the first instance of that, but if the #’s are in multiple rows then each of these need to be with the corresponding #’s on the new sheet.

I'm not quite sure what that meant. Might need a specific example or two if it isn't already covered with my code below.


Never-the-less, give this a try in another copy of your workbook to see if it is the sort of summary you are after in relation to the following:

1. .. I think a better approach would be .. to pull the matches into a new sheet. As I am only interested in the matches(records combinations) that are found both in the the vba columns that are also in Column A,

Sub Find_Matches()
  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)) = d(a(i, 1)) & ";" & m
      If Not d.exists(CStr(m)) Then d(CStr(m)) = Empty
    Next m
  Next i
  Application.ScreenUpdating = False
  Sheets.Add After:=Sheets(Sheets.Count)
  With Sheets(Sheets.Count)
    With .Range("A2:B2").Resize(d.Count)
      .Value = Application.Transpose(Array(d.Keys, d.Items))
      .Columns(2).TextToColumns DataType:=xlDelimited, Semicolon:=True, Other:=False, FieldInfo:=Array(1, 9)
    End With
  End With
  Application.ScreenUpdating = True
End Sub

 

[gint32]

I'm not quite sure what that meant. Might need a specific example or two if it isn't already covered with my code below.

Never-the-less, give this a try in another copy of your workbook to see if it is the sort of summary you are after in relation to the following:

Thanks thats ever so close you are a champ, thing is if there are more than one reference number with the same line of VBAs columns then your code seems to only it only gets the first and not the rest of the unique Id's

Meaning if I had the following line

Id's ? ?

<tbody>
[TD="class: xl66"]C2727956[/TD]
[TD="class: xl65, width: 114"]H0000008[/TD]
[TD="class: xl65, width: 134"]D1911054[/TD]
[TD="class: xl67, width: 134"]E1367986[/TD]
[TD="class: xl65, width: 134"]H0000008[/TD]
[TD="class: xl68, width: 134"]F2449706[/TD]
[TD="class: xl65, width: 134"]H0000008[/TD]

</tbody>

If possible , what I was looking for was some like :

[TABLE="class: cms_table, width: 304"]
<tbody>[TR]
[TD]C2727956[/TD]
[TD]E1367986 -1[/TD]
[/TR]
[TR]
[TD]D1911054[/TD]
[TD]E1367986 -2[/TD]
[/TR]
[TR]
[TD]F2449706[/TD]
[TD]E1367986 -3[/TD]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Please don't quote whole posts unless there is a specific reason - makes the thread hard to navigate/review. I have pruned that last post.

Meaning if I had the following line

I'm afraid you are losing me. Unlike hiker95, I prefer small samples (of the critical data only) & expected results, not whole workbooks with lots of data. So can we try with the following sample data? I think (hope) I have retained enough relevant rows, though we may need a little more to demonstrate what you are referring to above.

In this sheet I have removed most rows & as I understand it, only column A is relevant to start with. I know those colored cells will not be coloured either but left that as they will be relevant later.

Book1
	A
1	ID'S
2	B0831052
3	B2451610
4	C2727956
5	D1783524
6	D1911054
7	E1367986
8	H0960271
9	J1074420
Workbook 1

Let's suppose you just have this in Workbook 2 - apart from any change/addition as a result of a) below

Book1
	A	F
1	Id's	FreeText
2	F0990815	H0000006 fdsgdgf H0000010 vcbxbv H00000000001327
3	B2451610	TamH8767654 B8787654 J1074420Andy_H8727654 B8737654Jonny
4	D2233587	H0000006 fdsgdgf H0000010 vcbxbv H00000000001328
5	H1402171	gfffsgbbbbbbbbbbbvc H0000005fgdgfgfgfgdsdr5646hgfgxhgf
6	C2727956	dfgzdgfg H0000008 H0000006fggffg H0wqrewqe000001 E1367986 H0000008
7	E1367986	TamH8767654 C2727956 Andy_H8727654 B8737654Jonny
8	F2566913	gfffsgbbbbbbbbbbbvcH0000005 eargargagagaH0000006fggf
Workbook 2

Now, forgetting about ..

- the previous vba code,
- any double-clicking afterwards,
- any perception you have about what you think might be the best way to proceed

.. can you

a) State exactly what to add in what sheet and what cell(s) so that we get a situation where "more than one reference number with the same .." is true.
b) Show exactly what, in an ideal world, you would want on the new worksheet if the change in a) immediately above is included

(If I have not said so already, my signature block below has a link with how to post screen shots like this directly in your post that we can copy from. If that is possible for you it is easier for me, and most other readers, than having to go elsewhere & download files)