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]

Irony is that with the way I'm currently copy/pasting (not using a macro), the formula can just be a MID formula.

Obviously though I want something more robust. I really need a macro that will just take everything I need and wrap it up nicely for me. Heh. Anyway, any help would be appreciated.

 

[Alex881]

Actually, is there a way to copy data from a column into another column, based on certain criteria?

For example, data in column C that starts with an 'S' will go into column K, starting in cell K5. Anything that doesn't start with an 'S' will go into column J, starting with J5. Does this make sense?

I want a formula that I can have in columns J and K that will auto-populate with data once I paste in my original data..hope this makes sense.

 

[Peter_SSs]

I really need a macro that will just take everything I need and wrap it up nicely for me. Heh. Anyway, any help would be appreciated.

See if this comes anywhere near what you want. It is a bit hard for me to be sure as I think many of the spaces in your data have disappeared when you pasted the sample data into post 1.

Anyway, give it a try with a copy of your data.
I have assumed all that data from post 1 is actually in column A.

Sub Extract_Data()
  Dim a As Variant, b As Variant, CurrCust As Variant
  Dim i As Long, k As Long
  Dim s As String
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 2)
  For i = 2 To UBound(a) - 1
    s = LCase(a(i, 1))
    Select Case True
      Case s Like "address:*"
        CurrCust = Mid(a(i - 1, 1), 6, 5)
        If Not CurrCust Like "#####" Then CurrCust = "Check??"
      Case s Like "order status via card*"
        k = k + 1
        b(k, 1) = CurrCust
        b(k, 2) = Mid(a(i + 1, 1), 18, 8)
    End Select
  Next i
  Range("M2:N2").Resize(k).Value = b
End Sub

 

[Alex881]

That's right - all the data are in column A. Every time I paste data into my Excel sheet from this software program, the data go into column A.

I tried using your Macro on some sample data. I just got an error that states '400'.

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

Also, see the imgur link for a picture of what that data look like (one example):

https://imgur.com/a/Yo6OUhT

 

[Alex881]

Right now I'm using this to extract the correct data from each row (assuming today's date is the order date). I don't know how (yet) to find anything with a '**/**/**' type format.

=IF(SUMPRODUCT(--ISNUMBER(FIND("009,999",A5)))>0,MID(A5,6,5),"")&IF(ISNUMBER(FIND(" 09/13/19 ",A5)),MID(A5,18,8),"")

I copy the above formula down an entire column - it makes the correct decisions. It leaves all cells it doesn't need to touch blank, while extracting a customer's ID # and a customer's order #.

Having said that, it is possible that a customer's ID # will show up twice - this is because the software program makes new pages and if part of the data is on one page and part is on another, then the data repeat in that regard (if that makes sense). It's not a huge issue, but it's something I'm thinking about..

EDIT: Nevermind, this formula is better (using search):

=IF(SUMPRODUCT(--ISNUMBER(FIND("009,999",A15)))>0,MID(A15,6,5),"")&IF(ISNUMBER(SEARCH(" **/**/19 ",A15)),MID(A15,18,8),"")

 

[Alex881]

Further simplified the above edited formula to:

=IF(ISNUMBER(FIND("009,999",A5)),MID(A5,6,5),"")&IF(ISNUMBER(SEARCH(" **/**/19 ",A5)),MID(A5,18,8),"")

This works - it extracts the correct data everytime.

Now I need something robust that will take all this data and put it in two columns (one column that has the customer's five digit ID # and a column to the right of that one that has the appropriate eight digit alphanumeric string).

 

[Alex881]

I've separated the data that show in that one column by using two formulas:

(1): =IF(LEN(K5)=5,K5,"")

&

(2): =IF(ISNUMBER(SEARCH("S*******",K5,1)),K5,"")

This now makes my data show up in two columns. Now it's a matter of getting the appropriate data to show side by side..if that makes sense..

 

[Alex881]

Nevermind. I got help and was able to figure out the entire thing. Not using a macro, just several columns worth of formulas and then a PivotTable that I have to refresh when I paste in new data to column A.

 

[Peter_SSs]

.. was able to figure out the entire thing.

Glad you got something that worked for you.

Do you have to do this task regularly? I'm pretty sure we would be able to get a macro going for you with a bit more effort.

 

[Alex881]

I do this task several times a day. However, the way it works now is fantastic. All I really do is paste data then refresh a PivotTable. I genuinely appreciate the offer for a macro, but at this point I'm very satisfied! Thank you so much for the assistance though.