Calling all you Numbers users

28 Oct 2020 21:50:56

Rick Churchill

At some stage Microsoft are going to drop support for my 2016 Microsoft Office so I am learning about numbers and seeing if I can move my Excel spreadsheets into numbers. I have started with the most complicated as if I cannot achieve this then I am going to have to look at LibreOffice.

I am trying to convert an Excel Workbook into Numbers "book". One of the sheets has to have two sets of names one along the top and the other down the side. I have already discovered that Numbers does not have the feature to allow me to have the names vertical at the top so, to avoid an enormously large spreadsheet, I have substituted codes. I intended to have a lookup table to allow me to substitute the name for the code in another sheet of the “workbook” so that they can be printed next to output data.

In order to make this table I have to have a list of names which obviously must be in a column with the codes alongside. I want the horizontal list on one sheet to reference the vertical list on another and would normally do this with the “Transpose” function which is part of cut and paste in Excel. The transpose feature within Numbers is completely different and transposes ALL the data. As the names will change and I want to keep them alphabetical I don’t want to have to keep doing this cell by cell.

I cannot find anything on the internet and whereas there are several Excel forums, I cannot find one dedicated to Numbers. Has anyone a workaround for this missing feature?

Re: Calling all you Numbers users

29 Oct 2020 11:42:39

Euan Williams

Does the (free) Libre Office for Mac have this function?

Re: Calling all you Numbers users

31 Oct 2020 23:59:37

Tony Still

You can sort of transpose in a brute force way using the INDEX function. Those of a sensitive disposition should not read any further.

Fill the receiving cells with an expression along the lines of:
INDEX( $a1:$a10, COLUMN()-42, 1)
- Source data at, eg, a1:a10
- Choose the item in the data according to the COLUMN() of the receiving cell (with a fudge factor (42 here) to normalise the source row index according to where the destination is located)
- Always using column 1 'cos there's only one source column.

Once you've debugged it, you can paste this expression into a block of cells in one go.

The equivalent expression for row-based source data is left as an exercise for the reader ;-)

Re: Calling all you Numbers users

1 Nov 2020 11:17:24

Michael Corgan

There is a way to show the names vertically and as long as they won't need re-sorting it would work, albeit a bit fiddly.

Use the Text box function in Numbers, enter the required name, rotate it 90 degrees and move it to the head of the appropriate column. Repeat.

Re: Calling all you Numbers users

1 Nov 2020 11:51:28

Rick Churchill

Thanks for your responses. I have got round the problem by using Excel to transpose then pasted back into Numbers.
Yes I think LibreOffice has the function but I wanted to try to get to grips with Numbers.
I will try the workaround later Tony.
The Text Box idea is a non starter I'm afraid because I want to sort and as you point out it won't work. I also think it cannot work with functions such as lookup tables.
I'm putting players names into column headers and then want to sort to put them alphabetically (so I can easily find a name). When a new player is added I want to resort. A further problem to get round is that Numbers only sorts rows not columns.