Make Column with Authors listed once taken from a second column with all books listed with their authors.

limmy3 wrote:

Hello  everyone,

make a list of all authors once in that list and how often they have been mentioned. 

That formula will not work in Numbers. A Pivot Table, both in Excel and Numbers, is by far the most efficient way to do this kind of thing. Just a few clicks and you have the answer. So you might consider upgrading your Numbers version.

In old versions of Numbers we used to have to do this by formula with something like this:

Add an "index" column to the table with the data, and enter this formula in the second row and fill it down the column.

=IF(COUNTIF(F$1:F2,F2)=1,MAX(G$1:G1)+1,"")

This increments a counter each time it encounters a new author name in column F.

That counter can be used in Table 2 to look up each author and list it once.

In A2, filled down:

=INDEX(Table 1::F,MATCH(ROW(cell)−1,Table 1::G,0))

This gives a list in which each author appears once.

You can then count how many times that author appears in your data with this formula in B2, filled down:

=COUNTIF(Table 1::F,A2)

Use ; in the formulas instead of , if your region uses , as the decimal separator.

SG

Hi limmy3,

This may get you started. Create another column to add an Index to each Author.

Formula in B2 (in my example): COUNTIF(A$1:A2,A)

I can not remember back to Numbers 11.1

Does it feature pivot tables? Have a look under Menu > Organise:

With a Pivot Table, the solution is easy:

If your version of Numbers does not feature pivot tables, then we must find another solution to your question.

Regards,

Ian.

Hi limmy3,

OK, let's forget Pivot Tables and use the combination of INDEX and MATCH.

Those functions work the same way in Excel and Numbers.

However, I do not understand the overall aim of that Excel formula.

Let's forget about the IFERROR function for now. It just gets in the way as we construct and debug formulas. We can add IFERROR later, if needed.

Even so, I can not make sense of this formula (without IFERROR):

INDEX(Tabelle 1::$F$2:$F$134,MATCH(1,COUNTIF(Tabelle 1::$F$1:F5,Tabelle 1::$F$2:$F$134)+(Tabelle 1::$F$2:$F$134≠"")×1,0),column-index,area-index)

The combination of INDEX and MATCH can form a powerful Lookup formula, that works in Excel and Numbers:

INDEX(Column I want a return value from,

MATCH(My Lookup Value,

Column I want to Lookup against,

Enter “0″ ))

The Excel formula does not match the syntax above.

Please tell us your overall aim, not an Excel formula.

What are you trying to do?

Regards,

Ian.