+ Reply to Thread
Results 1 to 2 of 2

Formula that removes duplicates - got that, but how do I expand it over more colunns?

  1. #1
    Registered User
    Join Date
    03-23-2005
    Posts
    3

    Formula that removes duplicates - got that, but how do I expand it over more colunns?

    Hello,

    I have a list A2...A11

    In cell B1 is the following formula:
    =SUM(IF(FREQUENCY(IF($A$2:$A$11<>"",MATCH("~"&$A$2:$A$11&"",$A$2:$A$11&"",0)),Rvec),1))

    Rvec is defined in NAME MANAGER as: =ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1

    In cells B2...B11 is this formula:
    =IF(ROWS($B$2:B2)<=$B$1,INDEX($A$2:$A$11,SMALL(IF(FREQUENCY(IF($A$2:$A$11<>"",MATCH("~"&$A$2:$A$11&"",$A$2:$A$11&"",0)),Rvec),Rvec),ROWS($B$2:B2))),"")

    So, what happens is, the user chooses options from drop-down boxes in A2...A11, and in cells B2...B11, the above formulas work out if any items have been duplicated and then displays the list, only showing the duplicated items once.

    PERFECT! I take no credit for the above, I found it on the net, unfortunately I have just tried to find where I found it, but can't seem to find it anymore, so apologies if indeed it came from this forum that I cannot name you for your good work.

    Anyway, I want to move the above formula's along a few columns, so I can add the following:

    Column A = Name of Item
    Column B = Quantity
    Column C = Gross Weight
    Column D = Net Weight
    Column E = Cube

    So, Column F would now become the list where duplicated items are only displayed once.

    My issue is, as you can see from the nature of the added columns, if in column A the following data appeared:

    A B C D E
    Car 4 100 80 1.2
    Car 3 90 70 1.1

    How can I amend the formula so that it reads:
    F G H I J
    Car 7 190 150 2.3


    Your help on this one is greatly anticipated, and I look forward to any comments/suggestions
    Best regards
    manc

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Formula that removes duplicates - got that, but how do I expand it over more colunns?

    can you upload example workbook?

    Nice posts/year average btw

+ 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. Macro that searches through column and removes specific duplicates
    By bruizer31 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 05-17-2013, 02:39 PM
  2. [SOLVED] Removes Duplicates Except Blanks macro
    By ScabbyDog in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-06-2013, 09:49 AM
  3. [SOLVED] Macro removes duplicates but Charts blanks - Help needed
    By agricola in forum Excel General
    Replies: 13
    Last Post: 09-03-2012, 05:54 PM
  4. Replies: 3
    Last Post: 01-23-2012, 03:55 AM
  5. How do I merge two spreadsheets so it removes the duplicates?
    By Megan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-10-2005, 11:06 AM

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