Multiplication of string and values

Occasional Contributor

Hi everyone,


I have a Excel file where there two columns, one is Description (Column A) and Quantity (Column B). Now in that Description Column there are items which are repeating(has exact same description such as Row1 has ABCD written so same Row 7, 15, 56 has exact same text) but values in Quantity column is different. Now I want to merge all who has same rows who has same text (For example row 1,7,15,56 has same text) so all rows should merge into single row but they have different values in quantity column for that I need addition of all those values into singe row. For Example, Row 1, 7,15,56 has same text string (John) but all quantity column has different values respectively 667, 56, 67, 12, so I want row 1,7,15,56 in single row (john) and in quantity column values should be addition of all those rows(667, 56, 67, 12 = 802). There are many similar kind of values so I need to apply this thing on entire sheet.


Thank you

2 Replies



A picture is worth a 1000 words.  And an Excel file is worth 1000 pictures.


Ideally, click "browse" at the bottom of the reply window to attach an Excel file.  The Excel file should demonstrate expected results, which you might cobble together manually,


If the forum does not permit that (yet), upload the Excel file to a file-sharing website, and post the download URL.  I like; others like  You might like because it uses the same login as this forum.  But in that case, IMHO, be sure that the download URL does not allow others to edit the file.  Thus, others cannot make changes by accident (or on purpose).  (I'm all thumbs.  sigh)


If the forum does not permit you to post URLs (yet), spell out the URL.  For example, the URL for this thread is techcommunity dot microsoft dot com /t5/excel/multiplication-of-string-and-values/td-p/3647602


I'd create a pivot table based on the data.

Add the description field to the Rows area and the quantity field to the Values area.