From: Andri on
Dear Experts,

Please help related to the above subject.
From this discussion, got the following formula which is work well and
faster for small of database.
=OFFSET($G$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE(DATA)=I$1:I1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1)
where $G$1, the first row of DATA.

how to convert it to VBA as this time i have a huge database.

for Tab Data, Advanced, Unique Record only (works well also, but i need the
VBA).

basically we would like to do similar like this:
Data Sample

Vendor Name
A
B
F
B
C
E
E
E
A

the Result of Unique Vendor will be
A
B
F
C
E

TIA
From: Per Jessen on
Hi

Using VBA I would use an advanced filter and filter for unique entries, and
copy the result to another cell place.

Use the macro recorder to get started, and post the recorded macro in the
Programming group for further help.

Hopes this helps.
....
Per

"Andri" <Andri(a)discussions.microsoft.com> skrev i meddelelsen
news:8BDA0D14-8FE3-4B52-AD4A-3DAF07D19ED2(a)microsoft.com...
> Dear Experts,
>
> Please help related to the above subject.
> From this discussion, got the following formula which is work well and
> faster for small of database.
> =OFFSET($G$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE(DATA)=I$1:I1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1)
> where $G$1, the first row of DATA.
>
> how to convert it to VBA as this time i have a huge database.
>
> for Tab Data, Advanced, Unique Record only (works well also, but i need
> the
> VBA).
>
> basically we would like to do similar like this:
> Data Sample
>
> Vendor Name
> A
> B
> F
> B
> C
> E
> E
> E
> A
>
> the Result of Unique Vendor will be
> A
> B
> F
> C
> E
>
> TIA

From: Mike H on
Andri,

Try this code

Sub stance()
Dim MyRange As Range
Set sht = Sheets("Sheet1") ' Change to suit
Dim x As Long, LastRow As Long
x = 1
LastRow = sht.Cells(Cells.Rows.Count, "G").End(xlUp).Row
Set MyRange = sht.Range("G1:G" & LastRow)
For Each c In MyRange
If WorksheetFunction.CountIf(sht.Range("H1:H" & x), c.Value) = 0 Then
sht.Range("H" & x + 1) = c.Value
x = x + 1
End If
Next
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Andri" wrote:

> Dear Experts,
>
> Please help related to the above subject.
> From this discussion, got the following formula which is work well and
> faster for small of database.
> =OFFSET($G$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE(DATA)=I$1:I1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1)
> where $G$1, the first row of DATA.
>
> how to convert it to VBA as this time i have a huge database.
>
> for Tab Data, Advanced, Unique Record only (works well also, but i need the
> VBA).
>
> basically we would like to do similar like this:
> Data Sample
>
> Vendor Name
> A
> B
> F
> B
> C
> E
> E
> E
> A
>
> the Result of Unique Vendor will be
> A
> B
> F
> C
> E
>
> TIA
From: Andri on
Dear Mike,

thank you for your excellent VBA and Quotes.

the code has fulfil the requirement.

have a nice weekend to all of you.

respectfully,
andri

"Mike H" wrote:

> Andri,
>
> Try this code
>
> Sub stance()
> Dim MyRange As Range
> Set sht = Sheets("Sheet1") ' Change to suit
> Dim x As Long, LastRow As Long
> x = 1
> LastRow = sht.Cells(Cells.Rows.Count, "G").End(xlUp).Row
> Set MyRange = sht.Range("G1:G" & LastRow)
> For Each c In MyRange
> If WorksheetFunction.CountIf(sht.Range("H1:H" & x), c.Value) = 0 Then
> sht.Range("H" & x + 1) = c.Value
> x = x + 1
> End If
> Next
> End Sub
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "Andri" wrote:
>
> > Dear Experts,
> >
> > Please help related to the above subject.
> > From this discussion, got the following formula which is work well and
> > faster for small of database.
> > =OFFSET($G$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE(DATA)=I$1:I1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1)
> > where $G$1, the first row of DATA.
> >
> > how to convert it to VBA as this time i have a huge database.
> >
> > for Tab Data, Advanced, Unique Record only (works well also, but i need the
> > VBA).
> >
> > basically we would like to do similar like this:
> > Data Sample
> >
> > Vendor Name
> > A
> > B
> > F
> > B
> > C
> > E
> > E
> > E
> > A
> >
> > the Result of Unique Vendor will be
> > A
> > B
> > F
> > C
> > E
> >
> > TIA
From: Bernd P on
Hello Andri,

If speed is an issue I suggest to take my UDF Lfreq2:
http://sulprobil.com/html/lfreq.html

Regards,
Bernd