Slow Macro Assistance

[mikekolba]

Created this Macro to compare a part number in one workbook spreadsheet of 135k rows to another column of part numbers located in another workbook spreadsheet that is 600k rows.

When a match is found, then 2 cells of data from the matched rows in the 2nd workbook sheet are copied to 2 cells in the current row of the original first workbook sheet. Then it continues on until it completes this processing of the 135k rows.

It looks like it works, but it's pretty slow and I need some help optimizing it so it can do the same thing but way faster. I had to insert a DoEvents in this original code to keep excel from hanging and not responding but I need a quicker way of processing all of this data...

I could use some help with this.

Please Login or Register  to view this content.

[royUK]

Why loop?

You could use VLOOKUP without VBA

[mikekolba]

Would I be able to use a vlookup in this scenario? The lookup/comparison locations are all in column B of each spreadsheet and the values I would need to pull and copy would be in columns to the right of that B column used to look up and compare? Call me ignorant but haven't done a vlookup before.. Would I be able to paste certain data in certain columns from one sheet to other columns in the other sheet?

[Paul]

Can't use VLOOKUP due to column locations? Please explain.

While VLOOKUP can only search one column and return a value in a column to the right, INDEX/MATCH can do both left and right returns.

Return value in column B on Sheet2 after finding the value in A1 on this sheet within column G on Sheet2:

=INDEX(Sheet2!B:B,MATCH(A1,Sheet2!G:G,0))

Does that help?

[mikekolba]

That helps Paul. In this instance the data I need to return is to the right of the column. when i look up and compare the values in the B columns of each workbook. Would a vlookup be quicker or would index/match be quicker? Also which would be easier to move the data- basically in the 600k row sheet I'm looking to match the values in the B columns in both workbooks and then copy the value in column D and column M to Column F and Column E respectively of the other workbook with 135k rows.

One of the issues I also had was when running the macro, Excel would hang up and go not responding- would I have a similar issue in this case?

[Paul]

In most cases, if you're searching left-to-right, a VLOOKUP is going to be faster than INDEX/MATCH simply because it's only one function, and it's a native function coded into Excel.

If you need to search right-to-left, INDEX/MATCH is really your only good option other than writing code.

Essentially you're going to have either 270,000 formulas, or loop through 270,000 cells to input values using a macro. If your sheet recalcs occasionally/often, those formulas are going to be brutal.

[mikekolba]

It would be left to right with the comparison/lookups being in column B of both workbooks and the searching/copying of data from cells to the right of that column. Could you give me an example of how to do this for say taking the value of a cell in column b in workbook 1 sheet 1, looking for a match in column b of workbook 2 sheet 2 and then when it finds a match, copying the value of the cell in column M in workbook 2 sheet 2 to column E of workbook 1 sheet 1?

That would be great to get me started- then I could add the additional data for the other columns and data I need to move around?

[watersev]

hi, mikekolba, can you post sample workbook showing original data and result you need to obtain? I will try to help with the code.

[mikekolba]

ok- i have the 2 workbooks attached with a few rows of some sample data- not sure if there are any matches in them but you should get the idea.

Also the text in the 2 columns of data in book 2 that I am copying to book 1 is actually HTML formatted data (wrote a seperate macro to convert that raw data to HTML formatted which was a bear using all the ascii codes in VB for it)

Thanks for the assistance on this. The Real Book 2 Data is 600k rows and the real book one data is 135k rows so it's a lot of data to process.

Also the Book 2 data will increase to a larger number of rows in the future so I need a good quick way to process this data

[Paul]

A VLOOKUP formula across workbooks would look like the following. You would place this in E2.

=VLOOKUP(B2,[Workbook2.xlsx]Sheet2!$B:$M,12,0)

On a closed workbook, you would enter the full path. (Excel will automatically add the path if you have the second workbook open and then subsequently close it.)

[mikekolba]

Would it be better to add that formula to all 135k rows, or run it as a macro to perform it? Also, If there is no matched data for a row, I'd like those cells left blank then? I'm guessing I would need to do this as a macro so the cells don't get filled in with formulas?

[watersev]

please check attachment, run code "test" in Book1_1.xlsm

Notes:

1. Workbook names are hardcoded as well as sheet names
2. Both workbooks must be opened or the code quits
3. Values in column B of the first file are exactly the same with column B values in second file if they exist
4. There were no matches in workbook 2 sample, I've added one for testing purpose
5. All those "fancy" features like progress bar etc. can be added by yourself

[mikekolba]

Would a macro look something like this?

Please Login or Register  to view this content.

[mikekolba]

Thanks Water- I'm checking it out now.

[mikekolba]

Water- Looks great- was incredibly quick in the processing too. I just tested on the normal sheets and it did the substitutions fine.

I'm going to take that code and tweak it and use it for the other column of data now

Much appreciated to both you and Paul!

[mikekolba]

Whoops- correction- for some reason it's not doing the matches correctly on the larger set of data I just ran.

Here's an example of a match it made that isn't correct. The part numbers LL-C3750X-24-S-E= and 20037 aren't a match

Please Login or Register  to view this content.

[watersev]

can I see the sample of this data in the workbook and code used? I suppose that's the 'tweaker's" fault

If I add those data in respective sample books with my code provided there is no match between them

[mikekolba]

Working on uploading copies. I had to remove some of the data to make them small enough to upload. Also had to remove the pricing information from 2 of the columns.


Hmm- having problems uploading the zip files- gives me an error message about a security token....

[mikekolba]

Same error- says something about a security token being missing when I try to upload a zipped attachement....

Anyone know the attachment size limits?

[watersev]

the limit is 1 MB

You do not need to load the whole data just a piece (50-100 rows) with the problem rows. Plus I need to see the code used.

[snb]

alternative

Please Login or Register  to view this content.