2

I have a sheet that automatically pulls in social metrics from multiple accounts a few times a day, because of this I often have multiple lines of data for the same post. How do I filter the rows in a new sheet so that only the post ID (column F) with the highest number of impressions (column G) remains for each unique post?

Sample Data Set

The filtered result I want:

The filtered result I want

I've explored queries (not my strong suit) and filter formulas and can't seem to find a combination that does exactly what I need. Here's the formula that I've gotten to: 

=FILTER('Intermediate Data'!A2:L,MMULT(('Intermediate 
 Data'!H2:H>TRANSPOSE('Intermediate Data'!H2:H))*('Intermediate 
 Data'!F2:F=TRANSPOSE('Intermediate Data'!F2:F)),SIGN(ROW('Intermediate 
 Data'!F2:F)))<>0)

This code doesn't pull in all the post IDs---anyone has any ideas I can try?

player0
  • 124,011
  • 12
  • 67
  • 124
KArnold
  • 23
  • 4

2 Answers2

2
=ARRAYFORMULA(SORT(UNIQUE(VLOOKUP(
 QUERY(A2:C, "select B     where B is not null order by C desc", 0),
 QUERY(A2:C, "select B,C,A where B is not null order by C desc", 0), 
 {3, 1, 2}, 0)), 2, 1))

0

player0
  • 124,011
  • 12
  • 67
  • 124
2

You can also use sort/sortn:

=sortn(sort(A2:C,1,1,2,1,3,0),999,2,1,1,2,1)

enter image description here

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37