Prev: Formulas not updating
Next: Reference: forum discussion dated 17-18April2010
From: Andri on 24 Apr 2010 03:04
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 24 Apr 2010 04:04
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 24 Apr 2010 05:21
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 24 Apr 2010 16:06
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 25 Apr 2010 07:24
Hello Andri,

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

Regards,
Bernd
 |  Next  |  Last
Pages: 1 2
Prev: Formulas not updating
Next: Reference: forum discussion dated 17-18April2010