Select the UNIQUE vendors name

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
0
Utf
4/24/2010 12:04:01 AM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
703 Views

Similar Articles

[PageSpeed] 1

On Apr 24, 5:04=A0am, Andri <An...@discussions.microsoft.com> wrote:
> Dear All,
>
> Please help how to replace the below function with the VBA solution:
> =3DOFFSET($G$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE($G$1:$G$14)=3DI$1:I1),=
ROW(IN=ADDIRECT("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 wa=
it
> 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:=3Dxlfiltercopy,copytorange:=3Drange("SetYourRange"),unique:=3Dtrue
this will copy your unique items in "yourrange" cells to
"SetYourRange" cells

0
Javed
4/24/2010 7:33:55 AM
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@discussions.microsoft.com> wrote in message 
news:4CC27AAC-AA5A-475D-9579-C8BB08A97570@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
> 

0
helene
4/24/2010 2:28:28 PM
Reply:

Similar Artilces:

Name, Date and Address Formats
Hi, I have a number of different spreadsheets that I need to impirt into a single database. Unfortunately some of the information is in different formats. Would anyone be able to provide me with a solution that can turn: A B Brown ABC Brown *A B C*Brown* Brown, A B Brown, Anthony Bernard Brown Anthony Bernard Mr A B Bernard Into separate fields for title, forename, initials & surname On a similar theme I also need to do much the same thing with addresses: 1 Victoria Road, Victoria, Victoriashire, VC1 1VC etc And lastly (for now anyway) I also have some dates that I need to ...

CFileDialog selecting directorys only
I Have a CFileDialog and I just want to be able to select directorys no files. Can this be done? use SHBrowseForFolder http://msdn.microsoft.com/library/default.asp?url=/library/en-us/shellcc/platform/shell/reference/functions/shbrowseforfolder.asp HTH, "Chris Baker" <ChrisBaker@discussions.microsoft.com> wrote in message news:5185346E-6097-4B9C-89B1-D6588C87249A@microsoft.com... >I Have a CFileDialog and I just want to be able to select directorys no >files. > > Can this be done? > You also might take a look at www.codeproject.com/dialog/cfolderdialo...

Averaging Selected Records
Hi, I have a continuous form with lots of data on it. In the form footer I would like to average certain fields by checking a checkbox next to the fields (in the detail section) I would like included in the average. I am having a brain fart on this and just not getting it to average the selected records. Can anyone point me in the right direction? Thanks "Lythandra" <Lythandra@discussions.microsoft.com> wrote in message news:262B5AEB-2692-4561-824C-D6FC7465FC1D@microsoft.com... > Hi, > > I have a continuous form with lots of data on it. > > In the form ...

Selecting variable records from a form
I've created a checklist form and would like to create a report that demonstrates only the items in the form that have data. SH -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/200708/1 ...

How can I print only select sheets in my workbook?
My workbook has 12 sheets, but I want to print the second through ninth sheet exclusively, each time I hit print. (I do this once a week.) How do I do it? Excel 2007 Select sheet2 then hold SHIFT key and click on sheet9 File>Print>Active Sheet(s) Gord Dibben MS Excel MVP On Thu, 27 Mar 2008 19:14:00 -0700, MVictoreen <MVictoreen@discussions.microsoft.com> wrote: >My workbook has 12 sheets, but I want to print the second through ninth sheet >exclusively, each time I hit print. (I do this once a week.) How do I do it? >Excel 2007 Hi MVictoreen! As an optional m...

Selecting X cells based on answer to formula
Hi, I am trying to put together a spreadsheet that performs a couple of tasks. The first is basic resourcing which i am ok with, sort of how many heads and how many hours available means X cases will be done. The next thing I need to do is use the X cases result to identify from a list the oldest date and work through sequentially X dates in that column, which will not be consecutive, and tell me what the date of that case is. So if there were 10 cases done I would like one cell saying the oldest case worked on was - first date in column. And following doing the 10 cases I'd lik...

New contacts not showing when selecting the "To" button on a new e
Hello, If I add a new contact in Outlook 2003. Then I go to compose a new email. When I select the "To" buttom and either search or look for the new contact I created. It doesn't not show up. Any help would be appreciated. Thanks, Tony Two possibilities: 1. Your Contact doesn't have a resolved, valid electronic address 2. You did not add the Contact to the same folder you are displaying in the address book view. -- Russ Valentine "Tony414" <Tony414@discussions.microsoft.com> wrote in message news:AE3B3E10-8FCD-4840-8924-5B33D99A1F25@mi...

Consecutive unique numbers
Hi I use publisher to print some labels at work. It would be really useful if I could print each label with a unique and possibly sequential number. Is this possible using MS publisher, or even MS Word? Thanks Andy. Hi Andy, Yes, you can do this using Microsoft Word or Publisher. What you need to do is create a database of numbers, such as using Excel, and then using the Mail Merge features in Microsoft Word/Publisher, you can merge those numbers to each label. Visit the training section of my site to learn more about merging onto labels/business cards using a database. Brian K...

stored procedure for vendor master
I am looking at creating a stored procedure which when called using the below parameters will create a vendor in Great Plains .The values come from either the parameters below or from the EMPLOYEE class. I would like the stored procedure to populate the fields below. It would be very helpful if someone had an example of this kind of SP that I could start with. For example the SPROC should receive: Vendor ID (Required) Vendname (Required) VendClass(Required) AddressID Primary (Required) All address lines except country (Optional) All phones (Optional) Payment Terms (Optional) Ve...

Printing attachment names
When I print either a plain text or a rich text message that has an attachment, the name of the attachment (and an icon) are included in the output. But when I print an HTML messsage, there is no indication that there was an attachment. Do you know any way to force Outlook to include the attachment name(s) when printing an HTML message? Environment is Outlook XP, Windows 2000 Pro, Exchange 5.5 On Fri, 23 Jul 2004 14:53:42 -0700, "Tim Schaldach" <tschaldach@trigon-epc.com> wrote: >When I print either a plain text or a rich text message >that has an attachment, ...

Display table name as field in query
Does anyone know if there is a way to display the table name as a field in a query? I have a UNION query that merges all of the records from 10 different tables, but I need a field that indicates which table each record comes from. Any help would be greatly appreciated. Thanks, Craig Craig wrote: >Does anyone know if there is a way to display the table name as a field in a >query? > >I have a UNION query that merges all of the records from 10 different >tables, but I need a field that indicates which table each record comes from. Just use the table name in a calcula...

CFileDialog multiple file selection
I am using VC++ 6.0 I am trying to allow my users to select at most 50 files using CFileDialog When I over 18 files, the CFileDialog return IDCANCEL. I call CommDlgExtendedError() and it return #define FNERR_BUFFERTOOSMALL 0x300 I then check the first two bytes in lpstrFile and they are 24 and 1; which I take to mean 24*256 + 1 = 6145 The problem is, I have allocated a buffer of 20000 bytes. Can anyone tell me why this is failing I have included my code below Thanks Phi // Code Sampl TCHAR buf[20000] memset((void*)buf,0,20000) CFileDialog fldlg(TRUE,"All Files (*.*)|...

How do I set up a template to save time typing names
I have to send a lot of e-mails to the same people, and always more than one person. How do I set up a template so that I don't have to keep typing who it is to and who I am 'Cc' to? traceyella <traceyella@discussions.microsoft.com> wrote: > I have to send a lot of e-mails to the same people, and always more > than one person. How do I set up a template so that I don't have to > keep typing who it is to and who I am 'Cc' to? Create your message, but instead of sending it, save it as an OFT. WHen you want to send it, forward it from your Drafts fold...

Selecting Maximum Values in a Query with joins to other tables
I'm having difficulty with a query and hope someone can help me out. Basically, I'm looking to select the maximum amounts from a table based on a column in one of the tables named StateID. It works fine when I do this: SELECT TOP (100) PERCENT dbo.Bids.StateID, MAX(DISTINCT dbo.Bids.Amount) AS Amount, dbo.States.StateName FROM dbo.Bids INNER JOIN dbo.States ON dbo.Bids.StateID = dbo.States.StateID GROUP BY dbo.Bids.StateID, dbo.States.StateName ORDER BY dbo.States.StateName However, when I start to join fields from other tables in...

how to define range names
how do name cells so that I can see what the formula is calculating? Such as A1 * B1 = Final Price where A1 is price and B1 is discount so it reads in the final price cell as price * discount thanks in advance, A One quick way is to use the namebox (the droplist box just to the left of the "=" sign) In Sheet1, say: Select A1, then click inside the namebox, and type the name: price, then press ENTER Repeat to name cell B1 as: discount Then we could put in say C1: = price*discount The other (pe...

Display name points to the wrong user information
Hi, A client of mine is having problem with his user information in Outlook when sending out emails internally. Currently, they are running Exchange 2003 and Outlook 2003. This happened yesterday around 9am. When the user send an email to any internal staff, his user/contact information is stated as a different user. For example, my client's name is John Smith and the other account is TempStaff. 1. When he send out internal emails, other will see him as TempStaff in the From field. 2. When other users send him emails, other will see him as TempStaff in the To field. 3. On John Smith&...

Outlook 2003 does not export all fields to any of the selected file formats
Outlook 2003 does not export all fields, it is missing the date and time field for messages. If there is a way please let me know. For what purpose are you exporting your .pst items? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Great Eyes asked: | Outlook 2003 does not export all fields, it is missing the date and | time field for messages. | If there is a way please let me know. "...

Table Names
Does anyone know the table names for Ship To Addresses and for Sales Header data? I need to extract these two tables from SQL and do know what tables to use. Thank you. Regards, Diane All the information you requested can be found in the SOP10100 table. However, if you have individual ship to addresses by line item you will need to look at the SOP10200. You may want to check "Info Flow and Posting" from Accolade Publications (http://www.accoladepublications.com) which is a very simple to understand manual catored to the GP administrator and developer, written by Richard Wh...

clearing Automatic Naming in the 'To' field
In Outlook, when you click on the 'To' field and start typing the email address, a drop-down list appears to choose a name or email to complete the address (if the address has been used before) or the line will automatically complete if only one matching name has been used before. Sometimes email addresses change, so an option being presented on the line or in the drop-down list may no longer be valid. How do I clear the list of old names or email addresses that are no longer valid addresses. I know I can turn off automatic naming in the advanced e-mail options but I don...

Domain logon name case sensitive?
Are Domain logon names case sensitive? I created a new user but used lower case on our domain, when all other entires are upper case. Mistake apparently. The new user gets 'unauthorized' when opening crm. From the same machine, I can set the IE setting for force a logon with my credentials and CRM is fine. I want to change the domain login string on the CRM user record but the text is gray and you can't edit. Any suggestions? PROCEED WITH CAUTION........... Open SQL Enterprise Manager: Navigate to your CRM databases and expand the MSCRM database. Click on 'Tables&#...

Auto Population Of Names When Composing E-Mail
All - I have a wierd question...I have a user that is using Outlook 2003 and she sends a fair amount of email. I know that once you send a message to someone, Outlook "remembers" it so that if you send another message to them, all you have to do is start typing their name in the To box and it will show the name and email address. Her problem is that about once a month, she loses all of the names Outlook has "remembered". Anyone know why this would be happening and how to keep it from losing the remembered addresses? Thanks. Jeff If you head on over to the Sl...

Anyoone seen NDR generated where the Sender name is the failed recipient?
I have a really odd problem where someone will send an email via Outlook to another local user and will get an NDR returned from the Exchange 2003 server. Message tracking shows the message was delivered but the NDR lists the senders (ie their own) name and there is no time stamp as shown below ------------------------------ The following recipient(s) could not be reached: <Sender Name> on The recipient has been deleted or has no e-mail address. ------------------------------ It's happening to a number of individuals on infrequent occassions and I cannot find reference to this ...

Email 'From' field showing email address and not full name?
I've looked everywhere and can't find anything on this issue. I'm running Exchange 2003 on a Server 2000 box. When we email to outside servers (yahoo, gmail, etc) the from field shows the user's email address (eg: bob@microsoft.com) instead of their name (eg: Bob Jones). I've verified that the Display Name, Alias, and Simple Display Name fields in exchange all have the user's First and Last name. Any ideas, I'm stumped. Thanks, Chris Yes that's a hard one to find. In ESM go into Global Settings and click Internet Message Formats. Then in the right pane, rig...

Selective enabling of add ons
It would be nice if IE would allow me to enable add-ons for certain web pages. For example, I need flash installed to access my Bank. But I get annoyed by all the flash-based ads on other websites. I would like to enable flash for my Bank & youtube only. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and th...

Listbox selected freezes the form
I'm using the following line in the form_open to select the first item in the list which susequently selects data in the second listbox: Me.lstDate.Selected(0) = True However the whole form freezes and nothing works? Any ideas? Many thanks ShariS "ShariS" <u40676@uwe> wrote in message news:7e4b3831704cd@uwe... > I'm using the following line in the form_open to select the first item in > the > list which susequently selects data in the second listbox: > > Me.lstDate.Selected(0) = True > > However the whole form freezes and nothing works? Any i...