All geek questions in one place

Excel: removing duplicates in one column while maintaining maximum value in adjacent column

I have an Excel spreadsheet with two columns. The first column is the label, the second column is a numeric value. I would like to remove all duplicate labels in column "A" and stay with the maximum numerical value in column "B". I tried to illustrate below (the filter will result in the "Consolidated Sheet" specified in the "Original Sheet":

NOTE 1. I am trying to parse the mail server log files to get a list of hosts and their maximum number of SMTP connections on any given day. If you can suggest a way to do this on windows (even if it means using Strawberry Perl), I would appreciate a solution.

NOTE 2: You may have to use your imagination using the “spreadsheet”, which I described below, I tried to put each row in a separate row, but all rows are displayed in one row. [EDIT] Fixed by putting everything in an unordered list.

"Original sheet"

  • colA, colB
  • name1, 32.0,
  • name1, 12.5,
  • name1, 11.0,
  • name1, 10.1,
  • name1, 6.9,
  • name2, 2.3,
  • name2, 1.1,
  • name2, 0.9,
  • name3, 54,
  • name3, 60,
  • name3, 101,
  • name3, 12,

Consolidated Sheet

  • colA, colB
  • name1, 32.0,
  • name2, 2.3,
  • name3, 101,

Sincerely, EMC

+3
source share
2 answers

Could you just use the functionality of the Excel Pivot Table?

+4
source

" " .

, , .

, a2: a13, d2: d4 ( ). e2 = SUMPRODUCT (MAX (($ A2: $A13 = D2) * ($ B2: $B13)))

Ctrl + Shift + Enter, Enter.

, JDunkerley, .

0

Source: https://habr.com/ru/post/1718005/

More articles:


All Articles