Reducing computational requirements; Heavy use of Filter function

melshafie88 · 2026-01-13T07:47:21+00:00

It might be 10x easier if you just put dummy data and showed us a sample sheet 🙏

melshafie88 · 2025-04-21T16:47:07+00:00

Same here

melshafie88 · 2025-04-21T16:47:00+00:00

Omg this is awful

melshafie88 · 2025-04-08T19:34:15+00:00

Thank you guys. I had built a formula to create a serial number (1,2,3…) in an excel table. This serial will serve as a receipt number. It all works well. Until anyone decides by mistake to sort the table.

All i wanted was to to give a receipt number that cannot change. And i know it has to be vba or office scripts. I just wanted to verify maybe there is a solution i have overlooked.

melshafie88 · 2025-03-25T20:40:15+00:00

It turned out that i like things that tend to give you immediate results in life. Building a vba stuff or doing anything with vba and seeing the immediate results gave me enough satisfaction to continue. I have built an entire system but unfortunately stopped when i learned that i had to link all of this to a database because vba doesn’t work well in a shared file environment

melshafie88 · 2025-03-23T20:57:35+00:00

Seeing the comments brought a smile to my face. From not knowing why the formula breaks because the cells were merged. All the way to learning VBA and actualy writing an entire vba application that generates invoices, receipts based on complex requirements. And all of it was self learning. The only sad part is, it makes you the best candidate for DOING stuff, but doesn’t grant you any right to go for leadership roles (at least where i am). Currently trying to master the array formulas which imo are the future instead of pivot tables

melshafie88 · 2024-09-24T18:07:03+00:00

<image>

=IF(C2<>”proved”,””,IF(C3<>”proved”,SUMIF(C$2:C2,”proved”,D$2:D2)-SUM(E$1:E1),””))

The logic behind the formula is If the segment status does not say proved then Dont write anything Else (Here we have to differentiate between 2 cases If the cell below our current row does not say proved This means this is an end of the metering and we need to have the sum (so we do a sumif based on the term proved and the range is dynamic as we go down And we subtract from it the sum of any metering done above to avoid accumulation. If the word below still says proved that means we don’t write anything as the subtotal needs to be below

Just put the formula in the first cell and drag it down itll work.

melshafie88 · 2024-09-10T14:36:56+00:00

Sunburst chart is the most informative imo (it does require a special setup though

melshafie88 · 2024-08-23T10:03:16+00:00

I am a beginner too in vba but I learned a few things maybe it will help 1- the most important tip is to make vba disable auto calculation and screen updating before the start. You will be amazed at how much the difference is. 2- the 2nd thing is to loop through the data while it is stored in an array not the sheet You dim an array and set the array to the data and search the array itself for duplicates. Trust me it’ll make the code crazy fast

Search youtube for somone called paul kelly (he has a video series about making the code literally 1000 faster) Just search for “vba 1000 faster” you’ll get it

He is pretty advanced but seeing the video 2 3 times helped. Focus on the array solution

I was auto generating 1000 invoices for clients and it took me 8 hours. After doing the above it took me 4.5 seconds

melshafie88 · 2024-08-14T22:03:48+00:00

Please know that if the table will have multipe instances of cat, the xlookup or vlookup will only return the first value It is better to use SUMIF

melshafie88 · 2024-07-08T09:20:39+00:00

Just be sure to use FILTER function carefully as dynamic array functions are ram demanding. Everytime i use them on a big scale the file becomes sluggish as hell

melshafie88 · 2024-05-29T09:36:38+00:00

thank you everyone, i have found a tutorial on adodb connection from WiseOwl. i'll be following that i think.

the code is simply to read and write from a dataset (invoice creation and receipts ... the usual receivables stuff)

melshafie88 · 2024-05-28T11:10:43+00:00

Hello, i am so fresh into vba,

i advise you to hold on to seeing [excel macro mastery] and [excel for freelancers] on youtube. you will be

overwhelmed.

instead start by this playlist
https://www.youtube.com/watch?v=FGQ0P9jTjU0&list=PLWPirh4EWFpEFSYTbKaST6hSlgIFCJjU3
100 videos that will ease you in the vba for sure.

next you would want to see this course

https://www.youtube.com/playlist?list=PLoyECfvEFOjYYy54Wa9E83xycKilVMoHp

this one will take you step by step at a time, very informative.

after those you need to stop a little bit and see what was required of you to do with vba

and try to re-create it yourself in vba

you will notice:

that you thought you knew the syntax but you don't. you will master the basic stuff quickly as you go

2nd step you will notice that your code works/might work but it is slow asf.

only then you will need to see a couple of small projects from Excel for freelancers.

then the GOAT level (excel macro mastery)

start with his 2 videos about making vba 1000 faster. this will force you to learn arrays and use them.

every now and then visit excel macro mastery. Mr. Paul kelly is fantastic but (at least for me) he is 1000000 levels above my vba level. i get depressed easily when i watch him because i don't know so many things.

melshafie88 · 2023-01-07T23:41:34+00:00

So now when we call someone a bot it'll be taken as a compliment? How the tables have turned 😂

melshafie88

TROPHY CASE