Prev: Extracting First Name from cell with first name and last name
Next: Floating Text Box in Excel 2K3/2K7
From: Andri on 23 Apr 2010 20:04
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(INDIRECT("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
From: Javed on 24 Apr 2010 03:33
On Apr 24, 5:04 am, Andri <An...(a)discussions.microsoft.com> 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­DIRECT("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("SetYourRange"),unique:=true
this will copy your unique items in "yourrange" cells to
"SetYourRange" cells

From: helene and gabor on 24 Apr 2010 10:28
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" <Andri(a)discussions.microsoft.com> wrote in message
news:4CC27AAC-AA5A-475D-9579-C8BB08A97570(a)microsoft.com...
> 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(INDIRECT("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
>

 | 
Pages: 1
Prev: Extracting First Name from cell with first name and last name
Next: Floating Text Box in Excel 2K3/2K7