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)
30215

[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?
30218
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.
30219
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.

[k0st4din]

Hello georgiboy (http://www.vbaexpress.com/forum/member.php?15252-georgiboy)
i changed the range in the macro with my ranges but it doesn't work and it gives wrong result.
You say that the macro remembers the set last filter and works on it.
The columns I work with are: G - filter, D - filter and remember the last changed in the buffer memory. This BV is my D . The most important BU (from the example product), for me is DZ.
By all principles, it should work, but again, it distributes itself wherever it pleases.
I am also uploading the modified macro with my data.

Option Explicit

Sub testsheet1()
Dim lVar As Variant, rVar As Variant, pVar As Variant
Dim pNumVar() As Variant
Dim r As Long, n As Long, z As Long, x As Long
Dim rNumVar() As Variant
Dim fnd As Long, sfnd As Long
Dim cv As CustomView


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

Range("L8:AT417").ClearContents

pVar = Range("DZ8", Range("DZ" & Rows.Count).End(xlUp)).Value
lVar = Range("L7:AT417").Value
rVar = Range("AV7:CD417").Value

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

z = 0
For r = 2 To UBound(lVar)
For n = 0 To UBound(pNumVar)
If rVar(r, pNumVar(n)) = "A1" Or rVar(r, pNumVar(n)) = "A3" Then
fnd = fnd + 1
ReDim Preserve rNumVar(z)
rNumVar(z) = pNumVar(n)
z = z + 1
ElseIf rVar(r, pNumVar(n)) = "B2" Then
sfnd = sfnd + 1
ReDim Preserve rNumVar(z)
rNumVar(z) = pNumVar(n)
z = z + 1
End If
Next n
If fnd > 0 Then
fnd = fnd + sfnd
For x = 0 To UBound(rNumVar)
lVar(r, rNumVar(x)) = 100 / fnd
Next x
End If
Erase rNumVar: z = 0: fnd = 0: sfnd = 0
Next r

Range("L7:AT417") = lVar

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

[georgiboy]

So these are not the correct results for the filtered data on the attached?


You say that the macro remembers the set last filter and works on it.

I said it removes the filter, works on all of the data, puts the same filter back on.

[k0st4din]

So these are not the correct results for the filtered data on the attached?



I said it removes the filter, works on all of the data, puts the same filter back on.
Hello,
I must have missed a sentence of yours that has us both confused.
But unfortunately, when we write, it is possible to drift apart, I hope you understand me.
I attach the table in which I have filtered, for example, what I want and when I press the button, things are distributed correctly. - But the problem is that when I removed the 2 filters, there are results in the other rows that I didn't select at all.
I only want what I have filtered to show me values ​​in the cells. Then if I choose other things from the filters, it will calculate them, again only for my second, third, fourth choice, etc. as many as I have.
The moment you remove the filters, you will see that there is information in the other rows of the table, which I don't need, because for them there will be other searched things from the BU column (or in my real table DZ )
Where am I wrong?
I'm getting desperate!

[georgiboy]

You are not wrong, all along I have explained that the code is calculating ALL results regardless of the filters. (Remove filters, process data, apply filters)

I think i mentioned a few posts back that if you want it to run the results on only fitered data then you would need a completely different approach.

[k0st4din]

Yes, yes in post 16 I missed the last sentence. I am very sorry for this omission of mine.
Will we be able to fix the situation?
Thanks again with all my heart!

[georgiboy]

Is it a problem that all results are calculated?

Do you plan to change the BU column between different filters used?

[k0st4din]

Is it a problem that all results are calculated?

Do you plan to change the BU column between different filters used?
Yes, it is a problem if I put some filters and it calculates for me in the entire table.
-------------
As I mentioned, it should calculate only and only on the selected filters. When I select other filters, it will have to calculate based on the new ones without adjusting anything else.
Please download the latest file. This BU column, in my original file is a DZ column (only there will be a replacement of the different desired products to be distributed according to the selected filters).

[georgiboy]

Try the attached instead, personally i would move the DZ column to another sheet.

[k0st4din]

Hi, now everything is 100000000000% correct.
Many thanks for all the efforts on your part.
I was really desperate and thought that no solution would be found and I would have to read thousands of lines again line by line.
I wish you endless health and always help people in need!
:friends::clap::bow::beerchug: