Macro to extract data from report

[Alex881]

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):

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

 

[Alex881]

Update: I tried solving this entire thing using a formula instead of a macro. I've ran into several problems though, and have yet to find my magic formula.

I'm using

=IF(ISNUMBER(SEARCH("01",A5)),MID(A5,6,5),"")&IF(ISNUMBER(SEARCH("HD",A5)),MID(A5,18,8),"")

So, I tried this for an order that has an '01' under the 'A/R REP' (as an example).

When I drag this formula down the entire order, I'm left with the correct information (it brings out the proper 5 digit number and the proper 8 digit alphanumeric string). And, appropriately leaves the other cells in the column blank.

However, when I tried to build a formula that would do any two digit number between 01 and 15 (inclusive), I wasn't able to get it to work. The formula gave some false positives and other errors.

I was trying to make the formula any two digit number between 01 and 15 (inclusive), because that's what the 'A/R REP' field will contain. So the formula would have to get me the correct information in any of those scenarios.

If anyone can help, it would be greatly appreciated.

 

[Alex881]

Also, the formula in my post above is also an issue because of the dates in the row that contain the 'HD' text. It will see the '09' that's apart of the date and this then messes up the formula.

 

[Alex881]

Update: Tried the original formula with two possibilities (01 & 09). Made a second formula in the column to the right that does this:

=IF(ISNUMBER(SEARCH("/",K5)),RIGHT(K5,LEN(K5)-FIND("/",K5)),K5)

Pasted this down the column for the order and it made the correct decisions. Interesting...this might be bringing me closer to a solution.

 

[Alex881]

The biggest issue I'm seeing with my formula is that if I say =IF(ISNUMBER(SEARCH("06",A5))...etc....the formula will think that '069999' is valid. I don't understand how to make sure Excel only finds exactly '06' within a row that has other data in it.

 

[Alex881]

Basically, the formulas I came up with only work in certain scenarios. Anybody?

 

[Alex881]

New formula I'm trying:

=IF(SUMPRODUCT(--ISNUMBER(FIND(BYTE,A5)))>0,MID(A5,6,5),"")&IF(ISNUMBER(FIND("HD",A5)),MID(A5,18,8),"")

This is decent - seems to work, but will also give me a few rows of data that I don't need (for example, it will see '015668' in another row and do my MID function on that row. Not terrible - at least it seems to be working for the ones I need. I still have to do other formulas to derive a final result...hmm...

NOTE: 'BYTE' in the formula above refers to a table with values '01-15', inclusive.

 

[Alex881]

So far I think I can get a decent amount of stuff done, but if there are multiple orders from the same customer the spacing is off and the formulas won't work the same.

So, I'm taking =IF(SUMPRODUCT(--ISNUMBER(FIND(BYTE,A5)))>0,MID(A5,6,5),"") and putting that in a specific row.

Then I'm taking IF(ISNUMBER(FIND("HD",A5)),MID(A5,18,8),"") and putting that in a specific row.

Then it's a matter of taking these formulas down the column.

This might work - however, it just won't work in the case where a customer has ordered more than once because that messes up the spacing.

Anyway...

 

[Alex881]

Have worked more today, only thing I can say is that it would be really nice to somehow get the correct data all the time. I can get some of the data, but not always since the spacing between the orders is variable. Some kind of macro will probably have to be created in order for me to get my data in the way I want. It would be greatly appreciated if someone can assist on this matter.

 

[Alex881]

Anyone have any ideas? Sorry for all the bumping of my own thread.