12 Replies Latest reply: Jul 31, 2013 1:05 AM by Tradez
midjew Level 1 (0 points)

Need some help.

 

I have several customers and one row is 1 customer.

But I would like to keep data without having to have lots and lots of columns.

 

Basically I need to keep data of dates when Job was done and Paid and need several years stored. But how can I do this? Maybe in 1 cell?

 

Any help would be great!


Numbers '09, Mac OS X (10.7.4)
  • Barry Level 7 (29,976 points)

    Hi mj,

     

    You could keep it all in a single cell, at least until you reached the maximum number of characters that can be stored in a single cell, but there are problems with retrieving the data when using that approach.

     

    The physical equivalent would be a Banker's Box for each customer into which you tossed notes on paper for each transaction you wanted to record. Not a pretty picutre when you want to retrieve a specific transaction from the pile.

     

    You may want to do a bit more planning on this.

     

    Regards,

    Barry

  • Tradez Level 1 (0 points)

    I'm looking for something similar too.

     

    I place orders every week to restock my product inventory, and I want to keep track of the total order cost, along with a breakdown of the costs that make up the order,

     

    Because the types of cost incurred vary largely between orders, I currently need 20+ columns to store this data, and usually only 5 of these colums contain data, so it's extremely inefficient. (But I need all these colums because over about 10 orders, they all end up being filled at some point)

     

    It therefore makes sense that everey order is on it's own row which can then be expanded to show the breakdown of costs?

     

    Hope that makes sense and somebody can advise.

  • Wayne Contello Level 6 (17,554 points)

    why not enter data as transactions (by row).  Each row contains the date, amount, customer etc.  Then use the filter feature (part of the Reorganize panel) or a summary table by customer?

  • Tradez Level 1 (0 points)

    The issue I have is I want to know what makes up that 'amount'.. Some orders incur additional costs, some don't and those additional costs often vary between orders.

     

    For example, one order may consist of 4 different delivery charges, extra packaging costs, inspection costs, label costs, paper costs, clearance costs, whilst another order may only have a single delivery cost

     

    It seems a huge waste of space to have colums such as 'packaging coss' which sit empty for 90% of orders...

     

    Hope I understood you correctly?

  • Wayne Contello Level 6 (17,554 points)

    Number is really designed to be  a spreadsheet not a database.  You are trying to use it as a database that is multi replational.  You should consider using a better tool.  Libre Office is free and has a database tool.  Alternatively there is Filemaker.

  • Tradez Level 1 (0 points)

    Thanks  - It's a shame because that's the only part of my Spreadsheet that could do with that feature.  Everything else works perfectly I numbers, so I'm hoping I can find an alternative way round it some how!

  • Wayne Contello Level 6 (17,554 points)

    can you post a screenshot?  You can enter multiple lines of text in a single cell by holding the option key while hitting return-- maybe that will work for you.  There would be no simple way to aggregate data from that cell.

  • Tradez Level 1 (0 points)

    I think individual columns will just have to suffice for the moment then!  Will try and get a screenshot a little ater as it's on a different system.

  • Jerrold Green1 Level 7 (29,960 points)

    MJ,

     

    If you give us some sample entries, we may be able to suggest a design for you.

     

    Jerry

  • Badunit Level 6 (11,615 points)

    It can be done but it can get complex and error prone.  But here is a way.  I have it all in one table to demonstrate how to parse the data but you can have the parsing (column C and to the right) in a separate table. The parsed data can be summed up.

     

    You enter all the costs in one cell, one line per cost. Each needs a $ and each line is separated by Option-Return.

     

    Screen Shot 2013-07-30 at 12.50.39 PM.png

     

    Cell A1 contains an option-return (with the cell formatted as text) or the formula ="option-return" (with the cell formated as automatic). This is a workaround. The other formulas need to "find" option returns (character code 10) in other strings but you cannot use CHAR(10) to generate that character. So it is easiest to put the option-return character in a cell somewhere and reference it.

     

    A2 and B2 are manually entered data

    C2 =IFERROR(MID(B2,FIND("$",B2)+1,IFERROR(FIND($A$1,B2)-FIND("$",B2)-1,LEN(B2))),0 )

    D2=IFERROR(RIGHT(B2,LEN(B2)-FIND($A$1,B2)),"")

     

    Select C2 and D2. Copy. Paste at E2, G2, I2, etc. for however many costs you want to handle.

    Fill down to finish the table.

    You will probably want to include a column to the right that sums all the costs.

     

    You can hide all the parsing columns, leaving just your entered data (columns A and B) and your sum (which would be column J for this example).

     

    Be careful when entering data.  Forgetting a $ or forgetting an option-return will result in incorrect parsing.  A missing $ will not show up as an orange error triangle, it will be a 0 forthe cost. That is part of why I said it is more prone to errors to do it this way.  You could, of course, edit the formulas and flag any zero results, knowing there should be none.

  • kharisma Level 1 (105 points)

    Tradez,

     

    I suggest three tables ...

     

    1. Order Items - separate line for each item or cost on an order. 

      The order with many costs would have several lines,
      and the orders with just one or two costs would have just one or two lines.

      (Note that Joe has bought several items and incurred a number of costs.)

    2. Item Costs - summarises and groups costs according to type

      "breakdown of the costs that make up the order"

    3. Orders - one line per order

      Matches order to invoice number, date, total cost, customer, when paid, etc.

     

    See screenshot:

    orders.png

     

    The formulae:

     

    Order Items is purely entry only.

     

    ---

     

    Items Costs has fixed rows, one per category.

     

    The next column, Summary, has this formula:

     

    =SUMIF(Order Items::$Category, "="&$Category, Order Items::$Cost)

     

    Order Items::$Category - looks up the Category column in Order Items

     

    "="&$Category - searches for rows that match the category in Items Costs

     

    Order Items::$Cost - selectively adds only those costs that match this category

     

    ---

     

    Orders - mostly data entry

     

    The column Total Order has this formula:

     

    =SUMIF(Order Items::$Invoice, "=" &$Code, Order Items::$Cost)

     

    Selectivly adds those costs which match this invoice number.

     

    ---

     

    The reason for the $Code column is that when formatting the invoice column, I created a custom format INV00000, (see screenshot), but that ran into issues with comparisons between cells, hence the extra column.

     

    INV00000.png

  • Tradez Level 1 (0 points)

    Thanks for all of your input.  I am going to look to implement something similar and will let you know!