+ Reply to Thread
Results 1 to 7 of 7

Change Value in a separate Column if there are duplicates

  1. 01-22-2024, 07:40 AM #1
    chyeenne
    chyeenne is offline
    Registered User
    Join Date
    01-22-2024
    Location
    Singapore
    MS-Off Ver
    Microsoft 365 Version 2312
    Posts
    13

    Change Value in a separate Column if there are duplicates

    I have a list of products with different "steps". if there are duplicates in consecutive rows, i would like the Quantity in the Total Row to reflect the minimum value rather than the sum of the cells that contain the duplicates. How do I do this using VBA? Thank you in advance


    Book1.xlsmphoto1705923494 (1).jpegphoto1705923494.jpeg
    Last edited by chyeenne; 01-23-2024 at 09:11 PM.
    Reply With QuoteRegister To Reply
  2. 01-22-2024, 10:16 AM #2
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    47,092

    Re: Change Value in a separate Column if there are duplicates

    If you want us to work on a Pivot Table, you need to provide the raw data.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 01-22-2024, 10:38 AM #3
    Kaper
    Kaper is offline
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,825

    Re: Change Value in a separate Column if there are duplicates

    But if you have a table produced other way, or just ciopied and pasted, the main issue is that you tested both productname and quantity in column 2 (B).

    Assuming your data starts in A1 this code shall do the job:


    Please Login or Register  to view this content.
    Attached Files Attached Files
    Best Regards,

    Kaper
    Reply With QuoteRegister To Reply
  4. 01-22-2024, 12:19 PM #4
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    14,484

    Re: Change Value in a separate Column if there are duplicates 

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Sintek; 01-22-2024 at 12:23 PM.
    Good Luck...
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
    Also....Add a comment if you like!!!!
    And remember...Mark Thread as Solved...
    Excel Forum Rocks!!!
    Reply With QuoteRegister To Reply
  5. 01-22-2024, 11:22 PM #5
    chyeenne
    chyeenne is offline
    Registered User
    Join Date
    01-22-2024
    Location
    Singapore
    MS-Off Ver
    Microsoft 365 Version 2312
    Posts
    13

    Re: Change Value in a separate Column if there are duplicates

    hi kaper thank you SO much! your code works excellently. may i know if there is a way to set a condition that if any of the quantity is 0, the total will be the lowest value above 0 instead of 0?
    Reply With QuoteRegister To Reply
  6. 01-23-2024, 03:24 AM #6
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    14,484

    Re: Change Value in a separate Column if there are duplicates

    Tx for rep...I know you did not ask me...just for completion...
    total will be the lowest value above 0 instead of 0? 
    Please Login or Register  to view this content.
    Last edited by Sintek; 01-23-2024 at 03:53 AM.
    Reply With QuoteRegister To Reply
  7. 01-23-2024, 09:01 AM #7
    Kaper
    Kaper is offline
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,825

    Re: Change Value in a separate Column if there are duplicates

    You can add second test to final IF.
    But what in case when 0 is in first row of given product?

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Preventing duplicates in separate sections of the same column
    By niall2012 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-26-2020, 12:57 AM
  2. [SOLVED] Macro to count duplicates then number them in a separate column
    By paperbo in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 01-17-2018, 09:25 AM
  3. Formula for Listing Unique Values from a Separate Column Containing Duplicates
    By xtinct2 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-21-2015, 03:54 PM
  4. List duplicates in separate column
    By gillmacca in forum Excel General
    Replies: 11
    Last Post: 06-15-2013, 02:12 AM
  5. Copy Multiple Columns of Data from Duplicates in 1st Column into Separate Sheets
    By jdotking in forum Excel General
    Replies: 3
    Last Post: 06-12-2012, 01:33 PM
  6. Compare rows and display duplicates in a separate column
    By sans in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 11-06-2011, 05:30 PM
  7. How do I add the values of duplicates together in a separate column?
    By banestyrelsen in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 03-31-2008, 06:23 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1