+ Reply to Thread
Results 1 to 10 of 10

Consolidate values for duplicates

  1. #1
    Registered User
    Join Date
    11-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    99

    Consolidate values for duplicates

    Can you please help me figure out how to consolidate each value for duplicate numbers into one cell?

    Please see the example of what I need in"Example" tab in the attachment. For STRT_DT and SITE_DISPL_DT columns in DUPLICATE VALUE table I will need to list these values in two different columns if there are two dates per SKU_ID. And I will need to summarize the values in the below columns for the same SKU_ID.

    COM STORE
    FFLQTY FFLREV DC_ASP RET_QTY RET_REV RET_ASP


    Thank you so much!
    Attached Files Attached Files
    Last edited by Neyme; 03-14-2018 at 04:09 PM.

  2. #2
    Registered User
    Join Date
    12-21-2015
    Location
    United States
    MS-Off Ver
    2016
    Posts
    62

    Re: Consolidate values for duplicates

    Hi there, assuming you would never want the duplicated dates per SKU to show up, and you would always want the highest review number then I would use these maybe.

    All the below formulas will relate to your "Unique" tab

    In C3 and down: Must be done with Ctrl+Shift+Enter
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In D3 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In E3 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In F3 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In G3 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In H3: drag down, and right.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    11-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    99

    Re: Consolidate values for duplicates

    Hi Skiptomylou,

    Thank you so much for the quick turn around. All formulas work but the the formulas in D3, E3, F3 G3. I attached the file with the formulas that you built for me. Can you please help to fix the formulas in these cells?
    Attached Files Attached Files

  4. #4
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Consolidate values for duplicates

    A different approach, based on the AGGREGATE function (mimicking SMALL). Named range Data = DUPLICATE!$A$2:$K$31.
    In C3:
    Please Login or Register  to view this content.
    In D3:
    Please Login or Register  to view this content.
    E3, F3, and G3 are similar, varying only at red numbers. The IFERROR(1/(1/...),"") part is just an efficient way to show blanks instead of zeros.
    The rest are standard SUMIF formulae.
    Attached Files Attached Files
    Last edited by leelnich; 03-15-2018 at 10:40 AM.
    Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee

  5. #5
    Registered User
    Join Date
    11-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    99

    Re: Consolidate values for duplicates

    Hi Skiptomylou, I have Excel 2013 where MINIFS/MAXIFS formula is not available. I think this is the problem why the formula doesn't work.

  6. #6
    Registered User
    Join Date
    11-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    99

    Re: Consolidate values for duplicates

    Hi leelnich,

    The formulae is PERFECT. Works like a charm.

    Thank you so, so much to taking time to help me!!! I added reputation to you.

  7. #7
    Registered User
    Join Date
    12-21-2015
    Location
    United States
    MS-Off Ver
    2016
    Posts
    62

    Re: Consolidate values for duplicates

    I apologize about that. Incidentally, the same thing could be done by utilizing Max(IF( in the same fashion with an array (which was how I originally did it), but avoiding arrays I went the other direction.

    It looks like you have a solution though Glad I could help with some of it.

  8. #8
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Consolidate values for duplicates

    Happy to help! If concluded, please mark your thread as SOLVED (Thread Tools above post #1). Regards – Lee
    PS- No big deal, but I'm not seeing the rep. Did you click on the star under MY post?

  9. #9
    Registered User
    Join Date
    11-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    99

    Re: Consolidate values for duplicates

    I found the star and was able to add rep to your profile!

  10. #10
    Registered User
    Join Date
    11-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    99

    Re: Consolidate values for duplicates

    Quote Originally Posted by Skiptomylou View Post
    I apologize about that. Incidentally, the same thing could be done by utilizing Max(IF( in the same fashion with an array (which was how I originally did it), but avoiding arrays I went the other direction.

    It looks like you have a solution though Glad I could help with some of it.
    Hi Skiptomylou, your help is vERY appreciated. No need to apologize. I added the reputation to ypour profile. Have a wonderful day!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Sum values that correspond to
    By Geoff. in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-16-2014, 07:54 PM
  2. Replies: 2
    Last Post: 06-25-2012, 02:10 PM
  3. decimal correspond to full number
    By maanrehman2001 in forum Excel General
    Replies: 5
    Last Post: 04-01-2012, 11:16 AM
  4. Looking up specific values which correspond to a number
    By asbccontractorsltd in forum Excel General
    Replies: 1
    Last Post: 03-01-2012, 07:35 AM
  5. Making cell values correspond with Drop down List values
    By jrcream in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-29-2008, 03:14 AM
  6. Correspond values of one row to another
    By wood929 in forum Excel General
    Replies: 1
    Last Post: 12-04-2005, 06:40 PM
  7. Replies: 0
    Last Post: 08-29-2005, 07:45 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1