Results 1 to 2 of 2

Thread: Retaining information from duplicate cells after removing.

  1. #1

    Retaining information from duplicate cells after removing.

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Utah, USA
    Excel Version
    Version 2002 Build 12527.20194
    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))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts