Trying to expand a single row of cells into multiple rows based on a quantity in one cell. My ultimate goal is to make one label from each row so I’m using a Source Sheet to expand the data entry sheet into a usable form. Not sure if that makes any sense so here’s an example. Top data set is the data entry sheet. Bottom data set is what I want the Source Sheet to look like.
Hope that makes sense. Thanks for the help.
iPad 2
You can try the script below.
The new table will then appear automatically on that sheet. (If you don't like the default Header Column you can set up a blank table without a Header Column, click a cell in that table, and command-v or Edit > Paste and Match Format.)
SG
tell application "Numbers"
tell front document's active sheet
tell (first table whose selection range's class is range)
set vv to rows'scells'sformatted value
end tell
end tell
end tell
set text item delimiters to tab
set pasteStr to (vv's item 1 as text) & return #header row
set numRows to vv'slength
repeat with r from 2 to numRows
set qty to vv's item r's item 2
repeat qty times
set vv's item r's item 2 to 1 #set Qty to 1
set pasteStr to (pasteStr & vv's item r as text) & return
end repeat
end repeat
set the clipboard topasteStr
pasteStr#optional to view in Result pane
You can try the script below.
The new table will then appear automatically on that sheet. (If you don't like the default Header Column you can set up a blank table without a Header Column, click a cell in that table, and command-v or Edit > Paste and Match Format.)
SG
tell application "Numbers"
tell front document's active sheet
tell (first table whose selection range's class is range)
set vv to rows'scells'sformatted value
end tell
end tell
end tell
set text item delimiters to tab
set pasteStr to (vv's item 1 as text) & return #header row
set numRows to vv'slength
repeat with r from 2 to numRows
set qty to vv's item r's item 2
repeat qty times
set vv's item r's item 2 to 1 #set Qty to 1
set pasteStr to (pasteStr & vv's item r as text) & return
end repeat
end repeat
set the clipboard topasteStr
pasteStr#optional to view in Result pane
Hi G',
Took a while before it made sense.
Essentially you want as many copies of each row as the value in that row of column B, with all data copied except the value in column B, which will be set to 1 on each row in the second table.
I don't immediately see a way to control this using a formula, but at first glance, it looks like a script solution would be comparatively simple.
The script would need to:
Get the number of rows of data in Table 1 to know when to quit.
Get the total of column B to determine how many rows would be filled in Table 2. (May not be necessary)
Set a counter (counter 2) to the number of data rows in Table 1
Enter a 'read' loop that would repeat as many times as the value in that counter to:
That would be the overall pattern, but I'm sure I've missed some of the details. There are some talented scripters who can take it from there.
Regards,
Barry
Thank you, SG and Barry. The script did the trick. A couple of quick notes:
1) Thanks for including the instructions on how to make this work. I didn't even know that scripts existed so I was completely dependent on them.
2) Instead of clicking anywhere in the table I had to click in the first cell in the Quantity column otherwise it would return an error.
3) I added a Date column before the Quantity column and that returned an error so I had to move Date after Quantity and that did it.
Thanks again. That puts the finishing touches on spreadsheet - for now.
Brian
Glad the script worked for you.
If you have to do this again and you want to add a Date column before the Qty column, so the that the Qty column becomes the third column then you would replace item 2 with item 3 wherever you see it in the script (two places).
SG
"You can try the script below."
Nice!
Results on my test table:
Regards,
Barry
Expanding one row of cells into multiple cells
