HELP Looking up a specific data and criteria.

[shahid5788]

Hi, I am trying to find a formula which will identify certain criteria. Please see below these are my example transaction description. I have about over 40k transaction line items. I need a formula that will help me pick up transaction descriptions that contain "CHGBCK/ADJ" OR transaction that start with "Cxxxxxxx Ent"

I also would like you do some sort of lookup with that ID "Cxxxxxxx Ent" it can identity the "Name"


[TABLE="width: 1114"]
<colgroup><col></colgroup><tbody>[TR]
[TD]TRANSACTION DESCRIPTION [/TD]
[/TR]
[TR]
[TD]ACH DEBIT RECEIVED - Cust ID: ABCD Desc: CHGBCK/ADJ Comp Name: AMERICAN EXPRESS Comp ID: 0000000 Batch Discr: PAYMENT DATE 0 SEC: CCD Cust Name: TASK 1 Addenda: No Addenda [/TD]
[/TR]
[TR]
[TD]
ACH DEBIT RECEIVED - Cust ID: ABCD Desc: CHGBCK/ADJ Comp Name: AMERICAN EXPRESS Comp ID: 0000022 Batch Discr: PAYMENT DATE 0 SEC: CCD Cust Name: TASK 20 Addenda: No Addenda [/TD]
[/TR]
[TR]
[TD]
ACH DEBIT RECEIVED - Cust ID: ABCD Desc: CHGBCK/ADJ Comp Name: AMERICAN EXPRESS Comp ID: 0000070 Batch Discr: PAYMENT DATE 0 SEC: CCD Cust Name: TASK 87 Addenda: No Addenda [/TD]
[/TR]
[TR]
[TD]
ACH DEBIT RECEIVED - Cust ID: Ebay0001234567 Desc: TRANSFER Comp Name: PAYMENT Comp ID: 00000054a SEC: CCD Cust Name: C1234567 ENT COMPLE Addenda: No Addenda [/TD]
[/TR]
[TR]
[TD]
ACH DEBIT RECEIVED - Cust ID: Ebay0001234568 Desc: TRANSFER Comp Name: PAYMENT Comp ID: 00000084d SEC: CCD Cust Name: C1234578 ENT COMPLE Addenda: No Addenda [/TD]
[/TR]
[TR]
[TD]
ACH DEBIT RECEIVED - Cust ID: Ebay0001234458 Desc: TRANSFER Comp Name: PAYMENT Comp ID: 00000077d SEC: CCD Cust Name: C1234595 ENT COMPLE Addenda: No Addenda


[TABLE="width: 506"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD] Name[/TD]
[TD][/TD]
[TD][/TD]
[TD]ID#[/TD]
[/TR]
[TR]
[TD]AUG ENT COMPLEX/NE TIX[/TD]
[TD][/TD]
[TD][/TD]
[TD]C1234567[/TD]
[/TR]
[TR]
[TD]PPLCenter/NewEraTicket[/TD]
[TD][/TD]
[TD][/TD]
[TD]C1234578[/TD]
[/TR]
[TR]
[TD]WELLSFARGOCTRCOMCASTTIX[/TD]
[TD][/TD]
[TD][/TD]
[TD]C1234595[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 257"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fennek]

Hi,

also normaly I do not want to compete against professional, try this code:

<code>
Sub fen()
Dim Tr, C_ID
Tr = Sheets(1).Columns(1).SpecialCells(2).Resize(, 3)
C_ID = Sheets(2).Cells(1).CurrentRegion
With CreateObject("VBScript.Regexp")
.Pattern = "Name: C\d{7}\sENT"
For i = 1 To UBound(Tr)
If InStr(Tr(i, 1), "CHGBCK/ADJ") > 0 Then Tr(i, 2) = "CHB"
If .test(Tr(i, 1)) Then
Set RR = .Execute(Tr(i, 1))
ID = Mid(RR(0), 7, 8)
Z = WorksheetFunction.Match(ID, Application.Transpose(Application.Index(C_ID, 0, 2)))
Tr(i, 3) = Application.Index(C_ID, Z, 1)
End If
Next i
Sheets(3).Cells(1).Resize(UBound(Tr), 3) = Tr
End With
End Sub
</code>

I suppose the transaction data in sheets(1).cloumns(1), the customers name and ID in sheets(2).columns("A:B") and the results is written in sheets(3).

It should be fast, for 40,000 rows less then 5 seconds.

regards

 

[shahid5788]

How did you setup your worksheet in order to record this VBA Code?

I am currently getting an error.
"Z = WorksheetFunction.Match(ID, Application.Transpose(Application.Index(C_ID, 0, 2)))"

 

[Fennek]

Hi,

as I said: it needs 3 sheets, in particular the names are in sheet(2).column("A") and the ID in "B".

Z is the row-number where the ID found in sheet(1) is located in the customer list in sheet(2).

regards

 

[shahid5788]

Hi Fennek,

Thank you so much for this VBA code. And I apologize if I read your initial response fast. Is it possible you can explain this code a little more in simpler terms so I can understand it a little better?

 

[shahid5788]

Hi,

I have changed my ID# C1234567 from 7 digits to 6 digits C241153.

Will that change the VBA code?