From multi-column Database to a single-column one.

Discussion in 'Windows, Linux & Others on the Mac' started by Rudiakys, Dec 30, 2009.

  1. Rudiakys macrumors newbie

    Joined:
    Dec 30, 2009
    #1
    Hello everyone.
    I don't know how to make a database like this:
    [​IMG]
    , look like this one:
    [​IMG].
    Note that I don't simply have to transform it, but I have to leave (intact) the first one in its sheet, and have the result in another one.
    Thank You.
     
  2. Eraserhead macrumors G4

    Eraserhead

    Joined:
    Nov 3, 2005
    Location:
    UK
    #2
    I don't understand the question. What is the database store in? Access? Microsoft SQL Server?
     
  3. Rudiakys thread starter macrumors newbie

    Joined:
    Dec 30, 2009
    #3
    Excel.
     
  4. sushi Moderator emeritus

    sushi

    Joined:
    Jul 19, 2002
    Location:
    キャンプスワ&#
    #4
    The pics are not showing. Please try posting them again so we can see what you want to do in Excel.
     
  5. Rudiakys thread starter macrumors newbie

    Joined:
    Dec 30, 2009
    #5
    Hello everyone.
    I don't know how to make a database like this: (first image), look like this one: (second image).
    Note that I don't simply have to transform it, but I have to leave (intact) the first one in its sheet, and have the result in another one.
    Thank You.
     

    Attached Files:

    • 1.jpg
      1.jpg
      File size:
      46.8 KB
      Views:
      44
    • 2.jpg
      2.jpg
      File size:
      35.3 KB
      Views:
      85
  6. Eraserhead macrumors G4

    Eraserhead

    Joined:
    Nov 3, 2005
    Location:
    UK
  7. mkrishnan Moderator emeritus

    mkrishnan

    Joined:
    Jan 9, 2004
    Location:
    Grand Rapids, MI, USA
    #7
    Do you mean that you want to do it dynamically (like with Excel formulas, macros, or actions), without modifying the original sheet? And is the original sheet completely disorganized like the example you gave (no headers or labels, extraneous data in some entries)?

    Look up the built-in help for "pivot tables" -- this might do what you want, although the tables are assembled expecting the existence of column/row headers.

    Now the snark would be to say that you should have whoever creates the originals for you learn how to use Excel, or at least communicate with other human beings. :p
     
  8. Rudiakys thread starter macrumors newbie

    Joined:
    Dec 30, 2009
    #8
    Yes. Also, since this is only a simlified example of what the final sheets would be, there could be more columns.
    The fact is: I dont know hom many rows or columns there would be, because the first scheet is a result of another scheet either.

    Yes, there would be labels on the actual tables I'm going to deal with.
    Trying to pose my problem in other words: I'd like to know if there is some way to have a plain list of those nouns with their quantities attached. Maybe a formula that copies only things which are different from zero and empty cells.
     
  9. Eraserhead macrumors G4

    Eraserhead

    Joined:
    Nov 3, 2005
    Location:
    UK
    #9
    The snark in me would say that you should use a real database :p. In this case that even includes Access :eek:.
     
  10. mkrishnan Moderator emeritus

    mkrishnan

    Joined:
    Jan 9, 2004
    Location:
    Grand Rapids, MI, USA
    #10
    Yeah, things like this are so much easier, even in Access....

    I think that Pivot Tables should be able to be cajoled into what you want. The blank cells should be fine. I'm not 100% sure about how they'll handle all the zeros, though. I'm sure you could clean that up with some macros or Visual Basic, but that gets awfully silly...

    What's the volume here... are we talking about doing this hundreds or thousands of times -- like on a daily basis?
     
  11. dejo Moderator

    dejo

    Staff Member

    Joined:
    Sep 2, 2004
    Location:
    The Centennial State
    #11
    And the snark in me says that even the simplified version is still multi-column.
     
  12. sushi Moderator emeritus

    sushi

    Joined:
    Jul 19, 2002
    Location:
    キャンプスワ&#
    #12
    As others have suggested, a database may be the way to go.

    FWIW, using Excel as a database can be a real pain. Additionally, it's easy to cause your database to become disjoined (corrupted).

    Rule of thumb:

    - If you are going to be querying your data, then use a database.

    - If you are going to be calculating based upon your data, then use a spreadsheet.

    From the sounds of it, it looks more like data queries are the goal. Although to be honest, I still am not sure exactly what you want to do but understand that you don't want to post real data. The nice thing about databases is that they handle varying amounts of records (data) very conveniently. Excel Macros and such are much easier to do when the data elements are constant in number.

    Database wise, as some have suggested, there is Access on the Windows side. FileMaker Pro which is cross platform might be worth a look. Or maybe an SQL solution.
     
  13. Rudiakys thread starter macrumors newbie

    Joined:
    Dec 30, 2009
    #13
    All right then. I'm going to have a look at some database software, thankyou.
     
  14. balamw Moderator

    balamw

    Staff Member

    Joined:
    Aug 16, 2005
    Location:
    New England
    #14
    Unless of course you have mountains of data, in which case a database is still the right way to store the data, maybe with a spreadsheet front end. ;)

    Microsoft offers a reduced version of their SQL Server (a real database) software for free if you want to stay on Windows:

    http://www.microsoft.com/Sqlserver/2005/en/us/express.aspx

    Otherwise, you may want to look at MySQL which is cross platform.

    B
     
  15. Eraserhead macrumors G4

    Eraserhead

    Joined:
    Nov 3, 2005
    Location:
    UK
    #15
    As a serious lesson for future readers the only reason you would attempt something like what you suggested in the OP would be if you had a lot of data to start with that was already in such a system so it was going to be prohibitively expensive to change to using a database.

    Even in the case of the OP it is definitely worth spending a bit of time making sure the right solution is used for the problem - as not doing that will cost a lot of time (and therefore money) to get the right solution at a later point - or you'll waste a lot of time and money trying to do things in a more difficult way than necessary.
     

Share This Page