Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jul 2005
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Large Excel Spreadsheet (Excel 2000)

    I have a large Excel spreadsheet (46,000 rows and 65 columns). The spreadsheet captures total spend for vendors by account group. The rows capture all of the vendors (i.e. 46,000) and the columns capture various data, but mostly the account groups (59 account groups). I would like to know if there is a way to see for each vendor columns A-F in addition to the account group columns that contain a value. For instance, a particular vendor may have only been posted to 1 of the 59 account groups. Right now I have to scroll across 65 columns possibly to see this data. I've attached a sample of the spreadsheet. Thanks for any help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Large Excel Spreadsheet (Excel 2000)

    Welcome to Woody's Lounge!

    Select cell G2, then select Window | Freeze Panes. Row 1 and columns A:F will remain visible while you scroll through the data.

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Large Excel Spreadsheet (Excel 2000)

    That table is mostly open spaces..how could one reorganize it so that a Pivot Table or other report format could list next to each vendor only those column headings where there is an amount? Vendor Type 1 $xxx.yy
    Type 2 xx.yy and so on? Or is Access a better way?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Large Excel Spreadsheet (Excel 2000)

    Column A is labeled "ORACLE#" so the data may well come out of an Oracle database, and devonh28 may not be able to modify the design.

    If I were to set up something myself, I would choose Access (or a server database such as SQL Server with an Access frontend), and create a table in which each occurring vendor - account group combination would be a separate record:

    <table border=1><td>VendorID</td><td>AccountID</td><td>[b]Amount</td><td align=right>1</td><td align=right>18</td><td align=right>233.08</td><td align=right>1</td><td align=right>36</td><td align=right>269.94</td><td align=right>2</td><td align=right>36</td><td align=right>996.60</td><td align=right>...</td><td align=right>..</td><td align=right>...</td></table>
    The VendorID and AccountID are linked to lookup tables listing the vendors and account groups, respectively. This format is relatively compact, and is very suitable for tallying data in different ways. This could not be done in Excel, the number of rows would be too large.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •