code only finding one match and then stopping

Hi, fordraiders.

Some clarification, please...
(1) Overall, if a value isn't found in "Sheet3", then you look for it in "Sheet4". If it's not found there, then that row's columns B to T are blanked. Correct?
(2) If a value occurs more than once, then each occurrence needs to match to a separate row in one of the other sheets. Correct?
(3) If a value occurs twice on "Main" and once on each of the other two sheets, is it OK for the first entry to match to "Sheet3" and the second to match to "Sheet4"?
(4) Are the values in column A of "Main" sorted together?
(5) Roughly how many entries are there in each of the three sheets?

Edit: Please see attached. I've assumed answers to the above questions ("Yes", "Yes", "Yes", "Yes", "Not a lot") so please be careful. (BTW, the code is a bit of a dog's dinner, but we'll worry about that once it's doing what you want.)

Thanks,
Brian.Find-Duplicates-II.xlsm

(1) Overall, if a value isn't found in "Sheet3", then you look for it in "Sheet4". If it's not found there, then that row's columns B to T are blanked. Correct?
Yes
(2) If a value occurs more than once, then each occurrence needs to match to a separate row in one of the other sheets. Correct?
yes
(3) If a value occurs twice on "Main" and once on each of the other two sheets, is it OK for the first entry to match to "Sheet3" and the second to match to "Sheet4"?
anytime  there is a match in sheet3 ,4,5,6,7,8
The row is brought back.

4) Are the values in column A of "Main" sorted together?
not all the time
(5) Roughly how many entries are there in each of the three sheets?

800,000

per your example:

if column a on main asked for 4.
everytime it asked for 4 it would have returned all 4 occurences

if column a on main asked for 2.
everytime it asked for 2 it would have returned all 3 occurences

Thanks, fordraiders.

(1) Do I understand correctly - there are five sheets to match, not two? How big is the file? What versions of Excel and Windows do your users have? And PC specification?

(2) Please update the attached to reflect the required outcome.

(3) Are the "match" sheets sorted?

Thanks,
Brian.Find-Duplicates-III.xlsx

(1) Do I understand correctly - there are five sheets to match, not two?
yes
How big is the file?
3mb

What versions of Excel and Windows do your users have? And PC specification?

xp/7

lenovo 420t 8gb ram t5 3ghz





(2) Please update the attached to reflect the required outcome.

(3) Are the "match" sheets sorted?  no

Thanks, fordraiders.

For future reference, it almost always saves time and effort if you provide a full description of your requirements in the question - the logic my code uses is marginal for two sheets, unusable for five.

Please post the updated file.

Thanks,
Brian.

brian, the "find-duplicatesIII"...did not have any code in it.

for right now...if its looking at sheets 3 and 4  thats fine...

fordraiders,

It wasn't intended to have code (hence the xlsx) - I requested that you change it to reflect your updated requirements. Doing that would make it clear exactly what you want.

for right now
Completing the wrong way and then having to rewrite the code simply wastes everyone's time.

Brian.

ok here...
Find-Duplicates-III.xlsx

Thanks, fordraiders,, but what I wanted was for you to complete my example! So, just update column B in the "Match" sheet of my file to reflect the appropriate values from the other two sheets.

Thanks,
Brian.

ok.here
Find-Duplicates-III.xlsx

another example...finding values from both sheets
Find-Duplicates-III.xlsx

Thanks, fordraiders, that really makes things clear.Couple more questions, please.

(1) May we sort the match sheets? The Main sheet?

(2) You mentioned that there would be roughly 800,000 rows per sheet. Approximately what percentage of the match sheets' entries will be matched to by Main?

(3) 3MB seems like a vanishingly small file size for 4,000,000 rows worth of data. Any chance there's a misprint?

Thanks,
Brian.

1) no

2) 13%

3) i hope to not reach that point and someone else will develop the app further...

maybe 1.2 mil records at the most.

fordraiders,

I'm not sure why, but I've found it extremely difficult to get the necessary information. For example, the amount of data strongly influences the feasibility of a number of potential solutions. However, I still have no idea of how many rows you have now, nor how rapidly your data will grow until it reaches Excel's max no. of rows (although Excel is likely to have ground to a halt a long time before that).

Possibly the issue is that you are happy with an iterative development process, continually adding to the requirements - perhaps "until someone else will develop the app further"?

In any event, if you're willing to have one last go at this, please help me to understand your data...
 - How how many rows does each sheet have now?
 - How often are new rows added to each sheet?
 - How many new rows are added to each sheet?
 - Other than by this macro, is existing data ever changed or deleted?
 - When new rows are added, are they added to the bottom of each sheet or may they be inserted into the existing data? If added to the bottom, is the data ever sorted?
 - Other than in column A, does Main's data come from anywhere else other than by the macro?
 - If rows may be deleted from the "Match" sheets, then presumably any equivalent "Main" rows should be deleted? Except that at least one row should be left for each block of an SKU?
 - Anything else I need to understand?

Thanks,
Brian.

You are asking me questions that are unknown at this point.

but i will venture at the best answer.
How how many rows does each sheet have now?
right now I only have 2,000 on sheet3 and nothing on sheet4

 - How often are new rows added to each sheet?
I'm, aniticipating(as i'm told )  possible 600,000 on each page.
After that point i do not plan to support anymore.


 - How many new rows are added to each sheet?
I'm, aniticipating(as i'm told )  possible 600,000 on each page.
possibly 200,000 here shortly...another 400,000 in a month ot 2 .
I do not have a definitive amswer.


 - Other than by this macro, is existing data ever changed or deleted?
No.


 - When new rows are added, are they added to the bottom of each sheet.
Yes.

 or may they be inserted into the existing data?
no


If added to the bottom, is the data ever sorted?
No.

 - Other than in column A, does Main's data come from anywhere else other than by the macro?
Yes.
Random numbers are put into column A. So sometimes there will not be a match at all.

 - If rows may be deleted from the "Match" sheets,
This is simply a sheet where numbers are posted.
These numbers are changed and deleted everyday.
then presumably any
equivalent "Main" rows should be deleted?

The Reset button  deletes any data on the "Main" sheet.
anyone can post new numbers then in column A and do another lookup.

Except that at least one row should be left for each block of an SKU?
nothing is left after reseting data in ColumnA
 - Anything else I need to understand?

I place numbers in ColumnA on "Main" page.
hit the buttopn to do a search on sheets 3 and 4
Return the results to "Main" sheet to the right of the requested numbers in ColumnA.

Hope this helps.

Thanks, fordraiders, that makes things much clearer.

A few follow-ups, please...
 - Have you considered using Access to hold the Match data? It's much better than Excel for holding (and accessing) large volumes of data.  
 - Are new Match entries added to a single sheet until that's full and so further additions are made to the next sheet until that's full and so on?
 - If so, rather than hard-coding the Match sheets, the macro could simply treat all sheets other than Main as Match sheets. Would that be appropriate?
 - It sounds as if users are frequently matching very small numbers of entries, rather than occasionally looking for loads of them. Is this correct?
 - How many times a day will a user run a match? Reset Main?
 - How many entries in Main when the reset is done?

Finally, I was thinking of speeding things up by ignoring entries up to and including the first matched entry (i.e. whose column B is not blank). However, I don't think that this would be safe as a user may change an earlier number. Therefore, each Main non-blank column A will need to be checked and each existing blank column A row will be deleted.

Thanks,
Brian.

A few follow-ups, please...
 - Have you considered using Access to hold the Match data? It's much better than Excel for holding (and accessing) large volumes of data.
company wants only excel.


 - Are new Match entries added to a single sheet until that's full and so further additions are made to the next sheet until that's full and so on?
Yes, but for speed purposes.I'm stopping ay 600,000
per sheet.

 - If so, rather than hard-coding the Match sheets, the macro could simply treat all sheets other than Main as Match sheets. Would that be appropriate?


 - It sounds as if users are frequently matching very small numbers of entries, rather than occasionally looking for loads of them. Is this correct?
Yes, maybe 100-200 at each request.


 - How many times a day will a user run a match? Reset Main?
2-3 times

 - How many entries in Main when the reset is done?
zero...it just wipes out any entries and we submit another request.
Clears the sheet for new entries in Column A.


Finally, I was thinking of speeding things up by ignoring entries up to and including the first matched entry (i.e. whose column B is not blank). However, I don't think that this would be safe as a user may change an earlier number. Therefore, each Main non-blank column A will need to be checked and each existing blank column A row will be deleted.

ok..

in the post:
by: fordraidersPosted on 2013-09-09 at 13:19:32ID: 39477375

I had attached my current code.
I need it to do the same thing..but bring back any other entries from sheet 3 and 4
that may have the same request number in ColumnA on the main page.

Thanks for the additional checking.

Thanks, fordraiders.

There were a few points not addressed...
 - May I treat all non-Main sheets in the file as Match sheets?
- Just to confirm - each user would run matches two or three times a day and do a single reset?
 - "I had attached my current code.". Nope, your files were all xlsx's - no code.
 - Try to create a test file with data similar to your live data and filling 3,000,000 rows and 20 columns. The rows should not be identical (Excel's pretty good at saving duplication). How many sheets do you get before Excel freezes?

I have enough now to change my original macro, but let me emphasize that I'm going to have to code this around the huge size that the file will build to. Normally, I would load the match sheets' column A's and search that, however as your full data alone will almost certainly crash Excel, this is simply not on. So, the lookup will likely be slow and get even slower as the match data builds up. Eventually it will be a race to see which will fail first - the match or the addition of more match data.

Do the company understand the cost of using Excel for Phase 1?

Cheers,
Brian.

ok here is the file.
just a sample file.
Matchv-Tool.xlsm

Thanks, fordraiders. Other the fact that it has expired and, more significantly, that it's already using Access, the main part of this is simply the code in your original question.

And the rest of my queries?

Cheers,
Brian.

fordraiders,

Please see my previous post.

I've just noticed the "Instructions". I was a little concerned that you're apparently expecting the company's customers to download a multi-million row spreadsheet every month. However, even worse is that it'll be loaded on PC's many of which are dramatically lower spec'ed than the one you mentioned.  (Not to mention potential problems with Excel versions prior to 2007.)

This is going to seriously frustrate a lot of people. Instead you could use a pure Access solution - the Access runtime is free and means that the users wouldn't need to have Access installed.

Edit: As a partial test, I took the first 1,000 row in one of the sheets and copied them to create three million rows. The resulting file was 325MB, took two and a half minutes to open, used 700MB of memory and caused timeout errors when loading (unless Excel was already running)

Regards,
Brian.

ok, Brian..
That access code is flow over from another project. Nothing to do with this one.

I appreciate the help on this. I think its becoming much more complicated than anticipated.

I cant go to access.
i will not have more than 1.2 millions between sheets 3 and 4.

I just need the necessary data to populate on the "Main" sheet tab from sheets 3 and 4.

I will get it completed on my own.

If i can help with just rewarding points for commenting let me know.

fordraiders,

If you no longer require assistance then of course that's your choice. Points may only be given for solutions (or direct contributions to them). If you post your solution here then that should be selected as the answer and you can then decide whether it's appropriate to select one or more of my posts as "assists". Otherwise, you must delete the question, but hopefully that won't be necessary.

I think its becoming much more complicated than anticipated.
Yes, even more than the missing requirements, the size issue has completely distorted what should have been a straightforward task. So far, we've gone from 2.4 million rows, to 4.8, to 3 and now to 1.2 (file size 133MB).

Cheers,
Brian.

ok Thanks , When i get something I'l post it...

brian, Just an fyi, here is a previous post for this question...

https://www.experts-exchange.com/questions/27636139/looking-for-data-in-other-worksheets-in-same-workbook.html

I would suspect, adding

Set rFind = wks2.Range("A:A").Cells.FindNext(rFind)
ActiveCell.Offset(1).EntireRow.Insert
                                r.Offset(, 1).Resize(1, 19).Value = rFind.Offset(, 1).Resize(1, 19).Value 'copy B:W values

then when it is completed NOT finding anymore of the same values.
it proceeds to the next cell

fordraiders,

But does not fully undestand what I'm looking for in my opinion.
As you keep changing your description of the situation and it's requirements, I completely agree that I (at least) don't fully understand your situation!

If you've changed your mind about completing this yourself, would you like me to do it?

Thanks,
Brian.

If you've changed your mind about completing this yourself, would you like me to do it?
Yes,

This is what i ahve tried up to now:

But this throws me into a endless loop.

For Each r In rng
        Set rFind = wks2.Range("A:A").Find(What:=r.Value, LookIn:=xlValues, LookAt:=xlWhole, searchorder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        If Not rFind Is Nothing Then
             bFound = True
                r.Offset(, 1).Resize(1, 19).Value = rFind.Offset(, 1).Resize(1, 19).Value 'copy B:W values
                
                Do Until rFind Is Nothing  '   <----------------- added this
                        If Not rFind Is Nothing Then
                        Set rFind = wks2.Range("A:A").Cells.FindNext(rFind)
                           If rFind <> "" Then
                             'Insert row below active cell
                                  ActiveCell.Offset(1).EntireRow.Insert
                                    r.Offset(, 1).Resize(1, 19).Value = rFind.Offset(, 1).Resize(1, 19).Value 'copy B:W values
                            Else
                                 Exit Do
                            End If
                        
                        End If
                
                Loop
       
       
       Else
            Set rFind = wks3.Range("A:A").Find(What:=r.Value, LookIn:=xlValues, LookAt:=xlWhole, searchorder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
            If Not rFind Is Nothing Then
                bFound = True
                r.Offset(, 1).Resize(1, 19).Value = rFind.Offset(, 1).Resize(1, 19).Value 'copy B:W values
            
             
            
            
            Else
                r.Offset(, 1).Resize(1, 19).Value = vbNullString
            End If
        End If
    
    Next r

Select allOpen in new window

Brian,
Perfect...Thanks.!!
Thanks very very much.
Sorry to be a pain. Alot for 500 points.
If i could give more i would.

Perfect Thanks very much !

Thanks, fordraiders. It would be good if you could let us know how it performs when fully loaded.