0

I am developing a web application made using PHP (withh CodeIgniter as the framework) that should produce an excel file report as as summary of data.

I am using PHPSpreadsheet as a library to generate the .xlsx file and everything is successful.

Raw Data

I was able to display in a certain range of cell the top 5 values of a column ie. Top 5 Publications that has the most number of Articles.

With the use of the code =LARGE(ColumnOfTheDataRange , NumeralValue) -- I was able to get the top 5 of what I wanted

Top 5
Then, on the left side of these top 5 data, I need to display the matching Publication Names / Publications. I was able to do it with the function =INDEX(PublicationNamesCellRange, MATCH(CellOfTheMatchingData, ColumnOfTheDataCellRange, 0))

Publication Names

Now, kindly notice that there are multiple article counts 54 in the top 5 data column (right), which should belong to 2 different Publications. The problem is, what code or technique should I use to produce the unique Publication Name so that it won't repeat the Publication Name E because in the original excel file, the Publication Name E is the first one that has the matching data of 54?

Suomynona
  • 639
  • 1
  • 5
  • 20
  • There are a couple of posts on here that show solutions for this - I made use of them, so have a search. Even if you don’t find them you can look at large() and aggregate() but aggregate() is excel version dependant. – Solar Mike May 07 '19 at 05:05
  • hello sir @SolarMike, good day! Before posting this, I made sure that there's no available solution because I've kept on looking for one for the past 3 days. If there is, may you kindly give me the link please? or tag this post as duplicate? As for your tip regarding the `aggregate()` function, will be looking for it, thank you very much for the idea! – Suomynona May 07 '19 at 05:08
  • I'm surprised you have not found relevant posts, as I made clear that is how I found my solution... – Solar Mike May 07 '19 at 05:24
  • And why not just use a Pivot Table? – Michal Rosa May 07 '19 at 05:48
  • Hello @MichalRosa good day! make a Pivot Table of those 2 where I concat them? – Suomynona May 08 '19 at 00:45

1 Answers1

2

This is what I ended up using, edit to your situation.

enter image description here

Solar Mike
  • 7,156
  • 4
  • 17
  • 32
  • hello sir! thank you so much for this, still testing though, may I know what is the purpose of `ROW($1:$30)` code in your example? – Suomynona May 07 '19 at 05:34
  • I've tested some more stuff, still confused with the purpose of `ROW($1:$30)`, should it be static sir? – Suomynona May 07 '19 at 06:04
  • hellosir @SolarMike some of the cells are producing an error message `#NUM!` – Suomynona May 07 '19 at 06:17
  • 1
    Then you must have one of the ranges or arguments incorrect, this works on 5 different sets of data in my real sheet, and it works in that example as I had to reset the names. – Solar Mike May 07 '19 at 06:19
  • Brilliant! tested everything and made adjustments in the `ROW($1:$30)` part, change the **$30** according to the maximum or highest cell address to be included for example the range of your column reaches **E99** then adjust the code to `ROW($1:$99)` then I'm able to fetch the distinct publication names, no more duplicates! thank you very much for this sir @SolarMike :) – Suomynona May 07 '19 at 07:38
  • This is my final code `=INDEX($E$4:$E$200,AGGREGATE(15,6,ROW($1:$200) / ($J$4:$J$200=M4), COUNTIF($M$4:M4, M4)))` works like a charm! Again, thank you very much sir @SolarMike – Suomynona May 07 '19 at 07:39
  • additional question sir @SolarMike, might as well ask this from you since you're experienced with this. When I applied the function / code I have mentioned above directly to the excel file, I don't encounter any problem, but when placed it in my php code while using the PHPSpreadsheet library, it says the following error `Wrong number of arguments for INDEX() function: 5 given, between 1 and 4 expected' `. Do you have any idea about this, sir? – Suomynona May 07 '19 at 07:54
  • Sounds like you have lost a bracket or have a "," in the wrong place. BUt php may not like the way excel uses index... – Solar Mike May 07 '19 at 08:04
  • sir @SolarMike, I have no extra "," in the code / script, it's the exact code placed in the Formula Bar – Suomynona May 07 '19 at 09:59
  • Think about " But php may not like the way excel uses index.." or AGGREGATE()... as I suggested to your question here : https://stackoverflow.com/q/56018814/4961700 – Solar Mike May 07 '19 at 10:20
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/192996/discussion-between-jueviole17-and-solar-mike). – Suomynona May 08 '19 at 03:56