Need an Alternative than just V-LookUPs

[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 "Cxxxxxx Ent"

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

[TABLE="class: cms_table, width: 1114"]
<tbody>[TR]
[TD]TRANSACTION DESCRIPTION[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

[TABLE="class: cms_table, width: 1114"]
<tbody>[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: C123456 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: C123457 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: C123459 ENT COMPLE Addenda: No Addenda [/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="class: cms_table, width: 506"]
<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]C123456[/TD]
[/TR]
[TR]
[TD]PPLCenter[/TD]
[TD][/TD]
[TD][/TD]
[TD]C123457[/TD]
[/TR]
[TR]
[TD]WELLSFARGOCT[/TD]
[TD][/TD]
[TD][/TD]
[TD]C123459[/TD]
[/TR]
</tbody>[/TABLE]

 

[mikerickson]

It sounds like a filter would suit your needs better than a formula.
Auto-Filter could do it in place, but if you want it copied to another location, AdvancedFilter should work.

 

[shahid5788]

Is there a VBA code that would work for this? Something that will extract my first contingency of ("CHGBCK/ADJ" OR transaction that start with "Cxxxxxx Ent") and then also do a lookup from my table below in order to match ID# to their Names.

 

[mikerickson]

Have you looked at Advanced Filter or AutoFilter, they would do that without need for VBA.

 

[shahid5788]

I have not what would be best way to go about using Advanced Filter?

 

[mikerickson]

Make sure your data has headers and use a three row criteria range

Your Header
*CHGBCK/ADJ*
*Desc: C?????? Ent*