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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,236
Messages
5,509,981
Members
408,768
Latest member
ndg4405

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...
Top