Problems with Excel (again)

[LisaContractorUmbrella]

Hi Guys

I have a list of names and amounts - columns A & B as follows:

A White 50
A White 50
B Blue 20
C Green 10
C Green 15
D Red 25
E Black 35

I have added in column C which totals the figures in column B if the name e.g. in A2 is the same as the name in A1. This means that I have got a cummulative total for each individual. I now need to return the maximum value in column C for each individual e.g. for A White it would be 100 and for C Green it would be 25. I then need to be able to copy down the calculation as there is quite a lot of information involved. It would be possible to do this by sub-totalling but I also need to add a macro and use the MAX result in a VLookUp. As always any help gratefully received

 

[RichardCranium]

You're making it too hard, I think.

Column A = letters
Column B = names
Column C = numbers
Column D = running total

For Column D put this in cell D2 and replicate it all the way down:
=IF(B2=B1,C2+D1,C2)

Column E = the total per line

For Column E put this in cell E2 and replicate it all the way down:
=IF(B2<>B3,D2,"")

That gives you in column E the total for each name.

Is that what you're after?

 

[LisaContractorUmbrella]

You're making it too hard, I think.

Column A = letters
Column B = names
Column C = numbers
Column D = running total

For Column D put this in cell D2 and replicate it all the way down:
=IF(B2=B1,C2+D1,C2)

Column E = the total per line

For Column E put this in cell E2 and replicate it all the way down:
=IF(B2<>B3,D2,"")

That gives you in column E the total for each name.

Is that what you're after?

Hi Richard

Thanks for your quick answer. I had already added in the cummulative figure for column D but I only want to report the maximum cummulative figure for each individual so this:

A White 50
A White 50
B Blue 20
C Green 10
C Green 15
D Red 25
E Black 35

Would become:
A White 100
B Blue 20
C Green 35
D Red 25
E Black 35

Hope that makes sense

 

[RichardCranium]

Column E should have that number.

From this point I would simply copy 'n' paste special as just values into a new sheet, sort on column E, delete the lines without a value, then sort on column A, then copy 'n' paste that into my report.

But, if you want an automated way of doing it, I can't help. As a programmer with experience of support, I have a natural aversion to Excel macros and would just design the source data system to provide me with the summarised totals without going through Excel at all. Using Excel as an intermediary step is An Abomination Unto The Binary God as far as I'm concerned.

Then again, there must be a way of having in another column a lookup of all the rows in column E with a non-zero value and using that to reference the values in columns A, B and E for those rows, which is what you asked in the first place, wasn't it?

You don't have MS Access by any chance, do you? It'd be easier with that...

 

[LisaContractorUmbrella]

Column E should have that number.

From this point I would simply copy 'n' paste special as just values into a new sheet, sort on column E, delete the lines without a value, then sort on column A, then copy 'n' paste that into my report.

But, if you want an automated way of doing it, I can't help. As a programmer with experience of support, I have a natural aversion to Excel macros and would just design the source data system to provide me with the summarised totals without going through Excel at all. Using Excel as an intermediary step is An Abomination Unto The Binary God as far as I'm concerned.

Then again, there must be a way of having in another column a lookup of all the rows in column E with a non-zero value and using that to reference the values in columns A, B and E for those rows, which is what you asked in the first place, wasn't it?

You don't have MS Access by any chance, do you? It'd be easier with that...

Problem is that deleting individual rows manually is not an option as the (eventual) macro (apologies to the Binary God ) will be applied to data that will change each day i.e. the names in column B will be different and diffently ordered each time. Also the amount of data is quite large.

I do have MS Access but the data I will be using starts life as a csv download.

I have a feeling that the MAX and MATCH and IF functions combined in some incredibly clever way will give me the result I need but I am not that clever

 

[MarillionFan]

I may have missed the point here. But why don't you put it in a pivot table?

 

[RichardCranium]

I may have missed the point here. But why don't you put it in a pivot table?

Bloody newfangled MS Office 2000.

 

[Pondlife]

I may have missed the point here. But why don't you put it in a pivot table?

Agree with MF

 

[LisaContractorUmbrella]

I could use a pivot table if I wasn't going to be applying a macro and the VLookup later - although it may be a solution it would be a real pain in the

Any other thoughts chaps?

 

[MarillionFan]

1.A pivot table will do the first part. No problem. If the data is from a database etc, you can link the pivot table to it without ever having to extract the data.

2. What does the macro do?

3. You can run a Vlookup against a pivot table.

Pivots, when used properly are by far the most powerful tool in Excel.