Extract data from separate worksheet

searnold56

New Member
Joined
Mar 8, 2013
Messages
7
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.

Notes:
  • 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
xxxxxx
xxxxxx
xxxxxx
xxxxxx
XXXXXX
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

<tbody>
</tbody>

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
Balance

<tbody>
</tbody>

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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Watch MrExcel Video

Forum statistics

Threads
1,099,017
Messages
5,466,060
Members
406,462
Latest member
I10V3xl

This Week's Hot Topics

Top