0

I have a large data set (more than 1,000 items) scoring the relationship between two items. Something like:

----------------------------
| item1  | item2   | score |
|--------------------------|
| apple  | orange  | 1     |
| apple  | banana  | 5     |
| apple  | lemon   | 3     |
| banana | apple   | 2     |
| lemon  | orange  | 3     |
| lemon  | apple   | 1     |
| ...etc.|         |       |
----------------------------

I need to find the smallest score for each item1 and sort them like:

-----------------------------
| item1  | item2    | score |
|---------------------------|
| apple  | orange   | 1     |
| banana | apple    | 2     |
| lemon  | apple    | 1     |
-----------------------------

I can use filter to find the smallest one by one, but it is a massive task to find them for each. I know advanced filter should be able to solve this easily, but have not been able to find the way. Any help would be great. Thanks

  • Lemon, apple is 1 in your first sample. Why is it 2 in the second? – teylyn Sep 28 '16 at 4:23
  • @teylyn my mistake – vabm Sep 28 '16 at 4:49
2

That's what pivot tables can do without formulas and just a few clicks.

Click in the source data and then Insert > Pivot Table. From the Field List, drag Item1 into the rows area, drag Score into the Values area, then right-click "Sum of Scores" and change "Value Field Settings" to "minimum".

enter image description here

You can sort the pivot table by the values of the score, too.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.