How do I "compact" a row?

Question

Level 1

8 points

How do I "compact" a row?

Hi Folks,

This seems like it should be simple, but i'm having trouble finding an obvious solution. Consider the following row:

Header 1	Header 2	Header 3	Header 4	Header 5	Header 6
534	a comment	27

I would like to create another row in another table like so:

Header 1	Header 2	Header 3
534	a comment	27

That is, i'd like to replicate the row, but "compact" all of the cells out of it that don't meet a certain condition (in this case, if the cell is empty, it's not included in the replication).

I can achieve this on the first cell of the new row by a really nasty series of nested IF functions, but when it comes to the second cell, I then need to compare each nested IF to the first cell, it all got very messy very quickly. So messy in fact that my macbook air started to spin the beach ball for a long time every time I made a keystroke. Not a practical solution. Is there a straightforward way to do this?

Thanks so much,

Hal

MacBook Air, OS X Mountain Lion (10.8.2)

Posted on Apr 17, 2015 10:09 PM

Reply

Answer 1

t quinn

Level 5

7,043 points

Apr 18, 2015 6:49 AM in response to htmayes

Hi htmayes,

If your info is always in columns A, D, F then your solution is a simple one but I get the impression that is not the case.

If you are not making calculations, maybe you don't need more than one column:

If you are making calculations, it would be possible to extract data from the above.

It may be possible that why you are trying to accomplish is more esily done with a different approach. What are you trying to do (big picture)?

quinn

Reply

Answer 2

htmayes Author

Level 1

8 points

Apr 19, 2015 8:09 AM in response to t quinn

Hi Quinn,

Thanks for your reply. What I am trying to accomplish is this: I have a table of customers who have ordered services, I am trying to make a second table of customers that can be used by crew members in the field to record which services have been performed for the customer (using an iPad). Here's an example

Customer Table:

Customer	Spring Cleanup	Spring Cleanup - Hired	Weekly Mow & Trim	Mow & Trim - Hired	Hedge Trim	Hedge Trim - Hired
John Test	$175	TRUE	$40	TRUE	$455	FALSE
Jane Test	$225	FALSE	$45	TRUE	$390	TRUE

Work Order Table:

Customer	Service 1	Service 1 Completed	Service 2	Service 2 Completed	Service 3	Service 3 Completed
John Test	Spring Cleanup	[cell type: checkbox]	Mow & Trim	[cell type: checkbox]	[empty]	[empty]
Jane Test	Mow & Trim	[cell type: checkbox]	Hedge Trim	[cell type: checkbox]	[empty]	[empty]

There is a bunch of other data gathered (clock in/out times, etc) but those are not important for the example. What I'm trying to do is copy each hired service into the appropriate cell while skipping over services which have not been hired for each customer. Each customer has many possible services, but generally each week (I intend to make a new work order at the beginning of each week, with a separate table for each day) there will only be a small handful of services performed for a given customer. This way the crew members in the field don't have to scroll across dozens of columns to find the one or two services that they need to do for the customer this week. I can give a dropbox link to my actual sheet if that helps.

Thanks so much for your help.

Kindly,

Hal

Reply

Answer 3

t quinn

Level 5

7,043 points

Apr 19, 2015 1:13 PM in response to htmayes

Hi Hal,

The bigger picture is helpful. You have not been overlooking an obvious solution. What you want to do is not something that Numbers is set up to do. That doesn't mean that it can't be done. A dropbox link would be helpful. There are a couple of scripting wizards on this forum that may have ascript solution to offer.

I think the solution should also work well in a form on iOS. This can simplify input. If each client has a row then your employees will have a single page in the form for each client. I am a big fan of forms.

How many potential services are there? You mentioned dozens of columns. Does this mean around 12 possible services? What other data will your crew be inputing? Since the purpose if to simplify their recording experience I think it is relevant.

What version of Numbers are you using? Your profile says Mountain Lion for an OS so I assumed '09. Is that still true?

quinn

Reply

Answer 4

htmayes Author

Level 1

8 points

Apr 19, 2015 5:11 PM in response to t quinn

Hi Quinn,

Here's a link https://dl.dropboxusercontent.com/u/7499586/Master%20Customer%20Database.numbers

It's a little messy as i've been experimenting.

I guess i haven't updated my profile in a while. I'm on yosemite using the latest versions of both os x and Numbers.

There's 16 possible services. On any given week a customer is only going to receive 1-3 of them. Maybe a little more big picture would be helpful, as maybe my whole approach is not the best, here's the general process I'm trying to get to:

I currently have a spreadsheet that gets filled out in the field by the estimator to generate a quote, as well as a contract for each customer. The estimator exports this sheet into the dropbox, and emails a PDF of the contract to send to the customer. This process has been working great so far. This spreadsheet also generates a single row of data that contains all pertinent information for that customer. This row is copied into the master customer list spreadsheet.

From here I want to generate a work order/schedule based off of the information in each row of customer data. This ideally would be some type of format that a crew can fill out on an iPad. right now they take paper records and we have to manually enter everything at the end of the week, i am trying to eliminate this double-entry.

I like the idea of a form. Does numbers support forms, or are you talking about another program?

Thanks for all your help. I really appreciate it.

Kindly,

Hal

Reply

Answer 5

t quinn

Level 5

7,043 points

Apr 19, 2015 7:06 PM in response to htmayes

Hi Hall,

Well shoot, I am still at Numbers 3.2 so I can't see your spreadsheet. I don't know if you can export it for my version.

One of the difficulties in doing what you want to do is moving data from the customer table with the way it is set up. That is, the service/true/service/true structure makes it hard to extract the data. Is the sheet that is generated by the estimator setup differently? It maybe easier to bring the data in from there. This also means it would be difficult to read data back from your proposed work order table.

In iOS you can create a form version of any table in your spreadsheet. Tapping the "+" to the left of the sheet tabs will offer you the choice of new sheet or new form. Check it out. A form goes row by row and offers a line for each header column.

This table:

Customer	Spring Cleanup	Mow & Trim	Hedge Trim
John Test	TRUE	TRUE	FALSE
Jane Test	FALSE	TRUE	TRUE

Where the true/false are formatted as checkboxes would work very well as a form. John would have a page and next would be Jane's. We still wouldn't be able to hide sevice columns that a customer didn't want.

quinn

Reply

Answer 6

t quinn

Level 5

7,043 points

Apr 19, 2015 8:12 PM in response to t quinn

Hi Hal,

Here is a potential input form for your crews. This is a screenshot from my iPod so an iPad would be different.

Every potential task would be in the list. I don't see away around this with a form. The first line "Tasks" is generated with a formula. The table the form was based on looked like this:

quinn

Reply

Answer 7

Hiroto

Level 5

7,461 points

Apr 22, 2015 12:17 PM in response to htmayes

Hello

I'd keep Master table as simple as possible so that each record corresponds to single service order. Using such scheme, it is relatively easy to build a summary table as you describe.

E.g.,

Master A1 customer A2 John Test A3 John Test A4 John Test A5 Jane Test A6 Jane Test A7 Jane Test B1 service B2 Spring Cleaning B3 Weekly Mow & Trim B4 Hedge Trim B5 Spring Cleaning B6 Weekly Mow & Trim B7 Hedge Trim C1 charge C2 175 C3 40 C4 455 C5 225 C6 45 C7 390 D1 hired? D2 TRUE D3 TRUE D4 TRUE D5 FALSE D6 TRUE D7 TRUE E1 done? E2 FALSE E3 FALSE E4 TRUE E5 FALSE E6 FALSE E7 FALSE F1 index F2 =IF(AND(D2,NOT(E2)),A2&"|"&COUNTIFS(A$2:A2,A2,D$2:D2,TRUE,E$2:E2,FALSE),"") F3 =IF(AND(D3,NOT(E3)),A3&"|"&COUNTIFS(A$2:A3,A3,D$2:D3,TRUE,E$2:E3,FALSE),"") F4 =IF(AND(D4,NOT(E4)),A4&"|"&COUNTIFS(A$2:A4,A4,D$2:D4,TRUE,E$2:E4,FALSE),"") F5 =IF(AND(D5,NOT(E5)),A5&"|"&COUNTIFS(A$2:A5,A5,D$2:D5,TRUE,E$2:E5,FALSE),"") F6 =IF(AND(D6,NOT(E6)),A6&"|"&COUNTIFS(A$2:A6,A6,D$2:D6,TRUE,E$2:E6,FALSE),"") F7 =IF(AND(D7,NOT(E7)),A7&"|"&COUNTIFS(A$2:A7,A7,D$2:D7,TRUE,E$2:E7,FALSE),"")

Work Order A1 customer A2 John Test A3 Jane Test B1 Service 1 B2 =IFERROR(INDEX(Master::$B,MATCH($A2&"|"&INT(COLUMN()/2),Master::$F,0),1),"") B3 =IFERROR(INDEX(Master::$B,MATCH($A3&"|"&INT(COLUMN()/2),Master::$F,0),1),"") C1 done? C2 FALSE C3 FALSE D1 Service 2 D2 =IFERROR(INDEX(Master::$B,MATCH($A2&"|"&INT(COLUMN()/2),Master::$F,0),1),"") D3 =IFERROR(INDEX(Master::$B,MATCH($A3&"|"&INT(COLUMN()/2),Master::$F,0),1),"") E1 done? E2 FALSE E3 FALSE F1 Service 3 F2 =IFERROR(INDEX(Master::$B,MATCH($A2&"|"&INT(COLUMN()/2),Master::$F,0),1),"") F3 =IFERROR(INDEX(Master::$B,MATCH($A3&"|"&INT(COLUMN()/2),Master::$F,0),1),"") G1 done? G2 FALSE G3 FALSE

Notes.

Formula in Master::F2 can be filled down.

Formula in 'Work Order'::B2 can be filled (copied) across columns B, D and F.

'Work Order' table shows services for each customer of which status in Master is hired but not done.

'Work Order' table's columns C, E and G are for field input and not linked to Master::E.

Tables are built with Numbers v2.

Good luck,

H

Reply