question on re-formatting a report

How can I get a report that's copied to Excel in this format
(originally an .rtf file):

                Division A
                Accounting
                Invoice#
                123          $100
                567          $345

                Division A
                Marketing
                Invoice#
                456           $50
                877           $2000

to look like this:

               Division A   Accounting   123    $100
               Division A   Accounting   567    $345
               Division A   Marketing     456    $50
               Division A   Marketing     877    $2000

It's a massive report that can go up to 200 pages.  Any assistance is
greatly greatly appreciated.

Thanks
Nina
0
pi567 (4)
5/20/2009 4:52:28 PM
excel 39879 articles. 2 followers. Follow

1 Replies
520 Views

Similar Articles

[PageSpeed] 58

Try this:

- Copy the Data to columns D and E of a new sheet.
- Copy column D to columns B and C as well
- Select Cols A and B, press: [ctrl + \] [ctrl+click cell A1] [ctrl + D]
- Select Cols B and C, press: [ctrl + \] [ctrl+click cell A1] [ctrl + D]
- Select Col E, press F5>Special>Blanks. Edit>Delete>Entire Row

The VBA equivalent, with data in Sheet1!A:B of a new workbook, is:

Sub Reformat()
    Selection.Copy Sheets("Sheet2").Range("D1")
    Sheets("Sheet2").Select
    Columns("D:D").Copy Columns("B:C")
    Union(Columns("A:B").RowDifferences(Range("A1")), Range("A1")).FillDown
    Union(Columns("B:C").RowDifferences(Range("B1")), Range("A1")).FillDown
    Columns("E:E").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Columns("A:A").Delete Shift:=xlToLeft
End Sub


"pi567" wrote:

> How can I get a report that's copied to Excel in this format
> (originally an .rtf file):
> 
>                 Division A
>                 Accounting
>                 Invoice#
>                 123          $100
>                 567          $345
> 
>                 Division A
>                 Marketing
>                 Invoice#
>                 456           $50
>                 877           $2000
> 
> to look like this:
> 
>                Division A   Accounting   123    $100
>                Division A   Accounting   567    $345
>                Division A   Marketing     456    $50
>                Division A   Marketing     877    $2000
> 
> It's a massive report that can go up to 200 pages.  Any assistance is
> greatly greatly appreciated.
> 
> Thanks
> Nina
> 
0
lori1 (126)
5/21/2009 5:07:01 AM
Reply:

Similar Artilces:

How to adjust report width
Hi, I am trying to adjust the SOP Short invoice form width. Is it possible to extend the width of this report and how can I do this. Thanks in advance, -- Elaine Typically, the reports are set to read the printer settings for there width. You can change the report to print Landscape. On the Report Definition window, lookm at the Page Orientation section. One note about selecting the printer settings. If all your printers are not setup exactly the same, it could cause the report to print differently depending on which printer you send it to. -- www.fmtconsultants.com "Elaine...

After Re-Install
HELP!!! After a crash and a re-install of Office we are now getting user name and password errors in Outlook 2002. We can access the same accounts via a www interface with the same account information. PLEASE help. I've downloaded all the service packs for XP and Office and I've tried un-installing and re-installing including deleting all of the profile information I can find and deleting Outlook's registry, but to no avail. I'm obviously missing something. Any help would be fabulous. Please e-mail Monica@ViaSollertia.co.nz Thanks so much!!! Monica ...

how to select multiple text boxes in excel for formatting
I am trying to select multiple text boxes for formatting the font but seem unable to select all of them other than to click on each one individually. Is there an easy way to select all of the text boxes at once? To select multiple objects on the sheet -- Click on one object Hold the Ctrl key, and click on additional objects To select all the objects on the sheet -- Choose Edit>Go To, click Special Select Objects, click OK Or, to work with specific objects, you can add the 'Select Multiple Objects' tool to one of your toolbars: Choose Tools>Customize Select the Commands tab...

Magnify glass on report preview
Hi, I have a multi page report that I veiw using acPreview. When the report comes up my cursor is a magnify glass instead of the arrow. If I click on the next page arrow the cursor then changes to the arrow. Why? Thanks, ...

Clipart question
I upgraded to Pub2002 from Pub98. I work from home and my employer upgraded my Office 97 to Office 2000. Well, now when I try to get to my clipart from the Clip Gallery Frame plug-in, it wants the Office 2000 disk, which I don't have (my employer keeps all disks). Is there somewhere in Pub2002 that I can change the path from the Office 2000 disk to my Pub2002 disk? When I try to do the "Change Location" option, it looks for that specific clip art file and my disk has 6 or 7 folders of clip art. Thanks, Kristie PUB2000 Error Message: The Feature You Are Trying to Use Is No...

Add Invoice Number to Report
I have created a delivery table that contains all of the usual fields , Pickup, Delivery, Billing, NoPeices, Weight... etc. The table is updated with current delivery information daily. At the end of the week I create a report grouped on Billing customer that prints an invoice with all of the unprinted deliveries the customer is paying for... the report works fine. What I need to figure out is how to assign a unique invoice number (autonumber) to the report that will be associated with each Billing customer for that print period. Can I somehow generate a unique number at the time I ...

with ref to the dialog editor question
A few days ago I posted a question about "dialog editor." (please see below) Perhaps I was asking the wrong question. I'm trying to create a form to input data similar to the one found by doing a <DATA-Form> but with my own comments etc and using the targets as different parts of the spreadsheet. Please can anyone give me a clue on how to start Regards DAVE Bristol UK "Can someone please help. I am trying to start the dialog editor. my book says :- 1 In microsoft excel for windows, choose Run from the microsoft excel control menu. 2 Select the dialog editor o...

Re: how to make a stealth application
"Joseph M. Newcomer" wrote in message " Note that I cannot install software on my email machine when logged in as 'email'. I have to log off, log in as admin, install the software, and then log back in as 'email'. Worth pointing out that some children who build antispam software only allow the software to run for the installing userid, not have it installed system-wide by a sysadmin. A waste of money." Sir, I am 100% agree with you in above case, but i am just puting my point here, Please i am very-2 grateful if you point out anything that i have ...

Period Sales Analysis Report Error
I updated to ver 10 from 9 SP2 a couple weeks ago and everything has worked relatively well (Thanks to all newsgroup members who assisted) I am getting an error "Error in equation 'Net Sales'". Has anyone else seen this and what did you do? I'm guessing this is a modified report? Open the report in Report Writer and check out the Calculated field 'Net Sales'. You will probably find an error there. If I can't figure it out just by looking, I'll look at the v 9 report to see what the calc was trying to come up with and then correct the formula in v ...

GUID/Outlook format link giving error "Unable to open the seleted folder or item"
Hi, I have created custom forms in multiple public Outlook folders and when I send the GUID type link to the user, they receive an error: "Unable to open the seleted folder or item". The same happens if I send the Outlook type link. However, if I ask the user to go to the folder and find the appropriate posting, then the form opens and after that the link works, too. Is this due to some Exchange server setting? My clients are not the type that tolerate opening the form the long way, they want a one click solution. Please help, this is driving my team crazy. Any ideas are welcome...

CString Question #3
How do you go about passing information from one class to a dialog box class. I can do it useing char pointers , i was wanting to try CString pointers. Am i missing something eg CDeleteConformation::CDeleteConformation(char *DeleteTableName) : CDialog(CDeleteConformation::IDD) I know that i can pass cstring into but how do i pass it back as a pointer to an address. so i can change it. CDeleteConformation::CDeleteConformation(CString DeleteTableName) : CDialog(CDeleteConformation::IDD) thanks kevin_mfc@ihug.com.au >eg >CDeleteConformation::CDeleteConformation(char *DeleteT...

IMS
Hello All: I don't know if it is because I never closely monitored this queue or the server is having issues, but in the Internet Mail Service - Outbound Messages Awaiting Conversion Queue, I am seeing a rather large buildup of messages, most of which are mailer-daemon@ourdomain.com I am also seeing messages destined for internal users.... Can anyone tell me is thi sis normal? I also have a user complaining that someone outside of the company keeps getting a email from her over and over again. I am wondering if the 2 situations here are related or if I have 2 seperate issues. At any ra...

chart question #4
Can anyone please give me some guideline on how to draw a chart with y-axis is the count of the item in x-axis? I am using Excel 97. Thanks. I am not certain I fully understand your question, but I am going to give it a stab. Excel charts are very flexible, and I think you need to keep in mind that you need to have the right data in place to draw the chart. I.e. don't focus on the chart itself, focus on the data in the spreadsheet you are trying to chart, and once that is in place, your chart will look the way you want. -- ChrisJ Omaha "00KobeBrian" wrote: > Can...

Unmatched Query Question
I have a database that I'm using to pull the names of students who have applied for graduation from an Excel spreadsheet, that is updated weekly, into an Access Table. Right now, I'm using an unmatched query to pull Student IDs who have not previously appeared on the Excel spreadsheet. At first, I was a little worried about this because if a student applies for graduation with a dual degree, their name shows up on the Excel Spreadsheet twice (see example below). 111000 Pig Porky BA.PSY 111000 Pig Porky BFA.FP Fortunately, as long as the student's ID number isn't on the Eval S...

Add phone number to quote report.
Hey guys, My retail staff asked me if it is possible to add the customer phone number to the Quote summary or Quote detailed report. Does anyone know what changes need to be made to the report to make this happen. I know I will have to change the query and add a join statement to get it from the customer record but am unable to figure out how to do it. Any ideas? Thanks, Nevermind, I figured it out myself. Thanks anyway. <nick@realkiteboarding.com> wrote in message news:BhKXd.15130$GO5.11570@fe07.lga... > Hey guys, > > My retail staff asked me if it is possible to a...

report writer #11
does anyone have a pda for report writer? I am at a clients site and need to create some calculated fields on an invoice. Any information on report writer development would be very helpful. Thank you for any assistance. Bill This may sound silly but what is a "pda for report writer"? Did you mean PDF? My Great Plains install has a "Documentation" sub folder containing PDF manuals for many of the installed modules. Report Writer is one of the manuals. Take a look if something similar exists on the install you're working with. "2020" <2020@discussi...

A question about threads and message queues.
I'm writing a program using MFC, actually I've done this technique several times. I have a primary UI in one window, and I make a UI thread that has a different window that is displaying some graphics. I have a commercial graphics library that has 'transitions' (ways to go from one image to another using wipes or fades and the like). These transitions can be run in a loop for high speed, but still take a few seconds, during which time the UI on all threads in the app freeze. The solution to this is to repeatedly send a message to do the next step (via a timer or otherwise) ...

Non-Inventory Item and a Strange Report
Am trying to wade through various reports. We have two non-inventory skus (and departments); "rental" and "ship". Last month's sales report for rental showed our profit at 44.83% for this department (although this percentage is not applicable, I am wondering why it shows up that way.....). The profit for ship is 100%. I am guessing it has something to do with returns on our rentals.......is there a better way to set up a "rental" business in RMS? I would like to see a better analysis of our profit, and there are serial numbers listed with each item rent...

Conditional Formatting #27
I have a range of cells that I want to use conditional formatting on. I want the largest number in the range to be formatted or Highlighted or something like that. The conditions listed really don't seem to apply since the number may be different each time so I can't say for certain that the number WILL BE ....whatever. I just want to highlight the biggest number as compared to the rest in the range. Maybe i need to use a couple of different functions. Please let me know. Dave French Office XP Pro Select your range (say A1:C22) With A1 the activecell format|conditional form...

Conditinal Format Not Working
I am trying to get a field to turn red if the condition is "High" I tried using the conditional formatting and it does not work. I also tried using the below vba code and this does not work either. I tried it in the after update procedure and the on current procedure with no luck. Could this be an Access bug? Private Sub Critical_AfterUpdate() If Me.Critical = "High" Then Me.Critical.ForeColor = vbRed End If End Sub Dee, Is Critical a manually entered text control? Or a Combo... or a List? Is it bound to a text field? Details please... Yo...

Number Format Problem Prevents Proper Sorting
I have a long list of numbers that were exported from anothe application that I can't seem to format properly to sort in ascendin order. I have changed the format from General to Number and back wit no success. I'm sure that this is an easy fix, but I'm not seeing th answer. The third entry below (1103) should be toward the top of th list. Any ideas? 110272 110277 1103 110329 110338 11033 -- 65Stan ----------------------------------------------------------------------- 65Stang's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1500 View this threa...

Conditional formatting for groups
I have an Excel list where the first column identifies a group, like 1 1 1 3 4 4 6 6 6 Now I want to color the rows of the groups alternating for better readability. Group 1 (the rows starting with 1) - red Group 2 (the rows starting with 3) - blue Group 3 (the rows starting with 4) - red Group 4 (the rows starting with 6) - blue ....and so on The numbers in this list are not in sequence and have gaps. The number of rows belonging to a group differs from group to group. Any smart and experienced guy out there who can help? Requires a helper column... 1. Assuming your groups are in col. B, ...

Format Cells Date (or any change) not working on imported data
Hello, I've just spent ages researching this and not come up with what I need to be able to do. I have a worksheet for some simple data that has been imported, a date, text and number column (as they display graphically to the end user). All are a "general" format when using Format > Cells. The issue I have is that the date information is in an American date format and I would like to change them into a UK date format. Format > Cells and selecting any option (including custom and special) makes no changes to the imported data. I have seen the work arounds whereby you sp...

Re Outlook 2003 settings
When I setup my POP3 email account in Outlook 2003, it was automatically setup to connect thru LAN, even though my Internet connection is thru 56k modem--at least right now. I do have a built in LAN, but it is not connected at this time. I want to be able to poll for mail. Should I leave it set to LAN and check the modem box to connect when off line? Currently I manually dial my ISP--settings for dial up in Internet Explorer are set to never dial. Do I change this to always dial or will I end up sometimes having 2 dialers running--one from Network connections and one from Internet ...

Formatting Phone Numbers
Consider this example: A1 -> 3365551111 A2 -> 336-555-2222 A3 -> (336) 555-3333 I want to be able to scan through column A and change all numbers to be in the format of A1 which is just straight numbers...no punctuation. How would I do that? -- Tha BeatMaker ------------------------------------------------------------------------ Tha BeatMaker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23998 View this thread: http://www.excelforum.com/showthread.php?threadid=383980 Easiest would be to use a macro. Sub RemoveAlphas() ''...