Hi all!
I have a document with a row that contains names of some custumers. They are
writen down many times so there are multiple entries. I need another document
to check the first row of that document and display the name of the first
customer folowed by the second etc... but I don't want to display the same
customer twice. Is this possible? Do I need VBA code for this?

Thanks in advance.

Look at Chip Pearson's page on Uniques and Duplicates

http://www.cpearson.com/excel/duplicat.htm

--
Regards,
Tom Ogilvy


"Calle" <(E-Mail Removed)> wrote in message
news:5F4C7BE7-5522-4F97-9C83-(E-Mail Removed)...
> Hi all!
> I have a document with a row that contains names of some custumers. They
> are
> writen down many times so there are multiple entries. I need another
> document
> to check the first row of that document and display the name of the first
> customer folowed by the second etc... but I don't want to display the same
> customer twice. Is this possible? Do I need VBA code for this?
>
> Thanks in advance.

Thanks Tom! This helps. However do you have a code for looking up columns in
other documents/workbooks...

"Tom Ogilvy" wrote:

> Look at Chip Pearson's page on Uniques and Duplicates
>
> http://www.cpearson.com/excel/duplicat.htm
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Calle" <(E-Mail Removed)> wrote in message
> news:5F4C7BE7-5522-4F97-9C83-(E-Mail Removed)...
> > Hi all!
> > I have a document with a row that contains names of some custumers. They
> > are
> > writen down many times so there are multiple entries. I need another
> > document
> > to check the first row of that document and display the name of the first
> > customer folowed by the second etc... but I don't want to display the same
> > customer twice. Is this possible? Do I need VBA code for this?
> >
> > Thanks in advance.
>
>
>

Sub EFG()
Dim noDupes as Collection
Dim item, swap1, swap2, cell as Range, rng as Range
Dim i as Long, j as Long
Dim col as long
set noDupes = New Collection
' duplicate names in row1 of Sheet1 of workbook named Otherbook.xls
with workbooks("otherbook.xls").Worksheets("sheet1")
set rng = .Range(.Range("A1"),.Range("A1").End(xltoRight))
End with

for each cell in rng
On Error Resume next
nodupes.add cell.Value, cell.Text
On Error goto 0
Next

' sort the unique names
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) > NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, before:=j
NoDupes.Add Swap2, before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

' write them out to row 1 of activesheet

Col = 1
For Each Item In NoDupes
Activesheet.Cells(1,col) = Item
col = col + 1
Next Item

End Sub

Much of the code taken from John Walkenbach's site:
http://j-walk.com/ss/excel/tips/tip47.htm

Code is untested and may contain typos.

--
regards,
Tom Ogilvy




"Calle" <(E-Mail Removed)> wrote in message
news:633E75BC-1D7B-4C66-8DD2-(E-Mail Removed)...
> Thanks Tom! This helps. However do you have a code for looking up columns
> in
> other documents/workbooks...
>
> "Tom Ogilvy" wrote:
>
>> Look at Chip Pearson's page on Uniques and Duplicates
>>
>> http://www.cpearson.com/excel/duplicat.htm
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "Calle" <(E-Mail Removed)> wrote in message
>> news:5F4C7BE7-5522-4F97-9C83-(E-Mail Removed)...
>> > Hi all!
>> > I have a document with a row that contains names of some custumers.
>> > They
>> > are
>> > writen down many times so there are multiple entries. I need another
>> > document
>> > to check the first row of that document and display the name of the
>> > first
>> > customer folowed by the second etc... but I don't want to display the
>> > same
>> > customer twice. Is this possible? Do I need VBA code for this?
>> >
>> > Thanks in advance.
>>
>>
>>

Hi Tom!
That code works if I have the list in the same workbook but when I have it
in a differesnt document I get an error message (" The index is out of the
interval") well it's in swedish but I think it's translated something like
that....

with workbooks("otherbook.xls").Worksheets("sheet1")
set rng = .Range(.Range("A1"),.Range("A1").End(xltoRight))
End with

specifies it uses the otherbook.xls sheet1 row 1 to gather the information
(a workbook other than the activeworkbook). The error (subscript out of
Range) would mean you are not giving the correct name for the workbook or it
isn't open or you are not naming the correct worksheet. The workbook must
be open and you can't put in a string like "C:\MyFolder\Otherbook.xls" as
an additional caution.

--
Regards,
Tom Ogilvy


"Calle" <(E-Mail Removed)> wrote in message
news:02ACC728-6701-4FB2-9719-(E-Mail Removed)...
> Hi Tom!
> That code works if I have the list in the same workbook but when I have it
> in a differesnt document I get an error message (" The index is out of the
> interval") well it's in swedish but I think it's translated something like
> that....

hi again!
Ok, it works with open workbook. Any chance to get it to work with closed
workbook?

You could have the macro open the workbook.

If you mean without opening the workbook in Excel, it would be much slower.

You can look at this page
http://j-walk.com/ss/excel/tips/tip82.htm
of course you would have to know what cells contained the information you
wanted.

Another way would be
http://www.erlandsendata.no/english/...php?t=envbadac

but generally that is more suited to data in columns set up like a database.

The fastest way would be to use/add a dummy sheet and put linking formulas
in that sheet to extract the data. then use my code against that dummy sheet
and delete it when done.

--
Regards,
Tom Ogilvy

"Calle" wrote:

> hi again!
> Ok, it works with open workbook. Any chance to get it to work with closed
> workbook?