RichardR
Reputation
Top tag
Next privilege 15 Rep.
Flag posts
1 5
Impact
~3k people reached

• 0 posts edited

17 Actions

 Jan29 awarded Notable Question Dec15 awarded Popular Question Jan31 comment Combine duplicates in Excel, sum some columns and weighted average the others Hi Nixda, you get me wrong, it's brilliant. Thank you so much. I only added my last comments in case someone else came and looked at the thread. I have been merrily changing your code already to help with other sheets. Thank you again. Jan30 revised Combine duplicates in Excel, sum some columns and weighted average the others Corrected an error Jan30 comment Combine duplicates in Excel, sum some columns and weighted average the others Hi, I had made a mistake in my earlier request, the cost/conv column. It does NOT want to be an average weighted by clicks it wants to be weighted by conversions OR as I did it, by calculating afterwards from the total cost divided by total conversions(I had dealt with before I ran the macro by deleting the figures and putting in zeros). Jan30 comment Combine duplicates in Excel, sum some columns and weighted average the others Hi, amazing. You would not believe the trouble I had with this, it was giving me a run time error Overflow message, however I cut the sheet down in size. Until I sorted the spread sheet by Clicks! Then it ran fine, it did not like the sheet in either search term or conversion order. Thank you again. There must be others out there with Google reports like mine! Jan28 awarded Editor Jan28 revised Combine duplicates in Excel, sum some columns and weighted average the others new info added Jan28 comment Combine duplicates in Excel, sum some columns and weighted average the others I calculate a weighted average by dividing the clicks in that rows data by the total of clicks for that duplicated word, multiplying that fraction by the cell data and then summing the column of duplicates. The sum for the total looked like this: =(\$B2/\$B15*E2)+(\$B3/\$B15*E3)+(\$B4/\$B15*E4)+(\$B5/\$B15*E5)+(\$B6/\$B15*E6)+(\$B7/\$B15‌​*E7)+(\$B8/\$B15*E8)+(\$B9/\$B15*E9)+(\$B10/\$B15*E10)+(\$B11/\$B15*E11) This sum was on a test sheet that had ten rows. Do you think you could get this into the macro? Cheers Richard Jan28 awarded Scholar Jan28 accepted Combine duplicates in Excel, sum some columns and weighted average the others Jan28 comment Combine duplicates in Excel, sum some columns and weighted average the others Thank you for your help, I was unable to make this work. However one of your forum friends has produced a macro that did the job perfectly. Jan28 comment Combine duplicates in Excel, sum some columns and weighted average the others Dear Nixda, this is brilliant. Amazing it has enabled me to merge the data from over a thousand duplicates and make my next few weeks work so much better. THANK YOU. Well done. Jan26 awarded Student Jan25 awarded Autobiographer Jan25 comment Combine duplicates in Excel, sum some columns and weighted average the others I will happily put the info on another sheet. I can see how to remove the duplicates but I can't understand what you mean after that? My search report has 5000 rows each with a search term and its data, some of those search terms are identical but have individual data. I need to get to unique terms in the sheet but I need to have all the data that was associated with the duplicates that have been deleted, i.e. in the above example I need one row with "advent calendar" and the total clicks, total impressions etc garnered from the deleted rows? cheers Richard (lives in Cheshire, UK!) Weird? Jan25 asked Combine duplicates in Excel, sum some columns and weighted average the others