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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...