I have 2 excel sheets. One contains userid's and related fields, the other contains userid's and home-drive storage usage. They need to be combined in one sheet.

The obvious way is to loop through one and search in the other for the matching userid, then supplement either one and move on to the next. This will take ages tho and looks both ugly and inefficient.

Is there someone here who could point me in the right direction to go about it more efficient ?

I would be happy to post relevant code if it helps... but it's not that my code is broke, it just takes ages to loop through 90000 records till the record I need is found.

I'm aware that this should be done in a database, but for some reason managers prefer excel sheets where I work.

I was thinking about importing to an access database, do the combining there and then convert back to excel, but I rather take the opportunity to learn about sorting insane amounts of data :)


Thanks in advance.

Planet

If you are loping thru the cells to find a match it will take time. On the other hand, try to use the find function.

like

Set rFnd = Workbook.Sheets(1).Range("A1:A60000").Find(what:=s USerID, Lookat:=xlPart)

This will get you the range if the text is found or will be set to nothing

Hence you can check

IF Not rFnd Is Nothing
   CElls(rFnd.row,2).value = sSomeValue
Else
  ' Text Not Found
End if

Cheers:)
Shasur

http://www.vbadud.blogspot.com

Thanks for the info. That does the trick for me.

Is there any search pattern associated with the Find function so that I could try and learn a bit about effective searching ?

Regards,

Jeroen