mbbx5va2
08-17-2014, 08:24 AM
Hi
Suppose in sheet1 in column A I have integervalues in the cells until row n and column B contains surnames of individualsagain with n rows. So I have a 2 by n matrix consisting of numbers and text. NowI have a situation where there are multiple duplicated surnames so that I mayget Smith 5 times or Jones twice for instance in column B.
Now I want to go through column B and find allthe groups of duplicate surnames and then add the corresponding integer incolumn A. So if there are two instances of Jones each giving 35 and 45respectively then I would like the number 80 to appear in sheet2 cell A1together with the surname Jones in cell B1.
So the end result is another matrix in sheet2but with unique surnames and no duplicates in column B. I would like all ofthis done using a macro button in sheet1.
Now if I wanted to do this in Excel I would use the sort function to get all the surnames grouped together then one would create a vlookup for each unique surname but this is too time consuming. I don't want to spend time writing a different surname within the first argument of the vlookup.
Initially I am thinking of defining a 2 dimensional array and assigning it to the data set. Next I am thinking of a for next loop from x =1 To y which tells me the position each time a specific surname appears in column B. Now the Match function e.g
Match(x,"B:B",0) tells me the position of one but not all occurrences. If I know the position of all occurrences then I suppose I can just sum a set of references to the array.
Any thoughts much appreciated? :)
Suppose in sheet1 in column A I have integervalues in the cells until row n and column B contains surnames of individualsagain with n rows. So I have a 2 by n matrix consisting of numbers and text. NowI have a situation where there are multiple duplicated surnames so that I mayget Smith 5 times or Jones twice for instance in column B.
Now I want to go through column B and find allthe groups of duplicate surnames and then add the corresponding integer incolumn A. So if there are two instances of Jones each giving 35 and 45respectively then I would like the number 80 to appear in sheet2 cell A1together with the surname Jones in cell B1.
So the end result is another matrix in sheet2but with unique surnames and no duplicates in column B. I would like all ofthis done using a macro button in sheet1.
Now if I wanted to do this in Excel I would use the sort function to get all the surnames grouped together then one would create a vlookup for each unique surname but this is too time consuming. I don't want to spend time writing a different surname within the first argument of the vlookup.
Initially I am thinking of defining a 2 dimensional array and assigning it to the data set. Next I am thinking of a for next loop from x =1 To y which tells me the position each time a specific surname appears in column B. Now the Match function e.g
Match(x,"B:B",0) tells me the position of one but not all occurrences. If I know the position of all occurrences then I suppose I can just sum a set of references to the array.
Any thoughts much appreciated? :)