applying values from one cell to append other cell values

[J.Steik]

Hello - I have receieved some great help on an Excel worksheet I'm trying to put together - I thought I ought to start a new thread vs continuing the same thread throuhg several questions I've had - if Im doing this incorrectly please forgive me. Here is my question, reposted

I have a couple of issues I’m attempting to address with my project and hoped you folks could help me out. In the macro ‘SeparateIDs’ it looks for certain criteria in the Equipment ID it extracts..I need to broaden those criteria to include IDs containing hyphens and parenthesis as some IDs end up having numbers in parenthesis at the end of them. I also need to omit any results 2 characters or less. That brings me to my next hurdle…
..Prior to running the macro ‘SeparateIDs’ there is an equipment ID number in column F on the same row as the cell I’m extracting data from (‘Description’, column J)…that already existing ID in col F most often starts with a 2-digit number: what I’d like to do is when extracting the equipment ID’s from column J into column F, to reference the already existing number in F , if it starts with a 2-digit number then apply that 2-digit prefix to the beginning of the extracted IDs which have been pulled column J
So if F2 has an ID which is 03GT456 and the IDs we extract from J2 meet certain criteria we apply the ‘03’ prefix to the left side of them. The extracted IDs which need the prefix attached need to
· not be an ID which contains a hyphen
· be greater in length than 3 characters
So if the ID extracted from J2 was ‘LC456’, it would then be ‘03LC456’and so forth. If it was ‘V-456’ it would be left unchanged, but still extracted. If it was ‘GT’ it would not be extracted, and so forth.
I’ve tried experimenting with different expressions in the macro but am not certain I am using the ‘[‘ correctly and have failed miserably.
Is this something I can do with some modifications of the current macro?
Once again, thank you!

 

[NARAYANK991]

Hi ,

Your requirements are somewhat complicated , and possibly complex ; can you wait for at least a day , if not more ?

Narayan

 

[J.Steik]

Hello Narayn - absolutely ..I'm grateful for the assistance!

 

[shrivallabha]

While Narayan is busy lets take a shot at solving this.

Couple of questions:
1. Do you want to append the results from Column J to Column F value (what the thread title says but not in the thread body)?

2. If answer to Q1 is yes then the instrument tag (I am guessing that they are that by the pattern), if it is getting duplicated then how do you want them to be handled?
e.g. in F3 we have 08LC888 and the tag match found in J3 has LC888 which will become 08LC888 and therefore duplicate so do you want two results as
08LC888, V-886, 08LC888
or
08LC888, V-886

3. Is "," acceptable separator between tags or you might need something different later in the project?

I think somehow you are from Oil and Gas fields and having some work with Field testing loops which involves Equipment Tags (the ones "-" separated tags) and rest instrumentation items like Level transmitters etc. etc. Of course, that doesn't need to be correct and nor do you need to clarify this except above three.

 

[J.Steik]

hello Shrivallabha - It would be more efficient to not append column J to column F, I figured removing dupliucates could be done after the fact but would rather not have them in the first place - I don't have the VBA code 'know-how' but yes, these tags are based on those types of equipment. I have been trying to play with an ISNUMBER statement and extracting the left two characters, but I'm sure there must be a better way. The "-" hypens aren't separating tags , rather they are part of another naming convention for piping and such.
Basically, if F2 has the first 2 chracters as numbers, then the IDs extracted from J2 get the 2-digit prefix attached if the meet the criteria (no hyphens and >3 characters)

 

[shrivallabha]

Hi J,

In the module1 replace the complete code with following code. I have written comments in the code for your reference:

Option Explicit
Public Function ExtractID(strToCheck As String, rngReference As Range) As String
'\\ Function Arguments  ( Column J Value  , Column F Value  )
Dim objRegExp As Object
Dim varMatch
Dim strPrefix As String
'\\ Check following pattern
'\\ 1. Has numbers to begin with | or nothing
'\\ 2. Alphabets 1 or more
'\\ 3. Separated by Hyphen | or nothing
'\\ 4. Followed by some numbers
Set objRegExp = CreateObject("VBScript.RegExp")
With objRegExp
  .Global = True
  .IgnoreCase = False
  .MultiLine = True
  .Pattern = "([0-9]+|)[A-Z]+(-|)[0-9]+"
  '\\ Pattern  1.  2.  3.  4.
End With

'\\ Lets do some preparation
strPrefix = vbNullString
If IsNumeric(Left(rngReference.Value, 2)) Then strPrefix = Left(rngReference.Value, 2)
ExtractID = rngReference.Value

'\\ Loop through matched results
For Each varMatch In objRegExp.Execute(strToCheck)
  '\\ Check length of match
  If Len(varMatch) > 2 Then
  '\\ See if it contains -
  If InStr(CStr(varMatch), "-") > 0 Then
  '\\ Check if it is already in the list
  If InStr(" " & ExtractID, " " & CStr(varMatch)) = 0 Then
  ExtractID = ExtractID & " " & CStr(varMatch)
  End If
  Else
  If InStr(" " & ExtractID, " " & strPrefix & CStr(varMatch)) = 0 Then
  ExtractID = ExtractID & " " & strPrefix & CStr(varMatch)
  End If
  End If
  End If
Next

'\\ Return final string
ExtractID = Replace(Trim(ExtractID), " ", ", ")

End Function
Public Sub Separate_IDs()
Dim lngLastRow As Long, i As Long
Const intRptCol As Integer = 11 '\\ Corressponds column K
lngLastRow = Cells(Rows.Count, "J").End(xlUp).Row

'\\ Loop through column J and check matches
Application.ScreenUpdating = False
For i = 2 To lngLastRow
  If Len(Cells(i, "J").Value) > 0 Then
  Cells(i, intRptCol).Value = ExtractID(Cells(i, "J").Value, Cells(i, "F"))
  End If
Next i
Application.ScreenUpdating = True

End Sub

Then you have two options to implement the solution.

Solution 1:
Run macro "Separate_IDs"

Solution 2:
In the desired column where you need the result put following formula in row 2:
=ExtractID(J2,F2)

Should give you the same result.

Edit:= The code formatting has gone kaput . I am uploading file where the formatting is intact.

 

[J.Steik]

that is nearly the exact result I am looking for! I am having 2 issues with the macro: 1. it is adding the 2-digit prefix to even the equipment that already starts with numbers 2. I would like to cause the output to display in column F where the initial equipment ID is, transposed under the initial ID number - similar to the macro I posted in my attempt - I am nearly lost within the VBA code youve put together - where can I change these variables?
I must say - outstanding!! Thank you so much!

 

[J.Steik]

in looking back I failed to addrewss a 3rd criteria for IDs to receive the prefix: they can't start with a number either ..I failed to include that. My apologies

 

[shrivallabha]

in looking back I failed to addrewss a 3rd criteria for IDs to receive the prefix: they can't start with a number either ..I failed to include that. My apologies

That's OK. Lets see if we can fix that in the function first. If the results are in agreement the modifying the sub routine should be easier task

Change this line in the function ExtractID:

If InStr(CStr(varMatch), "-") > 0 Then

to

If InStr(CStr(varMatch), "-") > 0 Or IsNumeric(Left(CStr(varMatch), 2)) Then

And then run the function is worksheet as demonstrated in the file uploaded (Refer column L).

Please check and get back with your findings.