I went ahead with it anyway. (Couldn't resist the challenges. )
By the way ... thank you for clarifications in post #3. That helps a lot.
I won't elaborate on each of the formulas in the attached. I will note that in column A I completed the list of BvD IDs. If you like I will explain a short cut method for doing this.
Just know that offset row headers share the same difficulties as merged cells. They are fine for summaries where cosmetics are important. In source data they are the devil's spawn, create havoc in formulas and should be avoided always.
Also in column G is a formula to return the initial row number of each block of BvD IDs. It is one of 3 helper columns. Once you are satisfied understanding what they do ... if it were me ... I would copy and paste values only back onto each range. It will reduce the file size.
This formula in J2 is array entered and filled down to L2633.
Formula:
=IFERROR(1/(1/COUNTIF(INDEX(D$2:D$2649,$G2):INDEX(D$2:D$2649,$G2+$H2-1),INDEX(INDEX(D$2:D$2649,$G2):INDEX(D$2:D$2649,$G2+$H2-1),
SMALL(IF(FREQUENCY(MATCH(INDEX(D$2:D$2649,$G2):INDEX(D$2:D$2649,$G2+$H2-1),INDEX(D$2:D$2649,$G2):INDEX(D$2:D$2649,$G2+$H2-1),0),
ROW(INDEX($A:$A,1):INDEX($A:$A,$H2))),ROW(INDEX($A:$A,1):INDEX($A:$A,$H2))),COUNTIF($A$2:$A2,$A2))))),"")
I also adjusted formulas in N:P and R:T to ignore blank lines.
I am at a loss regarding the final formulas in V:X. Did you intend for those to execute only on the first row of each BvD ID block? I must confess my grasp of 'Herfindahl index' is a bit anemic. So I really didn't know what to do with those.
Bookmarks