reformatting data extract

A

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,053
Office Version
  1. 365
Platform
  1. Windows
folks,

i need to reformat a report extracted from our ERP. the report gives me accommodation revenue by fee type for each particular room type. if printed from the ERP, the report is formatted to print each accommodation class on new page with a column for each fee type. The number of bed days and accommodation classification are above the revenue table. Below the table is another table for Previous Months Adjustments.

Classification : ABC PATIENT FUND TOTALS
------- ---- ------
Current Month : Accom. Profess. Other Accom. Profess. Other Totals
Fees Fees
Bed Days : 456
Amount Raised 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Pre-Admission Deposits 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Receipts 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Pre-Admission Refunds 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Refunds 0.00 0.00 0.00 0.00 0.00 0.00 0.00
JNL:MONTH/S PRIOR ADJUSTMENT 0.00 0.00 0.00 0.00 0.00 0.00 0.00
---------- ---------- ---------- ---------- ---------- ---------- -----------
0.00 0.00 0.00 0.00 0.00 0.00 0.00
==================================================================================================================================
Previous Months Adjustments: Accom. Profess. Other Accom. Profess. Other Totals
Fees Fees
Bed Days : 108
Amount Raised 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Pre-Admission Deposits 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Receipts 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Pre-Admission Refunds 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Refunds 0.00 0.00 0.00 0.00 0.00 0.00 0.00
---------- ---------- ---------- ---------- ---------- ---------- -----------
0.00 0.00 0.00 0.00 0.00 0.00 0.00
==================================================================================================================================

<tbody>
[TD="align: right"] 1 [/TD]

[TD="align: right"] 2 [/TD]

[TD="align: right"] 3 [/TD]
[TD="align: right"][/TD]

[TD="align: right"] 4 [/TD]

[TD="align: right"] 5 [/TD]

[TD="align: right"] 6 [/TD]
[TD="align: right"][/TD]

[TD="align: right"] 7 [/TD]

[TD="align: right"] 8 [/TD]
[TD="align: right"][/TD]

[TD="align: right"] 9 [/TD]

[TD="align: right"] 10 [/TD]

[TD="align: right"] 11 [/TD]

[TD="align: right"] 12 [/TD]

[TD="align: right"] 13 [/TD]

[TD="align: right"] 14 [/TD]

[TD="align: right"] 15 [/TD]

[TD="align: right"] 16 [/TD]

[TD="align: right"] 17 [/TD]

[TD="align: right"] 18 [/TD]
[TD="align: right"][/TD]

[TD="align: right"] 19 [/TD]

[TD="align: right"] 20 [/TD]

[TD="align: right"] 21 [/TD]
[TD="align: right"][/TD]

[TD="align: right"] 22 [/TD]

[TD="align: right"] 23 [/TD]
[TD="align: right"][/TD]

[TD="align: right"] 24 [/TD]

[TD="align: right"] 25 [/TD]

[TD="align: right"] 26 [/TD]

[TD="align: right"] 27 [/TD]

[TD="align: right"] 28 [/TD]

[TD="align: right"] 29 [/TD]

[TD="align: right"] 30 [/TD]

[TD="align: right"] 31 [/TD]

</tbody>


I have included row numbering to indicate the standard number of rows between each data row.

What I want to do is to separate the fee amounts into columns and to use the Classification, month and number of bed days as columns themselves. Below is the end result i want to create.

ClassificationMonthBed DaysCurrent Month :Accom.Profess.OtherAccom.Profess.OtherTotals
FeesFees
ABCCurrent Month :Amount Raised
ABCCurrent Month :Pre-Admission Deposits
ABCCurrent Month :Receipts
ABCCurrent Month :Pre-Admission Refunds
ABCCurrent Month :Refunds
ABCPrevious Months Adjustments:Amount Raised
ABCPrevious Months Adjustments:Pre-Admission Deposits
ABCPrevious Months Adjustments:Receipts
ABCPrevious Months Adjustments:Pre-Admission Refunds
ABCPrevious Months Adjustments:Refunds
==================================================================================================================================

<tbody>
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]

<tbody>
</tbody>
[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"] 456 [/TD]

[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]

[TD="align: right"] 456 [/TD]

[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]

[TD="align: right"] 456 [/TD]

[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]

[TD="align: right"] 456 [/TD]

[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]

[TD="align: right"] 456 [/TD]

[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]

[TD="align: right"][/TD]

[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]

[TD="align: right"][/TD]

[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]

[TD="align: right"][/TD]

[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]

[TD="align: right"][/TD]

[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]

[TD="align: right"][/TD]

[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"] 0 [/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

The text to columns part is easy:
Code: 
Dim sFileName As String    'Show the open dialog and pass the selected file name to the String variable "sFileName"
    sFileName = Application.GetOpenFilename
    'They have cancelled.
    If sFileName = "False" Then Exit Sub
    Workbooks.OpenText Filename:=sFileName, Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
        Array(0, 1), Array(33, 1), Array(47, 1), Array(61, 1), Array(64, 1), Array(75, 1), Array(89 _
        , 1), Array(103, 1), Array(117, 1)), TrailingMinusNumbers:=True

its the moving of Classification, Month and Bed days that is vexing me.

<!-- Table easily created from Excel with ASAP Utilities (https://www.asap-utilities.com) -->
 
Last edited:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
no takers?

a bit more information. its a long report and i was mistaken about the number of rows per classification being static. In both the Current Month and Previous Months Adjustment sections there can be extra rows inserted which contain other financial information. The report header, which i didn't include in the extract above, signifies the start of each new page in the report. each page contains only one class of accommodation. the current month details are easy enough to pick up as they are always the same number of rows below the header. the Prev Months then is the harder part.

so, approach today is to see if i can write a loop that looks for each header then offsets down two rows to pick up the accomm class, down another three rows for the Current Month title, then down another three rows for the bed days, and finally another two rows for the amounts. then, do a find to get "Previous Months Adjustments" location and repeat the offsets.

its going to be ugly if i can get it to work. but, i've been called worse things:eeek:
 
Upvote 0
this so far:

Code: 
Sub test()

Dim CurrMth As String, Class As String, Beds As String, CMAmtRsd As String, PMBedDays As String, PMAmtRsd As String


Dim Rng As Range
Dim c As Range, d As Range
Dim pasteCell As Range
Dim DestSht As Worksheet


'Dim sFileName As String    'Show the open dialog and pass the selected file name to the String variable "sFileName"
    'sFileName = Application.GetOpenFilename
    'They have cancelled.
    'If sFileName = "False" Then Exit Sub
   ' Workbooks.OpenText Filename:=sFileName, Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
        Array(0, 1), Array(33, 1), Array(47, 1), Array(61, 1), Array(64, 1), Array(75, 1), Array(89 _
        , 1), Array(103, 1), Array(117, 1)), TrailingMinusNumbers:=True
 Set DestSht = Worksheets("AmtsRaised")
        
  CurrMth = Trim(Right(ActiveSheet.Range("c2"), 6))
  
  Set Rng = ActiveSheet.Range("a1", Range("a1048576").End(xlUp))
With Rng
    Set c = .Find("DEBT2.P33", LookIn:=xlValues)
    Set d = .Find("Previous Months Adjustments:", LookIn:=xlValues)
            
    If Not c Is Nothing Then
        firstAddress = c.Address
        
        Do
        Class = Trim(Right(c.Offset(2, 0).Value, Len(c.Offset(2, 0).Value) - InStr(c.Offset(2, 0).Value, ":")))
        Beds = Trim(Right(c.Offset(8, 0).Value, Len(c.Offset(8, 0).Value) - InStr(c.Offset(8, 0).Value, ":")))
        CMAmtRsd = Trim(c.Offset(10, 7).Value)
        PMBedDays = Trim(Right(d.Offset(3, 0).Value, Len(d.Offset(3, 0).Value) - InStr(d.Offset(3, 0).Value, ":")))
        PMAmtRsd = Trim(d.Offset(5, 7).Value)
             
      '///Set the variable to the first empty cell in Row 2
     
     With DestSht
     Set pasteCell = .Range("b1048576").End(xlUp).Offset(1, 0)

     'Copy and paste
     pasteCell.Value = CurrMth
     pasteCell.Offset(0, 1).Value = Beds
     pasteCell.Offset(0, 2).Value = Class
     pasteCell.Offset(0, 3).Value = CMAmtRsd
     pasteCell.Offset(1, 0).Value = CurrMth
     pasteCell.Offset(1, 1).Value = PMBedDays
     pasteCell.Offset(1, 2).Value = Class
     pasteCell.Offset(1, 3).Value = PMAmtRsd
     
     End With
     '///Sheets("Sheet1").Range("A2:B10").Copy Destination:=pasteCell
        
            Set c = .FindNext(c)
            Set d = .FindNext(d)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With


End Sub

I have changed the final result to pulling just the amounts raised per accommodation class and posting to the next page:



<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"] Month [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"] Bed Days [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"] Classification [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"] Amount [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] Current Month [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"] 43664 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"] 63 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"] ABC [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"] 0 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] Previous Months Adjustments: [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"] 43664 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"] ABC [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"] 0 [/TD]

</tbody>


However, when i am having trouble with the find inside a find, i think. the first run through gives perfect results but when resetting c and d, they both become the same. what have i done wrong?

I am going to post a new question focusing on this double use of FindNext so it doesn't disappear in this thread.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Managing Payment table separate from billing, calculating lps on every received payment based on criteria.
Replies
1
Views
586
NaushadAnwar
N
D
  • Question Question
Macro Requested to pull sections based upon number into blank excel sheet
Replies
0
Views
618
Darryproduct
D
K
  • Solved
Help with Macro, as it is not extracting all the data.
Replies
7
Views
956
Fluff
Fluff
M
  • Question Question
Sorting Data from Multiple Pages by Date Based on Description
Replies
1
Views
652
MickG
M
K
  • Question Question
Any Suggestions to improve....
Replies
1
Views
571
Smitty
Smitty

Forum statistics

Threads
1,223,228
Messages
6,170,876
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top