-1

With my previous posts
1. PHPSpreadsheet generates an error "Wrong number of arguments for INDEX() function: 5 given, between 1 and 4 expected"
2. Excel - Getting the Top 5 data of a column and their matching title but produces duplicates

I have found out that the PHPSpreadsheet library for PHP is yet to allow the usage of the AGGREGATE() and complicated formulas/functions but I'm in dire need of their functionalities

Going back, I have 2 columns in my Excel (produced by my web applications made from CodeIgniter and Laravel)

2 Columns

The problem is, the Article Count column (on the right) contains 2 values of 54 which is supposed to belong to 2 different Publications (on the left) but with the use of the formula =INDEX(E$4:E$38,MATCH(M4,J$4:J$38,0)) it just fetches the 1st matched Publication.

The output should look like this:
output

The original Table:
raw

My question is, what would be the right function or code in Excel so I could retrieve the SECOND Publication of my matched data? I'm aiming to target those Publications that has the Article Count of 54, but I want to aim the SECOND ONE which is the letter D WITHOUT using the Aggregate() function of Excel

Here are my used codes
1) =LARGE(J4:J38,1) - J4:J38 is my range of raw data, I am using this to get the 5 highest numbers in descending order
2) =INDEX(E4:E38,MATCH(M4,J4:J38,0)) - I'm using this to retrieve the Publication Names that matched the Article Count

Suomynona
  • 639
  • 1
  • 5
  • 20
  • yes sir @GeorgeMenoutis we also consider that because there might be times that there are 3 or more duplicates among the top 5 largest data that has the same value – Suomynona May 08 '19 at 06:45
  • Can you also tell me which columns and rows you use for the top 5 publication names and counts? – George Menoutis May 08 '19 at 06:55

2 Answers2

1

After communicating in chat, we got this correct formula:

=INDEX(E$2:E$38,IF(M4=M3,MATCH(L3,E$2:E$38,0),0)+MATCH(M4,OFFSET(J$2,IF(M4=M3,MATCH(L3,E$2:E$38,0),0),0,COUNT(J$2:J$38)-IF(M4=M3,MATCH(L3,E$2:E$38,0),0),1),0))

How this works: This IF(M4=M3,MATCH(L3,E$2:E$38,0),0) returns the position of the previous row's publication title in the titles array (E), in case the current publication count is the same with the previous one. Let's call this number X. Instead of using J2:J38 for the results, we use J(2+X):J38. This trick is done by using offset to cut off the previous section, already used by the previous row. This way, on repeating publication counts the already mentioned titles get ignored.

George Menoutis
  • 6,894
  • 3
  • 19
  • 43
  • after a long chat, finally got the correct answer without using `AGGREGATE()` function and NO DUPLICATES! thank you very much for your kind understanding and patience with me :D – Suomynona May 08 '19 at 07:45
0

You need to use AGGREGATE's SMALL sub-function to return the smallest matching row number and adjust the k argument to accommodate duplicate rankings.

'in M4
=LARGE(J$4:J$38, ROW(1:1))
'in L4
=INDEX(I:I, AGGREGATE(15, 7, ROW($4:$38)/(J$4:J$38=M4), COUNTIF(M$4:M4, M4)))

enter image description here

user11468633
  • 1
  • hello sir, i'm looking for a solution that doesn't require the AGGREGATE() function because it is disabled or being prohibited by the PhpSpreadsheet library :( – Suomynona May 08 '19 at 07:21
  • There are early (pre-xl2010) array formulas that do not AGGREGATE; plenty of examples. – user11468633 May 08 '19 at 07:25