check row in other document

[Guest]

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.

 

[Tom Ogilvy]

Look at Chip Pearson's page on Uniques and Duplicates

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

 

[Guest]

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

 

[Tom Ogilvy]

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.

 

[Guest]

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....

 

[Tom Ogilvy]

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.

 

[Guest]

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

 

[Guest]

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/index.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.