Excel question (need answer in next 5-6 hour)

CFA General
#1

I have one small question. Can someone please help ? There is two column in the excel work sheet. In one column several numbers are there. and in second column also there are numbers. Numbers in column 1 is repeated. I need to find 10 numbers of column 1 for which sum of related numbers of column 2 is maximum. Example 12345 12 23145 43 23456 35 23145 98 23145 789 23145 898 23145 1234 23145 3 12345 567 12345 678 12345 3456 12345 7890 23145 6787 23145 16 Here two numbers of column 1 for which sum of related numbers of column 2 is maximum is 23145 and 12345. This is what I want. Is there any method for finding this out ? Actually it may be a simple problem but I dont have much idea of excel functions Thanks

#2

highlight both. sort descending by second column? that sounds like all you’re trying to do.

#3
  1. Key your 10 numbers into a new column (example below assumes D1 is the first number that you’re interested in). 2. In cell E1, insert the following formula - NOTE that this is an array formula - hit CTRL- SHIFT - ENTER after inputting formula. You’ll know you’ve done it correctly if curly backets appear in the formula bar =MAX(IF($A$1:$A$100=$D1,$B$1:$B$100)) 3. Drag this formula down for the remaining nine numbers that you’re interested in. Note that this example assumes your two columns of data are in A1 : B100. Adjust as necessary
#4

So, if I am reading this correctly, you want to extract the index numbers in column 1 for which the sum of all their column 2 entries is the highest. If so, there are two very easy ways to do this: 1) If you have a list of all unique indexes in column 1, use SUMIF(). Then sort based on the results of the SUMIF() values. This function is self explanatory - use Excel search for the specs. 2) Use a pivot table. Highlight the data, then go to the Data menu and choose “Pivot Table Report…”. Put column 1 values in the left row field and column 2 in the data field, with field settings = “sum”. This will give you a a list of unique column 1 values and their corresponding column 2 sums.

#5

Yeah, the pivot table that Mister Walrus proposed sounds like the best way. Then just copy the data to a clean sheet and sort by sum.

#6

Pivot Table (sum of b NOT count of b) sort done

#7

Problem solved !! what I did is this : Column A data 1 Column B Data 2 Column C unique numbers of column 1 (filtered) Column D = sumif(A1:A100,C1,B1:B100) Then Filter top 10 values of column D

#8

Yay! Homework done/Not fired!

#9

Thank you guys for help