Search for name and letter matches and split equally

[k0st4din]

Hello, everyone,
after a long search to deal with different combinations of formulas by myself, I can't manage this work of mine, which takes me a long time to find a solution. I asked for help on other sites, but in a span of 3 weeks, no one can help me.
I am asking for some help because more than 2000 rows every month I am going crazy to plot values.
Here is my case study:
I have a very large table, in which on the right I have given names (in the example) from IVAN IVANOV 1 to IVAN IVANOV 35 - AK - BS.
From all these names in column BU, I have specifically selected names.
On the left of the same table, I have horizontally these same names from IVAN IVANOV 1 to IVAN IVANOV 35 - A:AI .
What I do is according to the selected names in column BU, start from A1 first, then A3, then B2 (as a final option and selection), look them up in the table on the right. Then on the same row, but already in the table on the left, if the name from BU - corresponds first to A1, then to A3 and finally to B2, to divide equally by the numbers to get 100% in column AJ (and the corresponding row ).
Apparently I can't handle it, because I tried with excel index match multiple criteria, SUM, SUMIF, but things don't work out for me.
I am asking for some help because I am processing over 2000 rows and this search and compare is totally killing me.
I will attach a sample table and I remain available if you have any questions, because for me personally, you may be confused somewhere in the very clarity of my problem.
Let me add one more thing:
The example with the division being equal is according to how many A1, A3 and B2 there are for the given row. The idea is that if I have 5, I divide them 100 / 5 and make each marked 20.
Since, in the example, I have forgotten test formulas, they do not play any role.
15,17,20,22,25,27,29 - these lines are an example of what should be obtained. The description of A1, A3, B2 above each of these lines (the previous one) is only and only, for your convenience, to be able to see, if the way and the formula are found, that there below should be the values, because they correspond in a real case of the truth.

[georgiboy]

Perhaps something like the attached?

[k0st4din]

Oh my god this is amazing!!!
I didn't believe I could do it in hundredths of a second.
Thank you so much!
However, now let me add one more thing (because I started with the idea of ​​formulas), and now things can also be obtained with a macro.
In this example table that I provided, I have before column A (from the example, because I'm going to change, columns and cells), So there I have some other things, let's call them Cities and Villages.
I tried the macro but when I want to use the filter to select the towns or villages I want and the macro does a mish-mash. Right, if it was a formula then it would be easy.
So in that line of thought, can it be done, even if there are filters, to read correctly the things put in the BU column again?
I'm worried that a filter will hide the searched things from that column and maybe I'll have to change them to be horizontal instead of vertical, for example: BU , BV , BW and so on to the end.
Just for the test, I added after BU, these cities and villages and I attach a photo, for the total confusion in the result (which, as I mentioned above, is normal because of the selected filter)
2022-10-08_092107.jpg

[georgiboy]

Do you select multiple items in the dropdown for the filter or just one?
Do you still want to process all of the data or only what is filtered?

One idea could be to remove the filter with VBA, process the data, apply the filter again.

[k0st4din]

Hello again
yes, to choose more than one city or village, but in some situations, it can be only one choice.
In that case we are talking about calculating things only for the filtered (selected) cities or villages or whatever.
And when I select other things, to do the calculation only for the new selected filter.
Removing and installing the filter can be done (it's a good idea).

[georgiboy]

Maybe the attached will do it?

It removes the filter, calculates all, applies the same filter.

[k0st4din]

Hello again,
i think there is some mistake.
Those test letters you put in, I decided to test before I threw the macro into the original table, but it didn't calculate correctly.
If I choose K and Ivan Ivanov 5,10,11,12,27 are selected - everything is as it should be, but then I choose Z and change Ivan Ivanov to 6,13,28,35, then it calculates correctly again, but accumulates and he sees that in each row there are both old and new choices.
Another thing I wanted to ask (because I couldn't see in the macro) - how exactly do you define a BV column (because in the real table it will be different so I know how to change my settings) and in the Name Manager should I also put these things?
2022-10-10_132311.jpg
I'll take a picture so you can see how it adds the new selected things to the old ones, which is not OK.
The idea is, when I select the new Ivan Ivanov ......., to calculate only for the selected ones, and not to have any of the old Ivan Ivanov .....
on the off chance that I have over 100% in some situations.
2022-10-10_132924.jpg
Thank you and I remain available!

[georgiboy]

I have added a line of code that clears all of the historic data before it calculates to the new values if you change the numbers in the list in column BU.

In the code it is the below part that defines the values/ range that are in column BU:

pVar = Range("BU8", Range("BU" & Rows.Count).End(xlUp)).Value

The part below then looks through the headings and creates a variant/ array holding the column numbers for each value in BU relavant to the table/ array:

    For r = 1 To UBound(rVar, 2)        
        For n = 1 To UBound(pVar)
            If pVar(n, 1) = rVar(1, r) Then
                ReDim Preserve pNumVar(z)
                pNumVar(z) = r
                z = z + 1
                Exit For
            End If
        Next n
    Next r

The rest of the code uses pNumVar to loop through the correct column numbers in the array.

[k0st4din]

No, not BU, but BV, where cities and villages (in this case d,k,z) are filtered. Because in the real table, this BV will be a completely different column (eg D). I'm trying to figure out where this column is defined.

[georgiboy]

That column does not get defined:

This part of the code below saves the view of the sheet as a temporary view (including your filters), it also removes the filters:

    If ActiveSheet.AutoFilterMode = True Then        
        Set cv = ThisWorkbook.CustomViews.Add(ViewName:="tmpView", RowColSettings:=True)
        ActiveSheet.AutoFilterMode = False
    End If

Then this part at the end of the code reapplies the filters by loading up the view we saved with the code above:

    If Not cv Is Nothing Then        
        cv.Show
        cv.Delete
        Set cv = Nothing
    End If

The part above, loads up the custom view (with your previously selected filters), deletes the custom view, sets the variable to nothing.

[k0st4din]

Yes, it calculates correctly now.
Since I'm not very familiar with macros, I'd just like to ask for clarification:
This "

Set cv = ThisWorkbook.C......................

" and this

If Not cv Is Nothing Then
         cv.Show
         cv.Delete
         Set cv = Nothing
     End If

ie this cv - is the column or coincidence?
If in my table this cv is G then it should do

If Not cv Is Nothing Then
         g.Show
         g.Delete
         Set g = Nothing
     End If......

[Aussiebear]

No, not BU, but BV, where cities and villages (in this case d,k,z) are filtered. Because in the real table, this BV will be a completely different column (eg D). I'm trying to figure out where this column is defined.

In most of your posts you keep mentioning column BU as the important column, so please understand that Geordiboy was simply following your input. You also may remember that in Post #8 he indicated the pVar value as "Range("BU8", Range("BU" & Rows.Count).End(xlUp)).Value". What was stopping you from changing this to "Range("BV8", Range("BV" & Rows.Count).End(xlUp)).Value' and testing?

[k0st4din]

Hello,
you didn't get it.
The macro is perfect, but at the very beginning I was thinking of a formula. After a way was found to be with a macro, then I mentioned that actually I have another column in which I select through filters the desired positions (my things, which in the example I called Cities and Villages).
Exactly those in the Cities and Villages example, just for testing I put them in the adjacent column BV. In my real table, those ranges in the macro will be changed to my real ones. And since in the example it is BV , but in reality in my table it will be G , that's why I asked how to replace BV with G in the macro.
BU remains important to me.
Thanks for understanding!

[georgiboy]

cv has nothing to do with a column name, if you look at the 'Dim' for 'cv' it is as below:

Dim cv As CustomView

cv is merely my way of knowing that wherever i mention cv in the code it is referring to a custom view.

As custom views hold the current filters i saw it fit to hold the current state of the worksheet, process the data with filters off, then reapply the filters as they were before.

The code will:
Save the view, remove the filters, process the data, apply the CustomView

As Aussie states above, it is the below line that needs to be amended to relocate column BU:

Range("BU8", Range("BU" & Rows.Count).End(xlUp)).Value

[k0st4din]

georgiboy, I'm getting worried! Thanks a thousand for your help, apparently I'm super stupid. Everything works super perfect, how do I replace the column BV, no BU, in my real table and where does it say in the macro. BU, it remains an important column for me, how to say in the real table (it should be column G, not BV), i.e. we now filter column BV with the letters for the test K,Z,D, in the real table we will have to filter column G. Again one, only question how to replace BV with column G? Is this what I don't understand or as I mentioned I'm terribly stupid?

[georgiboy]

You should not need to, you can amend the BU column as mentioned above.

The column BV moving to G should not make a difference as the filter for column G will be remembered within the CustomView wherever it is on the sheet.

As long as you amend all of the ranges in the code to be what they should be for the new layout:

    Range("A8:AI417").ClearContents 'Left table (no headers)    
    pVar = Range("BU8", Range("BU" & Rows.Count).End(xlUp)).Value 'Column BU
    lVar = Range("A7:AI417").Value 'Left table (with headers)
    rVar = Range("AK7:BS417").Value 'Right table (with headers)

    ...

    Range("A7:AI417") = lVar 'Left table (with headers)

If you want the code to only process on the filtered rows then we may need to use a different method altogether.

[k0st4din]

Ahaaaaaaaah, I'm so sorry, so if I filter the G column, everything will be as before, ie it will work super perfectly!
I told you, I'm probably very stupid, and I don't understand much about macros, and that's where my misunderstanding comes from!
I will put it in one copy in the real table, try it and write again.
I am very grateful and appreciative!
Last question before the real test: in the Name Manager should I also put these things? - which I asked about in post 7

[georgiboy]

No:

DoctorBase: Nothing to do with me
rngProduct: I created this when i was playing with something but then changed my mind - it is redundant.

[k0st4din]

OK, great thank you very much. I am testing and will write immediately after the tests with the real table.
You are an amazing person! Bow before you!

[Aussiebear]

Hello,
you didn't get it.

It seems I did, but you, yourself may not have. The pVar value could be changed to anything, even "G", which we only found about in post @13 of this thread. Accuracy in that which you inform the forum of is important. Please don't waste the time of others here if you simply want to disguise the real intent of your request.