A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

List the UNIQUE certain fields from the database



 
 
Thread Tools Display Modes
  #1  
Old April 24th, 2010, 08:04 AM posted to microsoft.public.excel.worksheet.functions
Andri
external usenet poster
 
Posts: 66
Default List the UNIQUE certain fields from the database

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
  #2  
Old April 24th, 2010, 09:04 AM posted to microsoft.public.excel.worksheet.functions
Per Jessen
external usenet poster
 
Posts: 686
Default List the UNIQUE certain fields from the database

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" skrev i meddelelsen
...
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


  #3  
Old April 24th, 2010, 10:21 AM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 8,419
Default List the UNIQUE certain fields from the database

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

  #4  
Old April 24th, 2010, 09:06 PM posted to microsoft.public.excel.worksheet.functions
Andri
external usenet poster
 
Posts: 66
Default List the UNIQUE certain fields from the database

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

  #5  
Old April 25th, 2010, 12:24 PM posted to microsoft.public.excel.worksheet.functions
Bernd P
external usenet poster
 
Posts: 613
Default List the UNIQUE certain fields from the database

Hello Andri,

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

Regards,
Bernd
  #6  
Old April 25th, 2010, 05:12 PM posted to microsoft.public.excel.worksheet.functions
Dana DeLouis[_3_]
external usenet poster
 
Posts: 184
Default List the UNIQUE certain fields from the database

Hi. Just some more options.
There are many variations, even along the following general ideas.
Assuming A1 is a database heading, this gets data starting in A2, and
places unique data in C2.


Sub YourMainCode()
Dim Unique
Unique = Union(GetColumn([A2]).Value)
[C2].Resize(UBound(Unique)) = T1(Unique)
End Sub

'// Library Stuff:

Function Union(v)
'// UnSorted Union
Dim D, Obj
Const Dummy As Long = 1

Set D = CreateObject("Scripting.Dictionary")

On Error Resume Next
For Each Obj In v
D.Add Obj, Dummy
Next Obj

'Note: Moving Keys out makes Index 0-based
'I prefer 1-base. Adjust other code if not used
Union = T2(D.keys)
End Function

Function GetColumn(Top) As Range
Set GetColumn = Range(Top, Cells(Rows.Count, Top.Column).End(xlUp))
End Function

Function T1(m)
'Transpose Once
T1 = WorksheetFunction.Transpose(m)
End Function

Function T2(m)
'Transpose twice
With WorksheetFunction
T2 = .Transpose(.Transpose(m))
End With
End Function


= = = = = = =
HTH :)
Dana DeLouis

On 4/24/2010 3:04 AM, 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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:40 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.