+ Reply to Thread
Results 1 to 13 of 13

Consolidating Duplicate Values

  1. #1
    Registered User
    Join Date
    12-18-2018
    Location
    Phuket, Thailand
    MS-Off Ver
    Windows 10 - MS Office 2016
    Posts
    77

    Consolidating Duplicate Values

    Hi there.

    I have a table, columns A through J, that has a lot of duplicated data. I would like a formula that consoldates any values that are matched in collumn D (PID), for collumns E, F, G, H & I. Collumns A, B & C do not consoldate, they just keep one value.

    Thank you so much!

    Danny
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,986

    Re: Consolidating Duplicate Values

    You keep labelling your workbooks Mr Excel - are you aware that this is not the Mr Excel forum?

    I will have a look at your workbook presently.
    Last edited by AliGW; 09-01-2020 at 02:58 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,986

    Re: Consolidating Duplicate Values

    Select columns A to J - Data ribbon - remove duplicates.

    You have a lot of open threads where you have received help, but given your helper no feedback whatsoever. We do expect members to at least thank those who have offered help - it's common courtesy. Thanks.

  4. #4
    Registered User
    Join Date
    12-18-2018
    Location
    Phuket, Thailand
    MS-Off Ver
    Windows 10 - MS Office 2016
    Posts
    77

    Re: Consolidating Duplicate Values

    Hi Ali,

    My apologies for my lack of gratitude! I am extremely grateful for the help I receive, I will be sure to be more vocal in the future.

    Yes - the file name is indeed incorrect.

    In regards to this post, I need to consolodate the values in columns E, F, G, H & I, as I will be using this file as a template for more data.

    Are you (or another) able to assist me with a formula for the above?

    Kindest regards,

    Danny

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,986

    Re: Consolidating Duplicate Values

    I have explained how to do it in post #3.

  6. #6
    Registered User
    Join Date
    12-18-2018
    Location
    Phuket, Thailand
    MS-Off Ver
    Windows 10 - MS Office 2016
    Posts
    77

    Re: Consolidating Duplicate Values

    Hi Ali,

    Unfortunately that doesn't consoldate the values in columns E, F, G, H & I - it leaves the non-removed value only.

    Or am I doing something wrong?

    Thanks again.

    Danny

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,986

    Re: Consolidating Duplicate Values

    Well, what you are NOT doing is giving us any examples of what you want, so it's all guesswork!

    For instance, how do you expect rows 9 to 25 (orange juice) to look in your consolidated table?

  8. #8
    Registered User
    Join Date
    12-18-2018
    Location
    Phuket, Thailand
    MS-Off Ver
    Windows 10 - MS Office 2016
    Posts
    77

    Re: Consolidating Duplicate Values

    Ok - for the example of orange juice (rows 9-25), it would look like this:

    COLUMNS:
    A - PRODUCT NAME - remove duplicates only (not consolidated)
    B - VAT RATE - remove duplicates only (not consolidated)
    C - VAT AMOUNT - remove duplicates only (not consolidated)
    D - PID - remove duplicates only (not consolidated) (THIS IS THE COLUMN THAT DRIVES THE FORMULAS)
    E - AMOUNT - consolidate
    F - PRICE - consolidate
    G - TOTAL - consolidate
    H - COST VAT - consolidate
    I - PROFIT - consolidate
    J - CATEGORY TYPE - remove duplicates only (not consolidated)

    Orange Juice:
    A - PRODUCT NAME - Fresh Orange Juice
    B - VAT RATE - 7
    C - VAT AMOUNT - 8.83
    D - PID - DKS18 (THIS IS THE COLUMN THAT DRIVES THE FORMULAS)
    E - AMOUNT - 54
    F - PRICE - 255
    G - TOTAL - 810
    H - COST VAT - 0
    I - PROFIT - 757.08
    J - CATEGORY TYPE - Unknown (product deleted)

    Thank you so much!

    Danny

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,986

    Re: Consolidating Duplicate Values

    Please provide a workbook showing what you expect those rows to look like AFTER consolidation. You are not telling us HOW you expect each column to be consolidated.

    Still no clearer - sorry.

  10. #10
    Registered User
    Join Date
    12-18-2018
    Location
    Phuket, Thailand
    MS-Off Ver
    Windows 10 - MS Office 2016
    Posts
    77

    Re: Consolidating Duplicate Values

    Hi AliGW,

    I have attatched a preview of what I want the final file to look like.

    Rows highlighted in yellow are those that had duplicate values.

    The calls with red text are those I want consolidating.

    I hope that makes sense!

    Danny
    Attached Files Attached Files

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,986

    Re: Consolidating Duplicate Values

    OK - this is done with PQ groupings.

    M Code:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    12-18-2018
    Location
    Phuket, Thailand
    MS-Off Ver
    Windows 10 - MS Office 2016
    Posts
    77

    Re: Consolidating Duplicate Values

    Hi AliGW,

    That's wonderful - thank you so much!!!!

    Danny

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,986

    Re: Consolidating Duplicate Values

    No worries. There are links to PQ info in my signature.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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. Replies: 4
    Last Post: 12-05-2019, 11:02 AM
  2. Replies: 8
    Last Post: 10-03-2017, 03:10 PM
  3. Consolidating duplicate data and recording # of duplicates
    By excel.junkie in forum Excel General
    Replies: 1
    Last Post: 09-11-2013, 10:26 AM
  4. Replies: 0
    Last Post: 02-17-2012, 11:28 AM
  5. Consolidating Sums from Duplicate Records
    By lookingformacros in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-30-2010, 04:05 PM
  6. Consolidating duplicate data
    By dbwiz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-30-2010, 06:47 AM
  7. Consolidating rows with duplicate information
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-05-2008, 11:48 PM

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