I'm looking for help for a macro that can replace formulas that make my Excel file huge

[K0st4din]

Hi everyone I have a great excel workbook.
In the Cardio worksheet, I have formulas in several columns that take information from other columns and arrive at the desired end result.
My problem is that I don't know how to turn all this information that I do by hand into a macro that does it.
I will attach a sample table with an explanation and I hope it will be understandable. As if there are unclear things, I will cooperate.
In column B2:B, I have names, in column D2:D I have dates, in column J2:J, I have abbreviations, in column P2:P I have values of numbers with a plus sign (+) - >>> of all - described above, I take all the information to reach the following rows and columns, which are as follows: In cell AD1 -> start date, in cell AE1 - end desired date. In column AF2: I start listing each name in column B2:B (and the order is according to the number of codes in column AT2: to the end (how many), (as in the example BAY BOY KIL - 19 times, and so on for each subsequent name).
In column AG2: to the end I have a formula that gives me a result based on the formulas in column GA2:GZ2.
In column AI2: to the end I have a formula that gives me a result based on the formulas in column HB2:IA2.
The results I send in the example are 100% correct. But since I have to copy each name every month, then paste the exact number of codes, then put the formulas in the back columns and rows, and then my file becomes mega huge, a copy / paste special values so that the file can become much smaller again, and range GA2:GZ2, HB2:IA2 delete it completely to remove formulas and calculations to significantly reduce file size
At least I found this solution to get the right result, you may find it much easier.
So my idea is, can someone convert all these formulas to replace them with a macro to make my job easier.
Because the explanation is very long, I remain available to help if something is unclear.

test repeat name codes and total.xlsm

I apologize a lot, but with the proposed option to attach the sample table, it does not allow me, because the cells are more than 300 and I can only send you a link to google drive to download and view it.

 

[DanteAmor]

Do the following. Remove columns from GA:GZ, HB:IA
Run the following macro.

Note: Preserves the formulas in columns AG and AI.
The file will be reduced to 87kb from 37Mg

Sub Replace_Formulas()
  Dim a As Variant, b As Variant, x As Variant, y As Variant
  Dim i As Long, j As Long, k As Long
  a = Range("J2", Range("P" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a, 1), 1 To 26)
  ReDim c(1 To UBound(a, 1), 1 To 26)
  
  For i = 1 To UBound(a, 1)
    j = 0
    For Each x In Split(a(i, 1), "+")
      j = j + 1
      b(i, j) = x
    Next
    k = 0
    For Each y In Split(a(i, 7), "+")
      k = k + 1
      c(i, k) = y
    Next
  Next
  
  Range("GA2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  Range("HB2").Resize(UBound(c, 1), UBound(c, 2)).Value = c
End Sub

 

[K0st4din]

Hello DanteAmor
thank you for your cooperation, but it is not what I want to achieve.
And as I wrote, I'm looking for an option for the whole calculation, finding the names, the order, the placement of the formulas, so that the macro can do it. If it is only for one copy / paste special value, then I lose everything else, as a sought-after idea.
I understand very well that the file is very large (not that I do not want to attach it with the proposed program), but then you will not be able to see all the formulas.
The other important thing is that there may be a far easier way, but I haven't found it.
All cells, columns, rows, I fill them with formulas (as the letters change according to the cell). Since the table is very large and the formulas are in many places, I will try to somehow write it in this comment, describing where the formulas go and in which cells. I hope I do not confuse something with the order. Let's start:

in AG2: to the end 
=IF($AH2="";"";SUMPRODUCT(($D$2:$D$64902>=$AD$1)*($D$2:$D$64902<=$AE$1)*($B$2:$B$64902=$AF2)*($GA$2:$GT$65000=$AH2)*($HB$2:$HU$65000)))

in AI2: to the end 
=IF($AH2="";"";COUNTIFS($J$2:$J$65536;"*"&$AH2&"*";$B$2:$B$65536;$AF2;$D$2:$D$65536;">="&$AD$1;$D$2:$D$65536;"<="&$AE$1))

GA2:GZ65000
=TRIM(MID(SUBSTITUTE($J2;"+";REPT(" ";LEN($J2)));(GA$1-1)*LEN($J2)+1;LEN($J2)))

HB2:IA65000
=IFERROR(VALUE(TRIM(MID(SUBSTITUTE($P2;"+";REPT(" ";LEN($P2)));(HB$1-1)*LEN($P2)+1;LEN($P2))));0)

In column AT2:to the end is a product witch I select for every one Name (see my 1st post)
The problem is that the first two formulas cover a lot of x 65000 cells and from there the huge file is obtained, not to mention that the file is in .xls format.
The idea is that everything works, but if we can do everything with a macro, it will be wonderful.
Thanks in advance and stay available!

 

[DanteAmor]

I did a test with the file you shared and the size was reduced from 37Mb to only 87kb.
Did you give my code a chance and try it?

I understand that you want the complete solution, but let's start with what I already gave you and we can improve.

 

[DanteAmor]

The problem is that the first two formulas cover a lot of x 65000 cells

Do you have data up to row 65000 or are you just entering the formulas assuming data will exist.
The recommendation is that you copy the formulas only up to the row where you have data.
According to the file you sent, you only have data up to row 214.
In fact the formulas only have the range up to cell 214.
So just put the formulas up to cell 214.

 

[DanteAmor]

in AG2: to the end =IF($AH2="";"";SUMPRODUCT ...
in AI2: to the end =IF($AH2="";"";COUNTIFS ...
GA2:GZ65000 =TRIM(MID(SUBSTITUTE ...
HB2:IA65000 =IFERROR(VALUE ...

Ready, the following macro contains the 4 formulas mentioned.
Note:
If you have excel 2003 and the sheet is full, that is, all the rows have data, then change this line:
lr = Range("P" & Rows.Count).End(3).Row
For this
lr = 65536

Before testing:
Delete all formulas from your sheet.
Take a test with a sample of data so you can review the operations. Then perform the execution with all your information.

I did a test with 70,000 records and it took 10 seconds.

Sub Replace_Formulas()
  Dim a As Variant, b As Variant, c As Variant, d As Variant, e As Variant, x As Variant, y As Variant
  Dim i As Long, j As Long, k As Long, m As Long, lr As Long
  Dim dicGA As Object, dicJF As Object
  Dim dt1 As Date, dt2 As Date
  Dim ky1 As String, ky2 As String, cad As String
  
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  Set dicGA = CreateObject("Scripting.Dictionary")
  Set dicJF = CreateObject("Scripting.Dictionary")
  dt1 = Range("AD1").Value  'date ini
  dt2 = Range("AE1").Value  'date fin
  lr = Range("P" & Rows.Count).End(3).Row 'or lr = 65536
  a = Range("A2:AH" & lr).Value
  ReDim b(1 To UBound(a, 1), 1 To 26)
  ReDim c(1 To UBound(a, 1), 1 To 26)
  ReDim d(1 To UBound(a, 1), 1 To 1)
  ReDim e(1 To UBound(a, 1), 1 To 1)
  
  For i = 1 To UBound(a, 1)
    j = 0
    cad = ""
    For Each x In Split(a(i, 10), "+")    'column J
      j = j + 1
      b(i, j) = x
    
      'CountIfs
      If a(i, 4) >= dt1 And a(i, 4) <= dt2 Then
        If InStr(1, cad, x, vbTextCompare) = 0 Then
          ky2 = a(i, 2) & "|" & x
          dicJF(ky2) = dicJF(ky2) + 1
        End If
        cad = cad & x
      End If
    Next
    
    k = 0
    For Each y In Split(a(i, 16), "+")    'column P
      k = k + 1
      c(i, k) = y
      
      'SumProduct
      If a(i, 4) >= dt1 And a(i, 4) <= dt2 Then
        ky1 = a(i, 2) & "|" & b(i, k)
        dicGA(ky1) = dicGA(ky1) + Val(y)
      End If
    Next
  Next
  Range("GA2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  Range("HB2").Resize(UBound(c, 1), UBound(c, 2)).Value = c
  
  For i = 1 To UBound(a, 1)
    ky1 = a(i, 32) & "|" & a(i, 34)   'column AF | AH
    'SumProduct
    If dicGA.exists(ky1) Then d(i, 1) = dicGA(ky1) Else d(i, 1) = 0
    'CountIfs
    If dicJF.exists(ky1) Then e(i, 1) = dicJF(ky1) Else e(i, 1) = 0
  Next
  Range("AG2").Resize(UBound(d, 1), 1).Value = d
  Range("AI2").Resize(UBound(e, 1), 1).Value = e
End Sub

 

[K0st4din]

Hello DanteAmor
first I want to thank you for the quick reactions.
I may not have understood correctly, but I definitely didn't mean to offend you. I just thought that the first macro you send me is the solution and that's why I wrote the answer.
You are very right that we have to start somewhere.
Yes, 100% was the first to delete everything and save the information in the Names columns and the values I was looking for as a result.
Thank you so much!
I start with the following to move on:

Do you have data up to row 65000 or are you just entering the formulas assuming data will exist.

I do these lines with formulas up to 65,000 to stock up, because I don't know in the given worksheet how many rows of names I could have, it may be different, but yes with the macro, we could say search up to this row to which the names in column B2:to tne end (where there are names) - Here is OK with macro

We should now clear or tell the macro to start looking for the names and after checking how much they are to take each name from column B2: to the end (according to the rows which are) and for each name to order the codes from column AT (which at the moment there are 19 (They can be 3, they can be 50).
From line 40-41AG and AI (up to 214 - in the example, I don't need these zeros because I have nothing to calculate) down it shows me 0 because I don't have Names in the AF column.
Or in other words: just an example: If I have 3 names, the following should be obtained: 1st name x 19 codes, 2nd name x 19 codes, 3rd name x 19 codes and when there are no more names down there should be no more 0 (zeros) ) -> AG and AI.

If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False ->>>> here to be True

I'm trying to say "TRUE" on this line, but I can't (I'm probably mistaken)
Thank you once again and I remain available to reach the final.
You're a wonderful person.
Thank you very much!

 

[DanteAmor]

did you try the macro from post #6?

 

[K0st4din]

Hello again, yes I tried the macro and returned an answer, what remains to be finalized in my previous answer. Post number #6

 

[DanteAmor]

I do not understand your answer. I don't know if your problem has already been solved and since you didn't mark the post as a solution either. I don't know if it makes sense to continue making macros if you are not going to try them and you are not going to comment if they work for you.