Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select the UNIQUE vendors name
Dear All,
Please help how to replace the below function with the VBA solution: =OFFSET($G$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE($G$1:$G$14)=I$1:I1),ROW(INDIRE CT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1) the above formula work fast, when then data is not many. But when we would like to SHORTLISTED / UNIQUE data, it will be a long wait for Excel to finish the task. Data: Vendor Name A B A B C E E E A the Result of Unique Vendor will be A B C E TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select the UNIQUE vendors name
On Apr 24, 5:04*am, Andri wrote:
Dear All, Please help how to replace the below function with the VBA solution: =OFFSET($G$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE($G$1:$G$14)=I$1:I1),ROW(IN*DIR ECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1) the above formula work fast, when then data is not many. But when we would like to SHORTLISTED / UNIQUE data, it will be a long wait for Excel to finish the task. Data: Vendor Name A B A B C E E E A the Result of Unique Vendor will be A B C E TIA You can use pivot table for that or use following (Change thee range address) Range("YourRange").advancedfilter action:=xlfiltercopy,copytorange:=range("SetYourRa nge"),unique:=true this will copy your unique items in "yourrange" cells to "SetYourRange" cells |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select the UNIQUE vendors name
Hello Andri,
The following short program hi-lights your unique data lines in red. Another way: copy your entire column somewhere way down on the sheet. Use eliminate duplicates on your command list. Best Regards, Gabor Sebo Sub formatunique() With Range("a1:a29") 'unique words hi-lighted in red .Select .FormatConditions.Delete .FormatConditions.AddUniqueValues .FormatConditions(1).DupeUnique = xlUnique .FormatConditions(1).Interior.Color = RGB(255, 0, 0) End With End Sub ----------------------------------------------------------------------------------------------------------- "Andri" wrote in message ... Dear All, Please help how to replace the below function with the VBA solution: =OFFSET($G$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE($G$1:$G$14)=I$1:I1),ROW(INDIRE CT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1) the above formula work fast, when then data is not many. But when we would like to SHORTLISTED / UNIQUE data, it will be a long wait for Excel to finish the task. Data: Vendor Name A B A B C E E E A the Result of Unique Vendor will be A B C E TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I do a cost comparison of products from many vendors? | New Users to Excel | |||
load one vendors prices to a master workbook | Excel Worksheet Functions | |||
monthly/yearly expence by vendors | New Users to Excel | |||
Multiple vendors and scenarios - Excel | Excel Worksheet Functions | |||
VBA Code to list vendors | Excel Programming |