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
Bookmarks