Extract data from separate worksheet


New Member
Mar 8, 2013
First, apologies for vagueness as I don't really know how to approach this.

I am exporting reports that are providing customer info that I need to consolidate into single lines. Unfortunately, the export is designed in a pdf/printable report format. The objective is to copy updated data into an input worksheet that then reformats the customer information in single lines that I can filter to create follow-up activity. There is both a header and footer on each "page" which I will try to represent below.

  • Each "page" has 8 rows on top (shown below) with the report name, business name, and address, customer type, then header row.
  • Each "page" has 4 rows on the bottom, 3 blank and 1 with the printed time/date and page # of #
  • Customer detail is framed in 5x20 sequences, but periodically the customer type changes so the number of customers per page is not consistent.
  • Each 5x20 customer block is the same, except swap out the phone type (ex: cell could be home, the blank cell between and some cells may be blank. Ideally my consolidated row returns a blank value if the cell is blank.
Report namexxxxxx
Customer Type: Sample
CustomerContact InformationActivityBalancesLast Payment
Last, FirstCell :123-456-7890First: 05/19/2016Services:$0.0005/14/2019
123 Main St
City, ST zipWork :123-456-7890Last: 06/18/2019Discount:$0.00$185.00
Email :email@example.comNext: 06/27/2019Account:$0.00
Printed:Wednesday, June 19, 2019 9:33:50 AMPage1Of332


I would like to return data under these headers
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
NameEmailCellWorkLast ActivityNext ActivityAccount


While a direct answer to this is welcome, even suggestions as to functions I should be looking into would be terrific.

Thank you!
Last edited:

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...