100k lines - Copying Name LookUp Value from Sheet 1 to Sheet 2 Efficiently

[AnteChristo33]

Greetings,

Need some help with an automation script - possibly a loop with variant arrays? Couldn't put it in practice, but it seems to be the consensus for faster data parsing.

I've got a convoluted 25 sheet workbook that we use to determine the price for certain products. The Product Names are "modular" and the pricing is determined by the "modules" in the name, eg. (ABC-DE-FGH) Lots of complicated formulas in there and no one has the full price list. I did find the Product List, however.

We're digging into the Lookup Sheet and the Product List Sheet.

My goal is to take that list of Product Names, input them (one by one?) in the lookup field, copy the calculated price onto the Product List Sheet and go down the list like so.
When the Product Name is input in the Lookup Field, the price has to be calculated via a ready-made formula.

The Hitch : There's over 100k Product Names and this attempt at a code takes way too long.

Sub Copy_Price()

Dim i As Long

For i = 2 To Range("A1").End(xlDown).Row
   
    Worksheets("Lookup Sheet").Range("A2").Value = Worksheets("Product List").Range("A" & i)
    Worksheets("Lookup Sheet").Range("B2").Copy
    Worksheets("Product List").Range("C" & i).PasteSpecial Paste:=xlPasteValues

Next i

End Sub

I've illustrated how the problem looks with a Test File and these images.

I do hope it helps in formulating a viable plan! I'm eager to learn the inner workings of a potential solution.

Thanks

 

[igold]

Hi AnteChristo33, welcome to the MrExcel board. With 100k rows you are on the right track. You would want to get all your data into memory, manipulate the data in memory and then write the results back to the sheet in one shot (all done with arrays). The most inefficient thing you can do is to have your code reading and writing to the sheet in the middle of the loop. That said, and perhaps it is just me, is that with the four pieces of information that you provided (Post title, your description, your posted code, and your pictures), I can't seem to tie it all together. Maybe you could add some more info or better describe your data and what you are doing with it.

 

[rlv01]

If I understand your example code, the sequence of events is this:

1. You transfer a value from your 'product list' sheet (PR) to cell A2 in your 'lookup sheet' (LS).​

2. Something magical happens to transform the value in LS cell A2 to a new value in LS cell B2​

3. The new LS cell B2 value is copied back to the PR sheet, col C.​

4. A new row in PS is selected and the process repeats.​

As @igold says, the best way to speed this up would be to load everything into memory, but having to feed everything through step 2 will sabotage that effort unless you can determine a way to move whatever is happening there into VBA.

An intermediate approach would be to put whatever formula you have in LS cell B2 into PS col C in order to make iteration unnecessary.

 

[johnnyL]

I am assuming the formula in B2 is a lookup formula. What is the formula that is in B2?

As @rlv01 suggested, why not just put that formula down the C column of "Product List' sheet? That would eliminate the need to do all of what you are trying to do in the code you posted.

 

[AnteChristo33]

Well, it is as you both say. If I could somehow understand how the formula works, it'd be ideal to just implement that into the script or better again, just copy it in the adjacent cell and modify the reference so I can just drag it down.

At the moment, those attempts are fruitless since the lookup system is a complex web of interdependant lookups. Multiple Hlookups and Vlookups working together across a bunch of sheet. I'll just keep at it, but I do thank you for your insights! If I manage to figure the file out properly, I'll do just that. I'll be back once it's done!

I don't know who designed that file, (it was well before my time with the company) but they sure loved headaches.

 

[johnnyL]

Can you post the formula that is in B2 as I requested?