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:8BDA0D148FE34B52AD4A3DAF07D19ED2(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 1718April2010 