Microsoft Excel - Extracting data from cells in multiple ranges

Asked By mario
23-May-11 08:55 AM
After converting PDF to TXT and importing it in Excel this is an example.
Each record starts with PO Line Item Number and ends with Tax Article Number.
Considering conversion, sometimes there is a blank row or a row with meaningless data.
The only data I am looking for is in the PO Line Item Number in the 3rd column: 44.00 and 4th column: 16.98
and
in Brief Description row 2nd column: 04/03/2011 - ITEM1 - INDUSTRIAL 3

For the example below, I would like to extract data into a spreadsheet that looks like:

Column A                           Column B    Column C
04/03/2011 - ITEM1 - INDUSTRIAL 3       44.00     16.98
04/03/2011 - ITEM1 - INDUSTRIAL 3         0.25     25.54

I suspect that this has to be a VB script that will recognize data 'start' and 'end' in order to correctly extract cells from each range and then do it until it reaches the end of the file.

If somebody had something similar, perhaps you could share. Thank you

Example:
PO Line Item Number  000001 Quantity  44.00 Unit Price  16.98 Item Total before Tax
Unit of Measure  HR EA Price per UOM  
Tax Code  Taxation country Code  Tax Rate  Tax Amount
Buyers Part Number  Sellers Part Number  Billing Period Start Date  Billing Period End Date
Delivery Order Number  Airway Bill Number  
Brief Description  04/03/2011 - ITEM1 - INDUSTRIAL 3
Invoice Item Text  7696 - 13051800 
Invoice Item Description
Standard Reason for Zero (0) Tax
Reason for Zero (0) Tax
Tax Article Number
PO Line Item Number  000001 Quantity  0.25 Unit Price  24.54 Item Total before Tax
Unit of Measure  OT EA Price per UOM  
Tax Code  Taxation country Code  Tax Rate  Tax Amount
Buyers Part Number  Sellers Part Number  Billing Period Start Date  Billing Period End Date
Delivery Order Number  Airway Bill Number  
Brief Description  04/03/2011 - ITEM1 - INDUSTRIAL 3 
Invoice Item Text  7696 - 13051800 
Invoice Item Description  
Standard Reason for Zero (0) Tax  
Reason for Zero (0) Tax  
Tax Article Number  
  Jackpot . replied to mario
23-May-11 01:20 PM
Hi Mario


With your data in active sheet try the below macro.  If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook. Activate the data sheet
--Run macro from Tools|Macro|Run <selected macro()>
--The resultant data should go to Sheet2 as mentioned in the code.



Sub Macro()
Dim wsTarget As Worksheet, lngRow As Long, lngTRow As Long
Dim varValue1 As Variant, varValue2 As Variant
  
Set wsTarget = Sheets("Sheet2")
  
For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row
  Select Case Trim(Range("A" & lngRow))
    Case "PO Line Item Number"
    varValue1 = Split(Range("C" & lngRow), " ")(0)
    varValue2 = Split(Range("D" & lngRow), " ")(0)
    Case "Brief Description"
    lngTRow = lngTRow + 1
    wsTarget.Range("A" & lngTRow) = Range("B" & lngRow)
    wsTarget.Range("B" & lngTRow) = varValue1
    wsTarget.Range("C" & lngTRow) = varValue2
  End Select
Next
  
End Sub
  mario replied to Jackpot .
23-May-11 02:05 PM
Hello Jackpot - thanks for prompt reply.
I created macro() and as a result I get a single column. I need also column B and C that will contain corresponding numerical values as I indicated in my initial request. These numerical numbers are in the "PO Line Item Number" line

Column A                                              Column B     Column C

 04/03/2011 - PC BHAVSAR - INDUSTRIAL 3
 04/03/2011 - PC BHAVSAR - INDUSTRIAL 3 
 04/03/2011 - Y PATEL - INDUSTRIAL 3
 04/10/2011 - PC BHAVSAR - INDUSTRIAL 3 
 04/10/2011 - PC BHAVSAR - INDUSTRIAL 3 

I am not sure if you can see it on my original inquiry page. Here it is:
A B C D
PO Line Item Number  000001 Quantity  44.00 Unit Price  16.98 Item Total before Tax
Unit of Measure  HR EA Price per UOM  
Tax Code  Taxation country Code  Tax Rate  Tax Amount
Buyers Part Number  Sellers Part Number  Billing Period Start Date  Billing Period End Date
Delivery Order Number  Airway Bill Number  
Brief Description  04/03/2011 - PC BHAVSAR - INDUSTRIAL 3
Invoice Item Text  7696 - 13051800 
Invoice Item Description
Standard Reason for Zero (0) Tax
Reason for Zero (0) Tax
Tax Article Number



Or maybe I am doing something wrong.
  Jackpot . replied to mario
23-May-11 02:08 PM
Hi Mario

Try this version


Sub Macro()
Dim wsTarget As Worksheet, lngRow As Long, lngTRow As Long
Dim varValue1 As Variant, varValue2 As Variant
    
Set wsTarget = Sheets("Sheet2")
    
For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row
  If Trim(Range("A" & lngRow)) Like "PO Line Item Number*" Then
  varValue1 = Split(Range("C" & lngRow), " ")(0)
  varValue2 = Split(Range("D" & lngRow), " ")(0)
  ElseIf Trim(Range("A" & lngRow)) Like "Brief Description*" Then
  lngTRow = lngTRow + 1
  wsTarget.Range("A" & lngTRow) = Range("B" & lngRow)
  wsTarget.Range("B" & lngTRow) = varValue1
  wsTarget.Range("C" & lngTRow) = varValue2
  End If
Next
    
End Sub
  mario replied to mario
23-May-11 02:58 PM

Jackpot - I found the issue.

Numerical values started with a single blank. When I removed leading and trailing spaces - your macro worked like a charm!

THANK YOU!

Regards, Mario

  mario replied to mario
13-Jul-11 11:28 AM
Hi Jackpot,

As I anticipated the source guy changed a format slightly and now what used to be in the Column C is in Column D.
In the script I changed:
from: varValue2 = Split(Range("D" & lngRow), " ")(0)
to:   varValue2 = Split(Range("E" & lngRow), " ")(0)

And the script produces an error message - Runtime Error '9': Subscript out of range.

However the script produces the listing I need.

How to fix Runtime Error 9? Please advise. Thanks.

Sub Macro()
Dim wsTarget As Worksheet, lngRow As Long, lngTRow As Long
Dim varValue1 As Variant, varValue2 As Variant
    
Set wsTarget = Sheets("Sheet2")
    
For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row
  If Trim(Range("A" & lngRow)) Like "PO Line Item Number*" Then
  varValue1 = Split(Range("C" & lngRow), " ")(0)
  varValue2 = Split(Range("E" & lngRow), " ")(0)
  ElseIf Trim(Range("A" & lngRow)) Like "Brief Description*" Then
  lngTRow = lngTRow + 1
  wsTarget.Range("A" & lngTRow) = Range("B" & lngRow)
  wsTarget.Range("B" & lngTRow) = varValue1
  wsTarget.Range("C" & lngTRow) = varValue2
  End If
Next
    
End Sub
Create New Account
help
NPD: Free Blu-Ray Players Contributed to Weekly Hardware Lead - Number Misleading. Windows 7 http: / / www.highdefdigest.com / news / show / Industry_Trends / High-Def_Retailing / NPD:_Free_Blu-ray_Players_Contributed_to_Weekly_Hardware_Lead Blu-ray players accounted for over 30% of sales, as they were offered free to buyers of the company's LCD televisions. Sony - - also accounting for one-third of sales - - had were almost non-existent, as the company did not offer any special deals to TV buyers. The NPD figures did not include Sony's PlayStation 3 or Microsoft's HD DVD sales data - - but said there was some drop in HD DVD player sales. For its part, the HD DVD Promotional Group told BetaNews the weekly sales data was from before the how NPD counts sales. It receives its data from point-of-sale systems from a number of retail outlets across the United States. He would not say how many retailers send from the likes of Amazon, where Toshiba's HD-A3 HD DVD player is the number one seller in the DVD player category and number 14 in all of electronics. By contrast, standalone Blu-ray players do not make the inboxer / petition / internet.asp keywords: NPD:, Free, Blu-Ray, Players, Contributed, to, Weekly, Hardware, Lead, -, Number, Misleading. description: www.highdefdigest.com / news / show / Industry_Trends / High-Def_Retailing / NPD:_Free_Blu-ray_Players_Contributed_to_Weekly_Hardware_Lead / 1403 www
a bar code that says "CD SET: X09-22117". Unfortunately, a Google search on that part number shows nothing, so I am hoping someone from Microsoft can use that number to trace the manufacturing part number to some actual product, and help me definitively identify what we received. What would be retail, OEM, open license, etc, but per an earlier thread Microsoft apparently doesn't give buyers any tools for identifying any piece of information about product keys. I don't want for status of keys, and 95% of those keys end up getting sold to multiple buyers, or being previously registered, then Microsoft has a clear trail leading back to the culprit I do activate it, then I guess the System Properties | General tab will have a number that would start with an MPC that is coded to tell you if it retail
Enabling alternate symbols and text. . . Windows 7 It's not my choice, it's the buyers choice. . . and I could use some help. We're talking about simple typing here, not to provide special characters (not just Spanish) without using any extra software: 1. Use Alt-number key combinations 2. Use Character Map. 3. Use the US International keyboard. 3. Use whatever Dallas, Texas (214) 662-9901 _ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ http: / / www.januarysound.com Sorry Ken, Turning on the Number Lock was the most expiditious method, and should have been (IMHO) the first suggestion and and teaching the client new key combinations is hardly as acceptable as turning on the number lock switch. I apologize for the lack of brain-power on my part, as Number Lock has always been a BIOS default for me, and I've never encountered this
what used to be) simple inventory control database to replace the old Cardex system the buyers in my company use to track product inventory. Here is what I have so farâ quite sure what you have in mind as a Release. In most purchasing Similar. . . . the buyers place one order, with one POnumber, on one date. . . . but, the PO may say 50 of part A to be shipped 10 a month for the next 5 months. Those are what then can that also be my InvTransID? or does it have to be another unique number? Also, in my table relationships window (I need the visual) will my InvTrans table only TransID) in the InvTrans table. My problem is that this results in Key violations, the number 5 could be assigned to the deductionsID and the receivedGoodsID etc. . . . I know one way what used to be) simple inventory control database to replace the old Cardex system the buyers in my company use to track product in