| 20210509 Stat Pull Chacon Encounters.xlsm | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | To be Looked into | VBA | O365 | ID | |||
| 2 | ABC(1234),FGH(8976),EFG(4567) | FGH | FGH | 8976 | |||
| 3 | FGH(8976),EFG(45678),CDE(3456) | CDE | CDE | 3456 | |||
| 4 | ABC(1234),FGH(8976),EFG(4567) | ABC | ABC | 1234 | |||
| 5 | FGH(8976),EFG(45678),CDE(3456) | #N/A | #N/A | 9999 | |||
Sheet2 | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2:C5 | C2 | =XTRACT(A2,E2) |
| D2:D5 | D2 | =LET(tbl,FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,")",""),"(",","),",","</b><b>")&"</b></a>","//b"),r,ROWS(tbl),seq,SEQUENCE(r/2,2),x,INDEX(tbl,seq),INDEX(INDEX(x,,1),MATCH(E2,INDEX(x,,2),0))) |
Function XTRACT(s As String, lk As String)
Dim res As String: res = "N/A"
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "(\w+)\((\d+)\)"
Set matches = .Execute(s)
For Each m In matches
If m.submatches(1) = lk Then res = m.submatches(0): Exit For
Next m
End With
XTRACT = res
End Function
| kishorenaidugurram.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| I | J | K | L | M | N | O | |||
| 2 | To be Looked into | Output | Name | ID | Work | ||||
| 3 | ABC(1234),FGH(8976),EFG(4567) | ABC | Looking or a Formula in J3 | ABC | 1234 | ABC | |||
| 4 | FGH(8976),EFG(45678),CDE(3456) | CDE | Looking or a Formula in J4 | BCD | 2345 | ||||
| 5 | CDE | 3456 | CDE | ||||||
| 6 | |||||||||
| 7 | Need to look values in Col N in Col I and if matching found return the associated name | ||||||||
| 8 | Ex: 1234 is in Cell I3 and return corresponding text i.e, ABC | ||||||||
| 9 | |||||||||
Sheet1 (2) | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J3:J9 | J3 | =IFERROR(INDEX($O$3:$O$9999,AGGREGATE(15,6,ROW($O$3:$O$9999)-ROW($O$2)/(O$3:O$9999<>""),ROW()-ROW($J$2))),"") |
| O3:O9 | O3 | =IFERROR(MID(INDEX($I$3:$I$9999,AGGREGATE(15,6,ROW($N$3:$N$9999)-ROW($N$2)/(ISNUMBER(SEARCH("("&N3&")",$I$3:$I$9999))),1)),SEARCH("("&N3&")",INDEX($I$3:$I$9999,P3))-3,3),"") |
I am using 2013/2016 version
| Book3 | ||||||||
|---|---|---|---|---|---|---|---|---|
| I | J | K | L | M | N | |||
| 1 | To be Looked into | Output | Name | ID | ||||
| 2 | ABC(1234),FGH(8976),EFG(4567) | ABC | 1234 | |||||
| 3 | FGH(8976),EFG(45678),CDE(2345) | CDE | 2345 | |||||
| 4 | XXX(8976),YYYY(45678),Z(99) | YYYY | 45678 | |||||
| 5 | XXX(8976),YYYY(45678),Z(99) | Z | 99 | |||||
| 6 | XXX(8976),YYYY(45678),Z(99) | 100 | ||||||
Sheet4 | ||||||||
Sub findtext()
Dim d As String, dd() As String, lr As Long
Dim i As Long, j As Long, val1 As Long
lr = Cells(Rows.Count, "I").End(xlUp).Row
For i = 2 To lr
d = Cells(i, 9)
d = Replace(Replace(d, "(", ","), ")", "")
dd = Split(d, ",")
val1 = Cells(i, 14).Value
For j = 1 To UBound(dd) Step 2
If val1 = dd(j) Then
Cells(i, 10) = dd(j - 1)
Else
End If
Next j
Next i
End Subthanks for response. Getting stuck @val1 = Cells(i, 14).Value. Also where are we using Col N in the above VBA please?
Would like to check Emp. ID in column N which is unique and search for Col I and if found fetch the name of the Employee.
Runtime Error 13. Type Mismatch
