Retaining information from duplicate cells after removing.

mmattil

New member
Joined
Mar 18, 2014
Messages
1
Reaction score
0
Points
0
Hello everyone, I am working on a sales workbook for a grocery store and I have an issue with duplicate entries. The sales data for a given time period counts sales when an item is on sale as a separate entry from sales when the item is not on sale. I want to find total sales for each item and then remove duplicate rows so the workbook is easier to analyze. I have attempted the following: find sales data by using the array function =sum( ($UPC column = UPC 1) *(sales column)). This gives me a sum for each entry but by then removing duplicates I cannot keep the correct sums because they reference rows I have removed. Is there a way I can merge two rows sales data and keep only the merged row? Or associate the sales sum with a UPC so when I remove duplicates I can retain the correct sum? Please let me know if I can provide additional information to clarify my problem. Thank you.
 
I did something similar today. Let me lay this out for you: lets say column A is your UPC codes and column B is your sales.
I copy all of column A and paste it in Column D. Then run the remove duplicates function on column D, I will usually sort this after removing the duplicates as well.
Then in Column E I do a formula to sum the column I want. So the formula would look like: =sum(if(d2=a:a,b:b,0)) remember to use a formula like this you need to enter it as an array formula by hitting ctrl+shift+enter. To speed up the formula try to enter it with the known ranges, =sum(if(d2=a2:a50,b2:b50,0))
 
Back
Top