Output based on rows of data that ends with another columns condition

H

hamistasty

Board Regular
Joined
May 17, 2011
Messages
208
Hello, I was hoping to get help on this simple output. I just haven't used excel in so long I've forgotten it all.

Basically, two columns outputs on the same row an ID exists are created, but only utilise the data in rows of another column up until an ID is listed.

[TABLE="class: grid, width: 503"]
<colgroup><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]ID
[/TD]
[TD="align: left"]Budget line[/TD]
[TD]Budget[/TD]
[TD="align: left"]Activity[/TD]
[TD="align: left"]Spent[/TD]
[TD="align: left"]% Spent[/TD]
[TD="align: left"]Total
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: left"]clean[/TD]
[TD]10[/TD]
[TD="align: left"]cups[/TD]
[TD="align: right"]4[/TD]
[TD] x
[/TD]
[TD] x
[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]
[/TD]
[TD] [/TD]
[TD="align: left"]desk[/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"]car[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: left"]repair[/TD]
[TD]20[/TD]
[TD="align: left"]cupboard[/TD]
[TD="align: right"]5[/TD]
[TD] x
[/TD]
[TD] x
[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"]shelves[/TD]
[TD="align: right"]4[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"]road[/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: left"]drive[/TD]
[TD]10[/TD]
[TD="align: left"]vehicle[/TD]
[TD="align: right"]9[/TD]
[TD] x
[/TD]
[TD] x
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: left"]deliver[/TD]
[TD]20[/TD]
[TD="align: left"]parcel[/TD]
[TD="align: right"]15[/TD]
[TD] x
[/TD]
[TD] x
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: left"]train[/TD]
[TD]15[/TD]
[TD="align: left"]in repairs[/TD]
[TD="align: right"]10[/TD]
[TD] x
[/TD]
[TD] x
[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"]in delivery[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"]in cleaning[/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"]in meeting[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"]in driving[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: left"]meet[/TD]
[TD]5[/TD]
[TD="align: left"]stakeholders[/TD]
[TD="align: right"]2[/TD]
[TD] x
[/TD]
[TD] x
[/TD]
[/TR]
</tbody>[/TABLE]

So essentially, I think this will be a condition formula? So it will see in row 2 that there is an ID, so in column f and g of row two it will calculate a formula with the values of column C and E until the next ID in column A is marked. So each x will be an output. The formula for column f will be the total of the sum of values in column E up until the row with the next ID divided into the budget in column C on the same row as the ID across. The output for column G where the x's are (opposite each ID) will be the sum of column E up until the next row that has an ID.

Would anyone be able to help with this?

Thanks
 
Sort by date Sort by votes
LloydFinancials said:
Based on your data, what are the x'es supposed to be?
Click to expand...

The x's are where the output would be. So based on the formula/condition it would read:

[TABLE="class: cms_table_grid, width: 503"]
<tbody>[TR]
[TD]ID[/TD]
[TD="align: left"]Budget line[/TD]
[TD]Budget[/TD]
[TD="align: left"]Activity[/TD]
[TD="align: left"]Spent[/TD]
[TD="align: left"]% Spent[/TD]
[TD="align: left"]Total[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: left"]clean[/TD]
[TD]10[/TD]
[TD="align: left"]cups[/TD]
[TD="align: right"]4[/TD]
[TD] 70%
[/TD]
[TD] 7
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]desk[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]car[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: left"]repair[/TD]
[TD]20[/TD]
[TD="align: left"]cupboard[/TD]
[TD="align: right"]5[/TD]
[TD] 55%
[/TD]
[TD]11
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]shelves[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]road[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: left"]drive[/TD]
[TD]10[/TD]
[TD="align: left"]vehicle[/TD]
[TD="align: right"]9[/TD]
[TD] 90%
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD="align: left"]deliver
[/TD]
[TD]20
[/TD]
[TD="align: left"]parcel
[/TD]
[TD="align: right"]15
[/TD]
[TD]75%
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: left"]train[/TD]
[TD]15[/TD]
[TD="align: left"]in repairs[/TD]
[TD="align: right"]10
[/TD]
[TD] 100%
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]in delivery[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]in cleaning[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]in meeting[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]in driving[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: left"]meet[/TD]
[TD]5[/TD]
[TD="align: left"]stakeholders[/TD]
[TD="align: right"]2[/TD]
[TD] 40%
[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
hamistasty said:
The x's are where the output would be. So based on the formula/condition it would read:

[TABLE="class: cms_table_grid, width: 503"]
<tbody>[TR]
[TD]ID[/TD]
[TD="align: left"]Budget line[/TD]
[TD]Budget[/TD]
[TD="align: left"]Activity[/TD]
[TD="align: left"]Spent[/TD]
[TD="align: left"]% Spent[/TD]
[TD="align: left"]Total[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: left"]clean[/TD]
[TD]10[/TD]
[TD="align: left"]cups[/TD]
[TD="align: right"]4[/TD]
[TD] 70%[/TD]
[TD] 7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]desk[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]car[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: left"]repair[/TD]
[TD]20[/TD]
[TD="align: left"]cupboard[/TD]
[TD="align: right"]5[/TD]
[TD] 55%[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]shelves[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]road[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: left"]drive[/TD]
[TD]10[/TD]
[TD="align: left"]vehicle[/TD]
[TD="align: right"]9[/TD]
[TD] 90%[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: left"]deliver[/TD]
[TD]20[/TD]
[TD="align: left"]parcel[/TD]
[TD="align: right"]15[/TD]
[TD]75%[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: left"]train[/TD]
[TD]15[/TD]
[TD="align: left"]in repairs[/TD]
[TD="align: right"]10[/TD]
[TD] 100%[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]in delivery[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]in cleaning[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]in meeting[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]in driving[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: left"]meet[/TD]
[TD]5[/TD]
[TD="align: left"]stakeholders[/TD]
[TD="align: right"]2[/TD]
[TD] 40%[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

I don't mean to bump, but I was hoping someone could help. Basically it reads add up rows from the row of this ID until you reach the next ID in column A. i just can't remember how do to that or what terms to use to search for it in google.
 
Upvote 0
hamistasty said:
I don't mean to bump, but I was hoping someone could help. Basically it reads add up rows from the row of this ID until you reach the next ID in column A. i just can't remember how do to that or what terms to use to search for it in google.
Click to expand...

Ok, so I made an effort, so maybe someone can help me with this:

Code: 
Sub myMacro()
Dim myRange As Range
Set myRange = Worksheet("DFATD2").Range("A9:A203")

For Each cell In myRange
    If Range(cell.Address).Value = 0 Then
        Range(cell.Offset(0, 11).Address).Value = Range(cell.Offset(0, 1).Address).Value
    End If
Next

End Sub

Basically for each blank cell in column A, i want it to take the first non blank cell first, offset to a column 11 columns but in the same row, and add it to a value. So in the above example I gave it, it will start with column A and because Id "1" is not blank, it is the first value, so it takes the offset column E which is "4", then adds it to a variable which I don't have int eh code above. Then, because the next row is blank, it adds that offset column, which is "2" to the 4. And so on, until it reaches the next ID which is "2". SO i want it to stop when it hits that next ID and take the total and put it in the first row of those additions offset from column ID value "1" into column "G", which is the 'total' column.

Then the process stars again with ID 2, and so on.

Can anyone please help?
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
run formula based on cell value to the last row
Replies
5
Views
432
MMM_84
M
P
  • Question Question
Applying conditional formatting to merged cells
Replies
1
Views
732
Joe4
Joe4
S
  • Solved
Combine columns using dynamic array formula
Replies
3
Views
313
Fluff
Fluff
J
  • Question Question
Conditional formatting based on value of another cell
Replies
3
Views
816
etaf
etaf
2
  • Question Question
Filter or Index based on cell value and column header
Replies
3
Views
307
Peter_SSs
Peter_SSs

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top