Loop and Identify cells that sum to a value from another sheet?

[NewUserMo]

Hello All,

I am not sure if this is possible and I have not been able to find something in the the threads that would help me. My question is the following:

I have 4000 rows of data, one row contains text (column A) and another with numbers (column B) and a third column with text (column C) located in sheet 1 In sheet 2 there is a table that contains a number (total value) the text in sheet 1. We currently take the text and the value in sheet 2 and filter for the text in sheet 1. Starting at the bottom of sheet 1 we manually highlight the cells in column b until the sum is equal to or greater than the value in sheet 2. Once we find identify the cells in sheet 1 that sum to > or equal to s value in sheet 2 we update the sheet 1 column C value to "NO" text. For example:

Sheet 2
A 500

Sheet 1
A 100
A 300
A 1000
A 100
A 500
A 300
A 200
A 100

The code should change the last three rows column C value to NO. Please note the data is sorted and the code needs to start from the bottom and work its way up until the criteria (sun) is met.

Appreciate your help

 

[SkipVought]

Hi,

Am I to assume that you're using Excel? You never answered my question in your original post.

WHY do you have alpha characters in column A that you want to sum? And even worse, WHY the alpha character in Sheet 2 that the sum must be equal to or greater than? Makes absolutely no logical sense!

Please tell us what's going on!

 

[SkipVought]

Oh, is you example supposed to be columns A & B?

What about column C?

And don't you have your column C data?

The formatting could be done using a Conditional Format, which is a native feature of Excel.

What's the significance of "No" in column C?

Please answer all 4 questions.

 

[SkipVought]

Well here it is.

Make the table a Structured Table - Insert > Tables > Table
The default table name will be Table1.

Name the cell ranges on Sheet2
rTEXT
rVALUE

Sub SetNO()
    Dim lRow As Long, lLastRow As Long, nSUM As Long
    
    [Table1[Col C]].ClearContents
    lLastRow = [Table1[#ALL]].Rows.Count
    
    For lRow = lLastRow To 2 Step -1
        If Cells(lRow, "A").Value = [rTEXT] Then
            nSUM = nSUM + Cells(lRow, "B").Value
            Cells(lRow, "C").Value = "NO"
            If nSUM >= [rVALUE] Then Exit For
        End If
    Next
End Sub

Sample data
[pre]
Col A Col B Col C
A 100
A 300
A 1000
A 100
A 500 NO
B 300
A 200 NO
A 100 NO

[/pre]

Select the data in column B
Use this expression in the Conditional Formatting Rule Type: Use a formula to determine which cells to format.

 

[NewUserMo]

Thanks!

 

[NewUserMo]

Hi SkipVought,

One twist I forgot about. If the value in the table is greater than the total of a cells in column B in sheet one then change the all text in column C to "NO".

Thanks for the help!

 

[SkipVought]

You did notice that I used both the numeric value AND the text value (A) as a filter.

Did you try entering a numeric value that exceeds the sum?

 

[NewUserMo]

Yes, I have a numeric value in the table that exceeds the sum in sheet 1.

 

[SkipVought]

And...???

 

[NewUserMo]

sorry, I was not clear. I need to round up.For example if table value is less than the value in sheet 1. Hope that clearer.

 

[SkipVought]

well tell me exactly how the macro is not doing what you expect. Please be specific. Use your posted example.

 

[NewUserMo]

Hi SkipVought,

It work perfectly. I did something silly in the code. Thank you for your help!

 

[Andrzejek]

Welcome to TT
To show the appreciation for the help, please use “[blue]Great Post? Star It[/blue]” link. This will let others know which post was helpful.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.