I have achieved this in Excel but I have a column of names which are numbered 1 - 100 in Column A; I want to convert them into two separate columns so all the odd numbered names are in column B and all the even numbered names are in Column C. Can't seem to find any reference to it anywhere.
This is the formula in Excel:
=INDIRECT(ADDRESS((ROW($A1)-1)*3+COLUMN(A1),1))
NorthRoyMal wrote:
This is the formula in Excel:
=INDIRECT(ADDRESS((ROW($A1)-1)*3+COLUMN(A1),1))
That formula will not work in Excel or Numbers to split column A into two columns of odd and even rows. It is multiplying by 3 and will therefore give every third row, not every other row. If you replace the 3 with a 2 it will do what you want.
B1 =INDIRECT(ADDRESS((ROW($A1)-1)*2+COLUMN(A1),1))
In the table shown below I left off the INDIRECT part of the formula so the addresses can be seen.
B1 =ADDRESS((ROW($A1)-1)*2+COLUMN(A1),1)
I filled it to C1 then fill down to complete the columns.
NorthRoyMal wrote:
This is the formula in Excel:
=INDIRECT(ADDRESS((ROW($A1)-1)*3+COLUMN(A1),1))
That formula will not work in Excel or Numbers to split column A into two columns of odd and even rows. It is multiplying by 3 and will therefore give every third row, not every other row. If you replace the 3 with a 2 it will do what you want.
B1 =INDIRECT(ADDRESS((ROW($A1)-1)*2+COLUMN(A1),1))
In the table shown below I left off the INDIRECT part of the formula so the addresses can be seen.
B1 =ADDRESS((ROW($A1)-1)*2+COLUMN(A1),1)
I filled it to C1 then fill down to complete the columns.
Try this:
I used a shorter table (51 rows and 50 'names') but same formulas will work on a 101 row table with 100 names (or other text).
The formulas are the same except for the presence or absence of the -1 closest to the end of the first (odd, in column B) and missing from the second (even, in column C).
Regards,
Barry
Hi Barry - it never ceases to amaze me people actually know this stuff! I wish I knew the logic so I could work it out for myself so I am therefor very grateful for your assistance. I can get most of the formula written but when I try to add the x2 part I get another function inserted - XIRR - which is to do with rates of return on investment! I can't find where there is a shortcut table that would force me to use the automatic insertion of the XIRR function each time I press the X key.
Badunit, you get the prize! Works a treat!
Thank you so much; thank you to BOTH you and Barry who bothered to help.
Where did you guys learn this kind of stuff? Is it an extension of a coding language?
And yes, you were 100% right, Badunit, the original Excel formula was for three three columns which I realised. I searched for an equivalent function in Numbers but, of course, couldn't find one so I reached out to this forum.
That × character (not x) in Barry's formulas is multiplication. You type it in as an asterisk * and Numbers will turn it into that ×.
For division you type / and it turns into ÷ (which, with very small font, looks an awful lot like the + character).
For subtraction you type - and it turns into − (a slightly longer dash).
Addition is the + and it uses that character.
Numbers accepts all those characters so you can copy/paste formulas posted here and not have to retype them (if they are posted as text).
Hi NorthRoyMal,
"…but when I try to add the x2 part I get another function inserted"
The "×" part of ×2 is not a lower case letter x. It is the multiplication sign, which cannot be directly typed from the keyboard. Type the multiplication operator (an asterisk) using shift-8 in its place. Numbers will immediately replace the operator with the multiplication sign.
The process for division formulas is similar: Type the division operator ( / ). It will be immediately replaced with the division sign ( ÷ ).
Regards,
Barry
Can Numbers transpose one column into two columns?
