vba to find specific combination of chars in one column

gint32 · Sep 11, 2018

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 · Sep 18, 2018

Thanks again..your vba works a treat but stops at the first Unique record in each line(the vbas). Also, i gave you the least amount of data possible on my last post as it was just one line (Ids & vbas ) and then the expected out come from that one line of data. Its wasnt a wholesheet of data as you say. So im a bit lost now too..my applogies

Peter_SSs · Sep 18, 2018

gint32 said:
Thanks again..your vba works a treat but stops at the first Unique record in each line(the vbas).

In post 29 you didn't report any stoppages & I haven't changed the code. I also ran that code from post 28 on the file that you had uploaded previously. Whilst it may not produce the exact results you are after, it didn't "stop" anywhere. Have you changed something at your end? Or perhaps I am mis-interpreting what you mean by the code "stops"?

gint32 said:
Also, i gave you the least amount of data possible on my last post as it was just one line (Ids & vbas ) ..

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>

Aren't those red numbers the result of running the earlier code that I had suggested, not your original data? From what I can see in the uploaded file, the only rows that look similar to that are the rows in Workbook 1 which are derived by formula from looking up the data in Workbook 2 that was produced by the vba code.

Doesn't your raw data consist of just Column A in Workbook 1 and columns A:F of Workbook 2? If so, I don't think any of those would be referred to as vbas would they?

Summary:
You are now asking for a more compact/succinct result, without redundant values. I am looking to give you that without performing unnecessary tasks.

Am I wrong to think that if we were doing this manually, we could get a set of results by looking at the 3 columns of data in post 30?

gint32 · Sep 18, 2018

Apologies. What I meant by "stops" is it moves on to the next line and disregards any other unique record(on the same line). So what i am saying if there are 10 hits(Vba's) within a row within the vba's if they are unique and are also in the id#s column then pair them together in the new created sheet one row for each.as in the example i supplied
Well hopefully I supplied enough details now To get what I meant.FYI..without saying to much these ids are actually a reference to actual people. So Imagine the following so you understsnd my needs..say it was an register of all army personal in the usa....and we had a data base full off all the known issues or grievances with all other soldiers. And we dont want to post two or more soldier's together in the same front line . Do we..so The free text would and does contain the detailed conflict/s information with all the ids he has an issue with( against the whos) and the ids column holds the person who orginally logged the complaint that they have the issue with. So your code gets the first guy he has an issue with...but forgets about the other guys who also got in on the fight..

Peter_SSs · Sep 18, 2018

gint32 said:
Well hopefully I supplied enough details now To get what I meant

About the "stops" yes, but you didn't answer my other questions. I have emphasised them below.

Peter_SSs said:
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>

Aren't those red numbers the result of running the earlier code that I had suggested, not your original data? From what I can see in the uploaded file, the only rows that look similar to that are the rows in Workbook 1 which are derived by formula from looking up the data in Workbook 2 that was produced by the vba code.

Doesn't your raw data consist of just Column A in Workbook 1 and columns A:F of Workbook 2? If so, I don't think any of those would be referred to as vbas would they?

Summary:
You are now asking for a more compact/succinct result, without redundant values. I am looking to give you that without performing unnecessary tasks.

Am I wrong to think that if we were doing this manually, we could get a set of results by looking at the 3 columns of data in post 30?

gint32 · Sep 18, 2018

Hi Pete

Re- Aren't those red numbers the result of running the earlier code :- Yep, that’s correct they are produced by the vba code you supplied ealier, into spew out into the vba’s in the workbook I uploaded, thanks to your other helpers.

re- Doesn't your raw data consist of just Column A in Workbook 1 and columns A:F of Workbook 2? If so, I don't think any of those would be referred to as vbas would they? : - Yes, clearly.

Summary:
re- You are now asking for a more compact/succinct result, without redundant values. I am looking to give you that without performing unnecessary tasks. : -Great to here! & hopefully I won’t and thanks once again[FONT=&quot]?[/FONT]

re- Am I wrong to think that if we were doing this manually, we could get a set of results by looking at : -Might not cover all permutations though.

Hopefully I’ll have addressed your questions now, thanks very much for sticking with me

gint32 · Sep 18, 2018

Incidentally your code was almost there, its just that if there are more than one (different/unique) within the free text thats also in columnA.Workbook1 then as I've said before seems to just get one and not more than one..

Peter_SSs · Sep 18, 2018

Thank you. That confirms most of what I have been thinking.

However, I don't exactly understand the final requirement, so I'm (still) asking that instead of repeatedly telling me in words thinks like :

gint32 said:
.., its just that if there are more than one (different/unique) within the free text thats also in columnA.Workbook1 then as I've said before seems to just get one and not more than one..

.. can you please show me ..

- some original raw data that leads to that situation
- the results from that raw data

Surely it can be something quite like that very small data I posted in post 30 with any minor addition/adjustment to introduce this final part I don't understand.
I still think what I posted back then is relevant

Peter_SSs said:
So can we try with the following sample data? .. may need a little more to demonstrate what you are referring to above.

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)

gint32 · Sep 19, 2018

Hi,
From what was pasted in your last post, the result I'd expect to see would be as follows (only)

[TABLE="width: 123"]
<colgroup><col width="85" style="width: 64pt; mso-width-source: userset; mso-width-alt: 3108;"> <col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;"> <tbody>[TR]
[TD="width: 85, bgcolor: transparent"]ID'S[/TD]
[TD="width: 78, bgcolor: transparent"]Matches[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C2727956[/TD]
[TD="bgcolor: transparent"]E1367986
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]E1367986[/TD]
[TD="bgcolor: transparent"]C2727956
[/TD]
[/TR]
</tbody>[/TABLE]

I'll add a few or rows though to be more realistic. in the next post

gint32 · Sep 19, 2018

How ever if I add the following to workbook2
[TABLE="width: 330"]
<tbody>[TR]
[TD="width: 58, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G1234567
[/TD]
[TD="width: 381, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]dfgzdgfg H0000008 H0000006fggffg H0wqrewqe000001 E1367986H0000008B2451610B2451610F0990815
[/TD]
[/TR]
[TR]
[TD="width: 58, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H1414140
[/TD]
[TD="width: 381, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]asaSa. F0990815 TONY. H1402171
[/TD]
[/TR]
</tbody>[/TABLE]

And the following to Workbook1
[TABLE="width: 70"]
<colgroup><col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3401;"><tbody>[TR]
[TD="width: 93, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "][TABLE="width: 70"]
<colgroup><col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3401;"> <tbody>[TR]
[TD="width: 93, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G1234567[/TD]
[/TR]
[TR]
[TD="width: 93, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H1414140[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

I'd expect to see this result [TABLE="width: 136"]
<colgroup><col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3401;"><colgroup><col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;"><tbody>[TR]
[TD="width: 93, bgcolor: transparent"]ID'S[/TD]
[TD="width: 88, bgcolor: transparent"]Matches[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C2727956[/TD]
[TD="bgcolor: transparent"]E1367986[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]E1367986[/TD]
[TD="bgcolor: transparent"]C2727956
[/TD]
[/TR]
[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]

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 85, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]

[/TD]
[/TR]
[TR]
[TD="width: 85, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]

[/TD]
[/TR]
</tbody>[/TABLE]

Peter_SSs · Sep 19, 2018

Thanks, this is getting down to the sort of nitty-gritty detail that should help clarify things for me. However, you do need to be careful & accurate with requirements and results because it is all I have to go on. So I have a couple of follow-up questions below. But first ..

gint32 said:
How ever if I add the following to workbook2
[TABLE="width: 330"]
<tbody>[TR]
[TD="width: 58, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G1234567
[/TD]
[TD="width: 381, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]dfgzdgfg H0000008 H0000006fggffg H0wqrewqe000001 E1367986H0000008B2451610B2451610F0990815
[/TD]
[/TR]
[TR]
[TD="width: 58, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H1414140
[/TD]
[TD="width: 381, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]asaSa. F0990815 TONY. H1402171
[/TD]
[/TR]
</tbody>[/TABLE]

And the following to Workbook1
[TABLE="width: 70"]
<colgroup><col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3401;"><tbody>[TR]
[TD="width: 93, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "][TABLE="width: 70"]
<colgroup><col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3401;"> <tbody>[TR]
[TD="width: 93, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G1234567[/TD]
[/TR]
[TR]
[TD="width: 93, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H1414140[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]
I'd expect to see this result
ID'S Matches
C2727956 E1367986
E1367986 C2727956
G1234567 E1367986
G1234567 B2451610

Then Workbook 1 would look like this

<b>Workbook 1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:117px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:center; ">ID'S</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; ">B0831052</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; ">B2451610</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:center; ">C2727956</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:center; ">D1783524</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:center; ">D1911054</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:center; ">E1367986</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:center; ">H0960271</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:center; ">J1074420</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:center; ">G1234567</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:center; ">H1414140</td></tr></table> <br /><br />

and Workbook 2 would look like this

<b>Workbook 2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:89px;" /><col style="width:707px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >F</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:center; ">Id's</td><td >FreeText</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; ">F0990815</td><td >H0000006 fdsgdgf H0000010 vcbxbv H00000000001327</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; ">B2451610</td><td style="font-size:12pt; ">TamH8767654 B8787654 J1074420Andy_H8727654 B8737654Jonny</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:center; ">D2233587</td><td >H0000006 fdsgdgf H0000010 vcbxbv H00000000001328</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:center; ">H1402171</td><td >gfffsgbbbbbbbbbbbvc H0000005fgdgfgfgfgdsdr5646hgfgxhgf</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:center; ">C2727956</td><td >dfgzdgfg H0000008 H0000006fggffg H0wqrewqe000001 E1367986 H0000008</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:center; ">E1367986</td><td >TamH8767654 C2727956 Andy_H8727654 B8737654Jonny</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:center; ">F2566913</td><td >gfffsgbbbbbbbbbbbvcH0000005 eargargagagaH0000006fggf</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:center; ">G1234567</td><td >dfgzdgfg H0000008 H0000006fggffg H0wqrewqe000001 E1367986H0000008B2451610B2451610F0990815</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:center; ">H1414140</td><td >asaSa. F0990815 TONY. H1402171</td></tr></table> <br /><br />

1. What is the reason for excluding the red pair from the expected results?

2. Can you confirm that that B2451610 should only appear once with ID G1234567 even though it appears twice with that ID in column F of Workbook 2?

vba to find specific combination of chars in one column

gint32

Board Regular

gint32

Board Regular

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

gint32

Board Regular

Peter_SSs

MrExcel MVP, Moderator

gint32

Board Regular

gint32

Board Regular

Peter_SSs

MrExcel MVP, Moderator

gint32

Board Regular

gint32

Board Regular

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

vba to find specific combination of chars in one column

Board Regular

Board Regular

Excel Facts

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

Board Regular

MrExcel MVP, Moderator

Board Regular

Board Regular

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock