Multiple Lines of Data per Row

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

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.

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?

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?

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.

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!

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.

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.

MJ,

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

Jerry

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.

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.

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:

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.

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