Re calculation of weights

[erdem_ustun]

Hi,

I have excel files.

There are different sections in this file.

There are different features under each section.
Unequal weights are available for these features.
The sum of each section is 10.
However, if there are no features under a section, the weight of the non-features needs to be proportionally distributed to other features existing under the same section.
How can I do this with VBA.
First I set all the features and weights by default. Later, if the cell were written "No" in the column next to the no features, I thought it would be necessary to re-distribute the weight in this section.

But I could not .

As sample data

Nbr	Section 1	Default weights	Status	Reweighted
1.1	Feature 1	3		3,75
1.2	Feature 2	5		6,25
1.3	Feature 3	2	No
	Section 2
2.1	Feature 1	5		5
2.2	Feature 2	5		5
	Section 3
3.1	Feature 1	2	No
3.2	Feature 2	2		10
3.3	Feature 3	2	No
3.4	Feature 4	4	No
...	.....	.....	....	....

<tbody>
</tbody>

I would like your help in this regard.

 

[wideboydixon]

Here's my attempt:

Book1
	A	B	C	D	E
1	Nbr	Section 1	Default Weights	Status	Reweighted
2	1.1	Feature 1	3		3.75
3	1.2	Feature 2	5		6.25
4	1.3	Feature 3	2	No
5		Section 2
6	2.1	Feature 1	5		5
7	2.2	Feature 2	5		5
8		Section 3
9	3.1	Feature 1	2	No
10	3.2	Feature 2	2		10
11	3.3	Feature 3	2	No
12	3.4	Feature 4	4	No
Sheet1

Cell Formulas
Range	Formula
E2	=IF(OR($A2="",$D2<>""),"",$C2/SUMPRODUCT(($A$2:$A$12>=INT($A2))*($A$2:$A$12$A2+1))*($D$2:$D$12=""),$C$2:$C$12)*10)

WBD

 

[erdem_ustun]

thanks your reply wideboydixon
but how can I do with vba

 

[wideboydixon]

Public Sub ReweightFeatures()

Dim lastRow As Long
Dim thisRow As Long
Dim firstRow As Long
Dim weightTotal As Double

lastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
firstRow = 0
weightTotal = 0
For thisRow = 2 To lastRow
    If Cells(thisRow, 1) = "" Then
        ProcessSection firstRow, thisRow - 1, weightTotal
        firstRow = 0
        weightTotal = 0
    Else
        If firstRow = 0 Then firstRow = thisRow
        If Cells(thisRow, "D").Value <> "No" Then weightTotal = weightTotal + Cells(thisRow, "C").Value
    End If
Next thisRow

End Sub
Private Sub ProcessSection(firstRow As Long, lastRow As Long, weightTotal As Double)

Dim thisRow As Long

For thisRow = firstRow To lastRow
    If Cells(thisRow, "D").Value = "No" Then
        Cells(thisRow, "E").Value = ""
    Else
        Cells(thisRow, "E").Value = Cells(thisRow, "C").Value / weightTotal * 10
    End If
Next thisRow

End Sub

WBD

 

[erdem_ustun]

thanks your reply @wideboydixon

Excellent.
I've been trying to find a solution for about a week.
It's exactly what I want.

Could it be that we wanted to reduce the number of columns and be more efficient with one column?

Default Weights can not be changed. Constant values.

Can we make the table like this?
If it does not bother you, it will not take much time.

Could it make more sense if all the calculation / control is done in the weights column?

Default weights assigned to a two-dimension(Nbr,Default weights) and then
If do the operation above you can be less column and effective?

Nbr	Sections	Weights
	Section 1
1.1	Feature 1	3,75
1.2	Feature 2	6,25
1.3	Feature 3	No
	Section 2
2.1	Feature 1	5
2.2	Feature 2	5
	Section 3
3.1	Feature 1	No
3.2	Feature 2	10
3.3	Feature 3	No
3.4	Feature 4	No

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Best regards...

 

[wideboydixon]

Before:

Book1
	A	B	C
1	Nbr	Section 1	Weight
2	1.1	Feature 1	5
3	1.2	Feature 2	3
4	1.3	Feature 3	No
5		Section 2
6	2.1	Feature 1	5
7	2.2	Feature 2	5
8		Section 3
9	3.1	Feature 1	2
10	3.2	Feature 2	No
11	3.3	Feature 3	No
12	3.4	Feature 4	4
Sheet1

After:

Book1
	A	B	C
1	Nbr	Section 1	Weight
2	1.1	Feature 1	6.25
3	1.2	Feature 2	3.75
4	1.3	Feature 3	No
5		Section 2
6	2.1	Feature 1	5
7	2.2	Feature 2	5
8		Section 3
9	3.1	Feature 1	3.333333
10	3.2	Feature 2	No
11	3.3	Feature 3	No
12	3.4	Feature 4	6.666667
Sheet1

Public Sub ReweightFeatures()

Dim lastRow As Long
Dim thisRow As Long
Dim firstRow As Long
Dim weightTotal As Double

lastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
firstRow = 0
weightTotal = 0
For thisRow = 2 To lastRow
    If Cells(thisRow, 1) = "" Then
        ProcessSection firstRow, thisRow - 1, weightTotal
        firstRow = 0
        weightTotal = 0
    Else
        If firstRow = 0 Then firstRow = thisRow
        If Cells(thisRow, "C").Value <> "No" Then weightTotal = weightTotal + Cells(thisRow, "C").Value
    End If
Next thisRow

End Sub
Private Sub ProcessSection(firstRow As Long, lastRow As Long, weightTotal As Double)

Dim thisRow As Long

For thisRow = firstRow To lastRow
    If Cells(thisRow, "C").Value <> "No" Then
        Cells(thisRow, "C").Value = Cells(thisRow, "C").Value / weightTotal * 10
    End If
Next thisRow

End Sub

WBD

 

[erdem_ustun]

I appreciate your will and your knowledge.

I think I told you the last time I misunderstood.

If I try to tell you this way.First the C column (Weight) will be empty.

Before:

	A	B	C
1	Nbr	Section 1	Weight
2	1.1	Feature 1
3	1.2	Feature 2
4	1.3	Feature 3
5		Section 2
6	2.1	Feature 1
7	2.2	Feature 2
8		Section 3
9	3.1	Feature 1
10	3.2	Feature 2
11	3.3	Feature 3
12	3.4	Feature 4

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

After the user opens and writes the cells "No" in column C, calculations will be made.

When I saw the code you wrote, the logic came as follows.

I thought that when I run the code with the trigger(With Worksheet_change) when there is a change in column c,

when I write "No" for cells in column c, Recalculation is done.

Let's say I wrote first "No" after a certain period of time this feature provided and I will delete "No"


After:

	A	B	C
1	Nbr	Section 1	Weight
2	1.1	Feature 1
3	1.2	Feature 2
4	1.3	Feature 3	No
5		Section 2
6	2.1	Feature 1
7	2.2	Feature 2
8		Section 3
9	3.1	Feature 1
10	3.2	Feature 2	No
11	3.3	Feature 3	No
12	3.4	Feature 4

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Final:

<table cellpadding="2.5px" rules="all" verdana,="" arial,="" tahoma,="" calibri,="" geneva,="" sans-serif;="" border:="" 1px="" solid="" rgb(187,="" 187,="" 187);="" border-collapse:="" collapse;"="" width=""><colgroup><col><col><col><col></colgroup><thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[/TR]
</thead><tbody>[TR]
[TD="align: center"]1[/TD]
[TD]Nbr[/TD]
[TD]Section 1[/TD]
[TD]Weight[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]1.1[/TD]
[TD]Feature 1[/TD]
[TD="align: right"]6.25[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]1.2[/TD]
[TD]Feature 2[/TD]
[TD="align: right"]3.75[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]1.3[/TD]
[TD]Feature 3[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD]Section 2[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]2.1[/TD]
[TD]Feature 1[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]2.2[/TD]
[TD]Feature 2[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD]Section 3[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]3.1[/TD]
[TD]Feature 1[/TD]
[TD="align: right"]3.333333[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]3.2[/TD]
[TD]Feature 2[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]3.3[/TD]
[TD]Feature 3[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]3.4[/TD]
[TD]Feature 4[/TD]
[TD="align: right"]6.666667[/TD]
[/TR]
</tbody></table>

Sheet1​

I hope that I did not keep you busy.
I really appreciate you.
my best regards..

 

[erdem_ustun]

I wrote the final table incorrectly.
I'm sending the final table again for it so it does not look right.

Final:

A	B	C
Nbr	Section 1	Weight
1.1	Feature 1	6.25
1.2	Feature 2	3.75
1.3	Feature 3	No
	Section 2
2.1	Feature 1	5
2.2	Feature 2	5
	Section 3
3.1	Feature 1	3.333333
3.2	Feature 2	No
3.3	Feature 3	No
3.4	Feature 4	6.666667

<tbody>
</tbody>

 

[wideboydixon]

Doesn't make sense this way. How can you weight the features with no reference number to start with?

WBD

 

[erdem_ustun]

Hi wideboydixon ;

Doesn't make sense this way. How can you weight the features with no reference number to start with?

WBD

is it be possible in the following way?

1. Column Nbr and default weights are initially defined as a row constant or array(Nbr,weights).

2.Then, if there is "No" in the weight column, we will write the default weights to the weight column by matching the first item in array(Nbr,weights) with the column Nbr.

3.later,If weights are written when the end of the section is finished, The new weights are calculated for the end of the rewrite weights.

What will be the benefit or activity of process.

The user will not have to know all the weights beforehand.

The user will only write "No" when the feature is not available.

The user will be able to change the "No" has previously written.

The user will be able to change the status of an existing feature.


With all respect