multiple columns with same variable to become 1 row at the pivot table

[keredu]

Hi everyone,

I have to analyze the number of published articles by authors, however, pretty much all the articles have several authors, so I do not know how to use a pivot table to analyze that.

Thatīs the example that I have.

Column A (Article) - Column B (Author 1) - Column C (Author 2)
Article 1 - Jose - Maria
Article 2 - Jose - Joao

I wanted a pivot table like this

Author - Number of articles
Jose - 2
Maria - 1
Joao 1

I did not wanted to change the source table (meaning that the source should still be with 3 columns Article, Author 1 and Author 2)

[MarvinP]

Hi,

I'm sorry to say that you will need a 2 column table to do this pivot table. You need to move Author2 down under Author1. Then you can do a pivot table with the data.

This is a common question and has many ways to do it. See one at:

https://blogs.office.com/2015/12/15/...in-excel-2016/

[Glenn Kennedy]

One way, not requiring a Pivot Table:

In F2, copied down, use this array formula:

=IFERROR(INDIRECT(TEXT(MIN(IF(($B$2:$C$10<>"")*(COUNTIF($F$1:F1,$B$2:$C$10)=0),ROW($2:$10)*100+COLUMN($B:$C),10^10)),"R0C00"),)&"","")

In G2, copied down:

=IF(F2="","",COUNTIF($B$2:$C$5,F2))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[keredu]

thank you. I have excel 2016, but I was not able to follow those steps. Iīll look again after I try Glennīs comment

[keredu]

One way, not requiring a Pivot Table:

In F2, copied down, use this array formula:

=IFERROR(INDIRECT(TEXT(MIN(IF(($B$2:$C$10<>"")*(COUNTIF($F$1:F1,$B$2:$C$10)=0),ROW($2:$10)*100+COLUMN($B:$C),10^10)),"R0C00"),)&"","")

In G2, copied down:

=IF(F2="","",COUNTIF($B$2:$C$5,F2))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

I think that will work! thank you!