1

SO, trying to brainstorm if this is possible. You guys are my go to. First I needed to call out the Duplicates. Which I can do and did with this formula.

=IF((B16&C16)=(B15&C15),"X",IF((B16&C16)=(B17&C17),"X",""))

But then I need to call out the greater price number out of the duplicate value? Is this possible? So like AC 41000 is a Duplicate because it shows up more than once, but I need to call out $6.42 as the higher price between the duplicate pair.

Data Sample

enter image description here

Sam Russo
  • 145
  • 1
  • 3
  • 18

1 Answers1

1

Try,

=AGGREGATE(14, 6, (F$2:INDEX(F:F, MATCH(1E+99, F:F)))/((B$2:INDEX(B:B, MATCH(1E+99, F:F))=B2)*(C$2:INDEX(C:C, MATCH(1E+99, F:F))=C2)), 1)

enter image description here

At first I followed your logic but quickly realized that your formula would be better as,

=if(countifs(B:B, B2, C:C, C2)-1, "X", "")

That led me to a simple two column pseudo-MAXIFS with AGGREGATE.

If you want to show the largest value when the row contins the highest value and an X if not then you have to double up the formula.

=IF(AGGREGATE(14, 6, (F$2:INDEX(F:F, MATCH(1E+99, F:F)))/((B$2:INDEX(B:B, MATCH(1E+99, F:F))=B2)*(C$2:INDEX(C:C, MATCH(1E+99, F:F))=C2)), 1)=F2, AGGREGATE(14, 6, (F$2:INDEX(F:F, MATCH(1E+99, F:F)))/((B$2:INDEX(B:B, MATCH(1E+99, F:F))=B2)*(C$2:INDEX(C:C, MATCH(1E+99, F:F))=C2)), 1), "X")

enter image description here

  • Oh man, that's super complex. I'm trying to read it. Whats does 'le99' mean? – Sam Russo Mar 22 '17 at 00:48
  • It's not correct, working on the second criteria now. –  Mar 22 '17 at 00:50
  • AGGREGATE is like SUMPRODUCT. You should not use full column references because **everything** will get processed (down to B1048576). `MATCH(1E+99, F:F)` is a way to quickly return the row number of the last number, in this case in column F:F. –  Mar 22 '17 at 00:59
  • btw, Excel 2016 does have a native [MAXIFS function](https://support.office.com/en-us/article/MAXIFS-function-dfd611e6-da2c-488a-919b-9b6376b28883). –  Mar 22 '17 at 01:05
  • This does what I need it to but not really. Lol. I wish it would just show the lesser value as a blank cell instead of filling all the way down. But you went beyond. It literally could have just had a "X" next to the greater value in the duplicate pair. – Sam Russo Mar 22 '17 at 01:16
  • See above for *highest or X*. –  Mar 22 '17 at 01:21
  • I do have 2016 btw. – Sam Russo Mar 22 '17 at 01:24
  • Hey Jeeper, I may just be tired. But both those formulas look the same? The one for the X – Sam Russo Mar 22 '17 at 01:27
  • If you have Excel 2016 then all of that complicated formula can be reduced to `=MAXIFS(F:F, B:B, B2, C:C, C2)` or `=IF(MAXIFS(F:F, B:B, B2, C:C, C2)=F2, MAXIFS(F:F, B:B, B2, C:C, C2), "X")`. It used to be that MAXIFS (and some others) were only in Excel 2016 if you had Office 365 but my Excel 2016 does not have Office 365 and it does have MAXIFS. –  Mar 22 '17 at 01:27
  • @SamRussoPalmer - Yes, just a bad paste somehow. The formula as in the image was correct and I've corrected the formula above now. –  Mar 22 '17 at 01:29
  • Hmmmmmm I have 2016, but it doesn't seem to recognize the MAXIFS function – Sam Russo Mar 22 '17 at 10:47
  • I suppose it could be my tablet version of xl2016. –  Mar 22 '17 at 13:45