Macro to extract data from report

Alex881

New Member
Joined
May 6, 2019
Messages
39
Hello,
I have a tedious task of reviewing data in a piece of software. I only need two pieces of data in order to do my review, but there are a lot of data that show up.

I only need the data shown below (data are fictional):

xxxxx08a.p 7.8.50 *ANALYSIS REPORT Date: 09/09/19
Page: 1 P999999 Time: 14:57:59
A/R CAR BILL-TO NAME CITY ST PHONE CR LIMIT HOLD REASON
REP NBR CODE
WW 17999 BOGDAN HOME CART SAN ANTONIO TX 555-555-5555 009,999,999 yes C11799917999S7777777
Address: 9999 119TH STREET Zip: 5555525000S8888888
55555S7666666
Ord Date Sales Bill-To Purchase Order Doc Amt S/O Ship Credit55555S7366666
Order Status Via Card
09/09/19 S7777777 1000000 iv000000g8 2,250.00 HD FXGRS7777777
Contact Name:donald duck/ purch 555 555 5555
C/S Rep: Daffy Doodles
---------------
CAR Hold Total: 2,250.00
CAR Commit Total: 0.00
------------------------------------------------------------------------------------------------------------------------
ZZ 25000 SUPPLY'S INC NY 999.999.999 009,999,999 yes C125000
Address: 19999 BIG AVENUE Zip: 99999
Ord Date Sales Bill-To Purchase Order Doc Amt S/O Ship Credit
Order Status Via Card
09/09/19 S8888888 10999999 6049999 4,157.85 HD FXGRS8888888
Contact Name:EMAIL JOHN DOE JOHNDOE@GMAIL.COM
C/S Rep: Amelia Prayers
---------------
CAR Hold Total: 4,157.85
CAR Commit Total: 0.00
------------------------------------------------------------------------------------------------------------------------
Page: 2 PRDDDD Time: 14:57:59
A/R CAR BILL-TO NAME CITY ST PHONE CR LIMIT HOLD REASON
REP NBR CODE
hh 55555 CALIFORNIA INC SAN FRANCISCO CA 555-555-5555 009,999,999 yes C155555
Address: BIG ROAD 120 Zip: 66666
1666 BIG CITY AVENUE
Ord Date Sales Bill-To Purchase Order Doc Amt S/O Ship Credit
Order Status Via Card
09/09/19 S7666666 16666666 099999BERT 2,342.34 HD FXSURES7666666
Contact Name:EMAIL JANE APOSTLE
C/S Rep: JARDIN HARPER
Ord Date Sales Bill-To Purchase Order Doc Amt S/O Ship Credit
Order Status Via Card
09/09/19 S7366666 17777777 090919BERNIE 7,450.41 HD FXGRS7366666
Contact Name:EMAIL JANE APOSTLE
C/S Rep: JORD CARVER
---------------
CAR Hold Total: 9,792.75
CAR Commit Total: 0.00

https://imgur.com/a/xsTvqlx <--- The first image shows how the first piece of data is 6 characters to the left of the start of the data, and it is 5 characters long. I used =MID(A5,6,5) in order to extract the 5 digit numbers for each order.

Column K in the images above is colored orange. That's where I manually input formulas. Columns M and N have some data colored yellow. That's how I want the data to be presented once the macro is ran. I manually inputted that data.

The second image in the link above shows how the second piece of data is 8 characters long and starts at the 18th character. I used =MID(A10,18,8) in order to extract the 8 alphanumeric string for each order.

The yellow section shows how I want the data to be arranged once the Macro is complete. I want the appropriate 5 digit number in one column, and in the cell directly to the right, the applicable 8 digit alphanumeric string.

As you can see, in some cases, the customer has placed two orders so the pattern isn't exactly the same. In this case, I want the 5 digit number repeated and the appropriate 8 digit alphanumeric string for each order.

NOTE: In some instances, either the 'WW', 'ZZ', etc will be blank. It is always two digits or two letters long. Also, in some instances, the 5 digit number will be blank (can be blank by itself or in addition to the 'WW'/'ZZ'/etc). In cases like this, would it be possible to have the macro tell me that it's detected an issue and where? Or just note that it found blank data.

Thank you very much.

<tbody>
</tbody>
 
Last edited:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

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