Need to split data from one row into a new row redux

Question

Need to split data from one row into a new row redux

Hi folks,

I asked this question about eight months ago (see thread https://discussions.apple.com/thread/5615030?answerId=23961353022#23961353022) and got an excellent response from forum regular Wayne Contello. However, I need to perform this operation again and when I attempted it recently, I am now greeted with a yellow warning triangle. Clicking it shows "This formula can’t reference its own cell, or depend on another formula that references this cell."

What I'm trying to do is the following:

I have an excel file that keeps track of members of a social group. The file places each member "unit" on a single row. The unit can be a single person or a couple. Columns are labeled "First1" "Last1" "Hometown1" "B-day1" while the second member of the unit is identified in columns like "First2" "Last2" etc.

What I'd like to do is duplicate those rows with two people (which I'll do by hand) but have a way of deleting the "xxxx2" data from one row and the "xxxx1" data from the duplicate row.

Wayne's illustrated solution was to create a blank sheet and enter the following formula in cell A2:

=OFFSET(Input Data::$A$2, INT((ROW()−2)÷2), COLUMN()−1+IF(MOD(ROW()−2, 2)=0, 0, 4)), which apparently worked fine for me last year but now is sending up an error flag. When I look at the formula, there is no clue except that which I quoted above.

Can anyone (or hopefully Wayne) take a second look at this and help me out? I can't imagine that it's a problem with using the newer version of Numbers, but who knows? I'm using version 3.2 (1861), which is the "new" Numbers.

Any help would really be appreciated.

Thanks!

-Tod

Posted on Jul 9, 2014 7:51 PM

Reply

Answer 1

Yellowbox

Level 7

20,383 points

Jul 10, 2014 8:18 AM in response to TodFromIndiana

Hi Tod,

The error message "This formula can’t reference its own cell, or depend on another formula that references this cell." may be because your table may be different from the one you were using for Wayne's solution. Numbers has Header Rows, Footer Rows and Header Columns. Such Headers in tables exclude themselves from formulas. Excel does not recognise them as headers. What table are you using now?

A screen shot of (the top left portion of) your table or a description of what you see under Menu > Table will help.

Regards,

Ian.

Reply

Answer 2

Jul 10, 2014 12:09 PM in response to Yellowbox

@ Yellowbox: Thanks for your comment. I checked per your screen shot above and you are right, my Header Rows = 1. In my blank table, where I would insert the formula in A2, I changed Header Rows = 0, then applied the formula into A2, and still got the same result. Here is a snapshot of that portion of my screen.

Here is a screen shot of part of the table containing the unaltered data:

Reply

Answer 3

Jul 10, 2014 12:16 PM in response to TodFromIndiana

I should mention that I don't have Excel on my Mac. I receive the sheet and open it in Numbers. If that makes any difference.

Reply

Answer 4

SGIII

Level 8

35,324 points

Jul 10, 2014 2:11 PM in response to TodFromIndiana

Hi Tod,

Looking at your example it seems the quickest and easiest solution would be to select the cells with the data in E through G and drag them over beneath the cells in A through C (first making sure there are enough blank rows at the bottom of the table). Then select columns E through G and delete. And sort on B if desired.

To drag, just select the cells, hold down the mouse/trackpad "button" and move the cursor to drag. The cells will "lift off" the table and move to where you "drop" them.

SG

Reply

Answer 5

Jul 10, 2014 2:51 PM in response to SGIII

@ SGIII: Holy crow! That is almost to easy to believe. Thanks for this tip. Worked like a charm. I can't believe it was this easy.

-Tod

Reply

Answer 6

SGIII

Level 8

35,324 points

Jul 10, 2014 5:53 PM in response to TodFromIndiana

Yes, spreadsheets are complicated beasts, but they can be very simple too. Not sure how well Apple has documented some of the interface conveniences like this one. The closest seems to be Move rows and columns within a table. Thanks for the green tick!

SG

Reply