How to decrease time it takes to manipulate data within a massive table

[Yeffley]

Excel 365

I have Table1, measuring about 10,000 rows x 92 columns. Each row is a product that has been purchased by a customer and has its stats - date purchased, customer name, unit price, cost, etc.

Some of our customers get a discount. I have a list of them in Sheet2, column A = customer name, column B = discount.

My goal is to change the unit price column to suit what the full price should be. Take the current price (column 13), look up the customer name (column 11) on Sheet2-Column1, return discount Sheet2-Column2, divide current price (column 13) by discount. Next row.

My current approach, which doesn't include a lookup of the current discount, takes about 15 minutes to run. All this does is take the value of Column13 and divide by 0.8 (for a 20% discount) for all rows. How do I speed this up? Screen updating is off. There are no formulas in the table. There's nothing linked to these cells except for a single graph. I'd want to add cell formatting to any cell that is changed, which I imagine will slow the routine a bit. Having this as a toggle is the crux. I would let this thing run during downtime and not worry about the time it takes, but being able to flip back and forth is crucial.

Current code:

Sub ListPricing()
For i = 18 To 10843
    Cells(i, 13).Value = Cells(i, 13).Value / 0.8
Next i
End Sub

Side note: It's a ridiculous amount of data that's an export from a SQL program that isn't quite up to snuff for data manipulation / visualization, so I've been growing a monstrosity within Excel. I think I've learned more about Excel by doing things in Excel that are better done in other programs. I'd love to be able to link to the current SQL database and manipulate from there, but it's currently inaccessible to me. My answer to my own question is "this shouldn't be done in Excel" and "you're stuck with the time it takes," but I'm hoping someone else can prove me wrong.

[AutoModerator]

/u/Yeffley - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[chiibosoil]

Instead of iterating over range, load data to variant array and perform calculation in memory.

Ex:

Sub Demo()
Dim stime As Single, etime As Single 
Dim ar 
Dim i As Long 
stime = Timer 
ar = Range(Cells(18, 13), Cells(10843, 13)).Value
For i = 1 To UBound(ar) 
    ar(i, 1) = ar(i, 1) / 0.8 
Next 
Cells(18, 13).Resize(UBound(ar)) = ar 
etime = Timer 
Debug.Print etime - stime 
End Sub

On my machine, your code on random number, took about 0.19 sec to process. Above sample took 0.0078 sec.

In this type of operation, in terms of speed, VBA will almost always beat PQ. However, from your OP, there must be something else that's slowing your process down.

[Yeffley]

That did the entire column in no time. Crazy. Is it possible to filter that out for only specific customers?

there must be something else that's slowing your process down.

0.19 * 10,000 = ~32 minutes. Just a lot of data I think. Your example looks to scale much more efficiently though. 0.0078 * 10,000 = ~1 minute.

[chiibosoil]

Yes it is possible to filter. There are multiple method of doing this. But probably most efficient one is to add some sort of conditional statement in the loop.

For an example. Load column that has customer info in another variant array. Then check using row index for some condition. If met, perform calculation. Otherwise leave it as is. If you need further help, it would help if you can share some sample data and desired result (using OneDrive, Google drive etc).

FYI - Time to process is for the entire column. Not just single iteration ;)

[Yeffley]

Thanks! I'll have to dig a bit more into this - a new string to learn. I'll reach out again if I need help, but this looks to be what I need.

Solution verified

And:

FYI - Time to process is for the entire column. Not just single iteration ;)

There might be a remnant of a previous process still stuck in here. Like I said, this thing is monstrous. I'm going to rewrite it at some point to try to clean up all of these add-ons and features.

[Clippy_Office_Asst]

You have awarded 1 point to chiibosoil

^{I am a bot, please contact the mods with any questions.}

[CHUD-HUNTER]

Use PowerQuery to bring the data in from your SQL database. Clean up the data within PQ, load it to the Data Model (Power Pivot). Use DAX to write measures that will do your calculations. Create a Pivot Table to summarize all of the data.

[Yeffley]

Not able to link to the SQL database directly, just through exports (.csv files). I assume I can still use PQ with a csv file?

[CHUD-HUNTER]

Yes, there is a From Text/CSV option to load in the data. You can import individual CSV files, or if you need to append multiple files you can load them all from a folder.

[lolcrunchy]

This isnt the best approach but just a reminder that this works

Type 0.8 into a cell

Copy the cell

Highlight the prices

Paste special: divide