well I tried the first step:
if number of B2,C2 pairs=F2 then just E2
else write E2 and "," and content of cell from this column (but below) where B,C are the same and count of pairs (F) is one greater than F2
first is probably clear enough, so about "else part":
the innermost part of formula is:
there could be only one row where all 3 criteria ar met:
because in every row where
F is increased by one (F counts a number of pairs BC above and including given row)
So the sumifs will really not "sum", but return just this one element from column A
(ok, sum of one element is this element anyway - this is why I wrote about numeric values in column A)
I used only part of columns (for instance A3:A$2000 - you noticed mixed addressing?) below current row, because anyway we dont expect meeting column F criteria above, and this way excel will have less data to crunch.
Then it is easier to understand:
MATCH returns position of this element in Column A (again only part of column below current row)
INDEX returns value from column G on this position (OFFSET could be used here as well instead of index - may be would be even a bit faster? I haven't tried).
So let's take the case where you have triplicate ;-) of BC pair (717029,4 7046365,0).
In the bottommost row where BC pair is met (row 12 in my attachment) in G12 you have just the same as in E12 (22)
going up we have row 11 where again the same BC pair showed up so in G11 we have E11&","&G12 (4,22)
going further up we find row 8 when again the BC pair is met so in G8 we have E8&","&G11 (7,4,22)
Similar - row 10 (first from bottom occurence of 717004,7 7046368,4) so only E10 in G10 and going up row 7 with the same pair so E7&","&G10 in G7
And that's it. (I am just to push button "Post Quick Reply", but it wasn't so quick )
Bookmarks