Scrubbing Invoice Data And Automating GL Coding

Y

yehayahe

New Member
Joined
Jan 10, 2019
Messages
1
Good Afternoon,

I am learning to use Excel VBA and would like some help with a project. I'd like to write a subroutine or function that scans each line of Excel transportation invoice data for a unique identifying number (PO - 7 digits, SO - 8 digits) then outputs a GL code based on characteristics of the identifying number (ie. a PO # beginning with 31- would code as inbound freight for our Eau Claire, WI branch - GL Code: 3030.00.5201.00

Which VBA code should I use for a project like this?

Also, the identifying number could be contained within any of the three columns shown below:

Reference
Reference2Reference3
PO Number: RMA# 62742598 PORM 5Order Number: RMA# 62742598 PORM 5336046
PO Number: PORM #5336483Order Number: PORM #5336483
PO Number: PORM 5336588Order Number: PORM 5336588
Order Number: 6197080
PO Number: BG293073511Order Number: 3188003
PO Number: BG291771411Order Number: 14481649
PO Number: BG292098811Order Number: 4187688Sales Order #: 14497125
PO Number: BG292718711Order Number: 14513525
PO Number: BG292720211Order Number: 14509939
PO Number: BG293044911Order Number: 6197098
PO Number: BG292663511Order Number: 14506161
PO Number: BG292868611Order Number: 14513529
PO Number: BG293072911Order Number: S/O 14523612 ** PO 6197093
PO Number: BG292531011Order Number: 2937927
PO Number: R#R5531324Sales Order #: NA
PO Number: 3260943Sales Order #: na
PO Number: BG292787711Order Number: 3870412
PO Number: BG292789211Order Number: 3188142
PO Number: BG292788311Order Number: 1253933
PO Number: BG292943411Order Number: W1F8750
PO Number: BG293086111Order Number: 4311450
PO Number: BG292713511Order Number: 5336389
PO Number: BG292712911Order Number: 5763351
PO Number: BG292711811Order Number: 1253164
PO Number: 1254006Sales Order #: na
PO Number: BG292864211Order Number: 1254008
PO Number: BG292933611Order Number: 127678
PO Number: PORM#5336677Order Number: PORM#5336677
PO Number: 1253970Order Number: 1253970
PO Number: PORM 4674826-4574824-46Order Number: PORM 4674826-4574824-4674825Customer Reference: RGA-291652
PO Number: 4268714-01Order Number: 4268714-01
Order Number: 141066-00
PO Number: 5336181Order Number: 5336181
PO Number: 14502785 PO 6196805Order Number: 14502785 PO# 6196805
PO Number: 46747600-00Order Number: 52713
PO Number: 6197121Order Number: 6197121
PO Number: RADUEOrder Number: RADUE
PO Number: NAOrder Number: 4310736-00; 4310836-00
Order Number: PORM#5336316
PO Number: 2944028Order Number: 2944028
PO Number: PORM#4675329Order Number: PORM#4675329Customer Reference: RGA# 092118bh2
PO Number: NAOrder Number: 2178381Sales Order #: 2179071
PO Number: 6194079Order Number: 373852-004
PO Number: 1253847Sales Order #: 55030823
PO Number: 6196937Order Number: 6196937
PO Number: 5336310Sales Order #: 55030824
Order Number: 6196906
Sales Order #: 14510770
PO Number: PORM#5336860Order Number: PORM#5336860
PO Number: PORM#5336642Order Number: PORM#5336642
Order Number: 6197016
Order Number: 2943972
PO Number: BG293796611Order Number: 6197223Customer Reference: 386576
PO Number: BG292188311Order Number: 14503801
PO Number: BG292905711
Order Number: 14517763
PO Number: BG293465811Order Number: 14528593
PO Number: BG293534811Order Number: 14534925
PO Number: BG293707011Order Number: 14534610
PO Number: BG293519311Order Number: PO # 2179038
PO Number: BG293459911Order Number: 3567564
PO Number: BG293822311Order Number: 434302
PO Number: BG293291911Order Number: 3188310Sales Order #: Q5620516
PO Number: BG293468111Order Number: 3188366
PO Number: BG293213211Order Number: 4315305
PO Number: BG293472411Order Number: 3188297
PO Number: BG293334911Order Number: 3870479
PO Number: BG293490311Order Number: 3870472
PO Number: BG293488111Order Number: 1254118
PO Number: BG293486811Order Number: 4916889
PO Number: BG293863011Order Number: 3870511
PO Number: BG293862111Order Number: 5518059
PO Number: BG292091211Order Number: 5336086
PO Number: 53365871Order Number: 53365871
PO Number: BG293837211Order Number: 4319880
PO Number: BG293667711Order Number: 4675460
PO Number: BG293248011Order Number: E049973
PO Number: BG293246811Order Number: 5336629
PO Number: BG293471011Order Number: 1254140
PO Number: BG293153111Order Number: RMA# 751694, RMA# 751696
PO Number: BG293456611Order Number: 4675378
PO Number: BG293295311Order Number: PORM#5336716
PO Number: BG293714511Order Number: MULTI PORMsCustomer Reference: MULTI RGAs
PO Number: RMA #6799Order Number: RMA #6799
PO Number: 4313785-00; 4312527-00;Order Number: 4313785-00; 4312527-00; 4311632-00;
PO Number: 6197078Order Number: 8622
Order Number: PORM#5336662/5335844
PO Number: 373852-003Order Number: 373852-003
Order Number: PORM#5336472
PO Number: NAOrder Number: 3187985
PO Number: NAOrder Number: 4314428-00
PO Number: NAOrder Number: 3188304
PO Number: PORM#5336710Order Number: PORM#5336710
PO Number: PORM#5336510Order Number: PORM#5336510
PO Number: 2179335Order Number: 2179335
PO Number: 14541100Order Number: 14541100
PO Number: 14549258Order Number: 14549258
PO Number: 14552141Order Number: 14552141
PO Number: 2179250Order Number: 2179250
PO Number: 3567718Order Number: 3567718
PO Number: 2174611-06Order Number: 2174611-06Customer Reference: 2179450
PO Number: 3188402Order Number: 3188402
PO Number: 2179434Order Number: 2179434
PO Number: 8279090Order Number: 8279090
PO Number: 9046186366Order Number: 9046186366
PO Number: 2179137Order Number: 2179137
PO Number: 6197386Order Number: 6197386
PO Number: 5763460Order Number: 5763460
PO Number: 1253523Order Number: 1253523
PO Number: Porm #5337032Order Number: Porm #5337032
Order Number: PORM#5337050
PO Number: PORM 5337134Order Number: PORM 5337134
PO Number: PORM#5337182Order Number: PORM#5337182
PO Number: 6197475Order Number: 6197475
PO Number: porm 4675650--4675648Order Number: porm 4675650--4675648Customer Reference: RGAs 18992--18987
PO Number: 4675541Order Number: 4675541
PO Number: 5336990Order Number: 5336990
PO Number: BG293570111Order Number: PORM#5336835
PO Number: 4324250-00Order Number: 4324250-00
PO Number: PORM 4675583Order Number: PORM 4675583Customer Reference: RGA R5531258
PO Number: 1454110Order Number: 14541100
PO # 6033413
Order Number: 6197403
PO Number: 318584Order Number: 318584
PO Number: NA
Order Number: 4321023-00; 4317883-00; 4318821-00
PO Number: PORM 4675462
Order Number: PORM 4675462Customer Reference: RGA W141860
PO Number: 8179061Order Number: 381603
PO Number: PORM 5336903Order Number: PORM#5336903
PO Number: 5212316Order Number: 5212316
PO Number: 5336585Order Number: PORM#5336585
PO Number: RMA 12855Order Number: RMA# 12855
PO Number: 3188080Order Number: 3188080
PO Number: PORM 5336954Order Number: PORM#5336954
PO Number: PORM 5336518Order Number: PORM 5336518
PO Number: 2944303Order Number: 2944303
PO Number: 6034520Sales Order #: NA
PO Number: 542381Order Number: 4674569Customer Reference: 542381
PO Number: RGA OI333844Order Number: PORM 4675436Customer Reference: RGA PI333844
PO Number: PORM 4675664Order Number: PORM 4675664Customer Reference: RGA -rma1463825
Order Number: 2179305
PO Number: 2944220Order Number: 2944220
PO Number: 2179371Order Number: 2179371
PO Number: 4675792Order Number: 4675792Sales Order #: 14560158
PO Number: 14563169Order Number: 14563169
PO Number: 14563166Order Number: 14563166Sales Order #: 1254678
PO Number: 14560155Order Number: 14560155
PO Number: 14563168Order Number: 14563168
PO Number: 4326755-00Sales Order #: NA
PO Number: 2179544Order Number: PO # 2179544
PO Number: 2179493Order Number: 2179493
PO Number: 6701800Order Number: 6701800Sales Order #: 12704
PO Number: 5336944Order Number: 5336944
Order Number: 4916965
Order Number: 4916965
PO Number: 5337143Order Number: 5337143
PO Number: 2179547Order Number: 2179547
PO Number: 1252845Order Number: 1252845
PO Number: PORM 4675703--4675702--Order Number: PORM 4675703--4675702--4675704--4675Customer Reference: RGA 0065278211
PO Number: 3567773Order Number: 3567773
PO Number: 4675818Order Number: 14563165Sales Order #: 4675818
PO Number: 1791900-00Order Number: 1791900-00
Order Number: 5337267
PO Number: 4317916-00; 4320519-00;Order Number: 4317916-00; 4320519-00; 4317927-00
PO Number: 125384Order Number: 125384
PO Number: 5337012Order Number: 318767
Order Number: PORM#5336982 & 53368
Order Number: 3567677
PO Number: PO 9021258 SO 62757218Order Number: PO 9021258; SO 62757218
PO Number: NAOrder Number: 4323940-00
PO Number: NAOrder Number: 4323742-00; 4323788-00; 4323053-00
PO Number: 4675582Order Number: 4675582
PO Number: PORM#5337133Order Number: PORM#5337133
Order Number: 533127
PO Number: 6197443Order Number: 6197443Customer Reference: P18069-002ARC
PO Number: 2177103Order Number: 2177103Customer Reference: 2179088
PO Number: 4188301Order Number: RMA'S# 8400029872, 8400029856, 84000
PO Number: 6194616Order Number: 6194616
PO Number: PORM 4675819Order Number: porm 4675819Customer Reference: RGA 0065278891
PO Number: PO 6197653Order Number: PO 6197653
PO Number: RMA# 12855Sales Order #: NA
PO Number: 2179600Order Number: 2179600
PO Number: BG295020611Order Number: 5336919
PO Number: BG295238011Order Number: 2179434Customer Reference: 2179697
Order Number: 5336832
PO Number: BG295427611Order Number: 1254689
PO Number: BG295205911Order Number: 5337563
PO Number: BG295205211Order Number: 4675951
PO Number: BG295255511Order Number: 1254825
PO Number: BG295221611Order Number: 4917005
PO Number: BG295186611Order Number: 4615858
PO Number: BG295284511Order Number: 4188481
PO Number: 5337353Order Number: 5337353
Order Number: 1254680
Order Number: 5335372
Order Number: 5337408
PO Number: NAOrder Number: 4323788-00; 4329589-00; 4329500-00
PO Number: NAOrder Number: PORM 4675925Customer Reference: RGA rma-r56037901
PO Number: NA
Order Number: 3188670
PO Number: 4675725Order Number: 4675725
PO Number: 1804346-00Order Number: 1804346-00
PO Number: 73199-00Sales Order #: NA
Order Number: 14569796
Order Number: 2944577
Order Number: 14581035
Order Number: RGA# Q 5638100
Order Number: 6605402
PO Number: PO 2179735Order Number: PO 2179735
PO Number: 14579830Order Number: 14579830
PO Number: 4676050
Order Number: 14578754
PO Number: 1254678
Order Number: 14563166
PO Number: RM 5337719Order Number: PORM#5337719
PO Number: 6197940Order Number: 6197940
PO Number: BG295870611Order Number: 1255034
PO Number: BG295626211Order Number: 8279174
PO Number: BG295836911Order Number: RMA# 400015079
PO Number: 6197962Order Number: 1248117

<colgroup><col style="mso-width-source:userset;mso-width-alt:9472;width:194pt" width="259"> <col style="mso-width-source:userset;mso-width-alt:13165;width:270pt" width="360"> <col style="mso-width-source:userset;mso-width-alt:9764;width:200pt" width="267"> </colgroup><tbody>
</tbody>
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
You must log in or register to reply here.

Similar threads

D
  • Question
Email information using an email address in a spreadsheet and supporting data from the spreadsheet
Replies
3
Views
911
jbeaucaire
jbeaucaire

Forum statistics

Threads
1,215,375
Messages
6,124,580
Members
449,174
Latest member
chandan4057

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top