In column D of the file there are location names, however, they appear randomly throughout the data, although always in column D (i.e. there could be one or more spaces between each location name). In column I there are amounts associated with the location named in column D (for example, cell D2 may have London as the location, and cells I2, I3 and I4 might contain 50, 75 and 125 respectively, cell D5 might have Paris as the location with cell I5 and I6 containing 35 and 400 respectively).
I am trying, with no success, to write code to assess when there is a change in the location and then sum the associated amounts and place these results (or even the formula) in column J, adjacent to the location name.
I have picked up many great tips and tricks for Excel and VBA, but as I continue to learn, I continue to get into situations that frustrate me, and variable conditions seem to be one of those situations.
Any help - or even direction - will be much appreciated.
Have a great weekend.
In column D of the file there are location names, however, they appear randomly throughout the data, although always in column D (i.e. there could be one or more spaces between each location name). In column I there are amounts associated with the location named in column D (for example, cell D2 may have London as the location, and cells I2, I3 and I4 might contain 50, 75 and 125 respectively, cell D5 might have Paris as the location with cell I5 and I6 containing 35 and 400 respectively). You can do this without VBA. In an empty column, say J, in J2 enter the formula =IF(D2<>"",D2,J1). Copy this down to cover all rows with data in them. [Optionally, copy and paste special values... the contents of J.] Now, create a PivotTable with column J as the row field and column I as the data field. Select the SUM option for how the data field is summarized.
Thanks again and keep up the great work.
I was certainly surprised by "The Wild" - Who knows?, perhaps they'll hook up with the former Minnesota team, unless the Ducks manage to knock them off also!!
If I understand your comments, you're suggesting I look at the code that is used when I implement subtotals or pivot tables (both of which I am quite familiar with). Once I have reviewed this I will try to convert the results into VBA code.
Thanks again.
Originally posted by nevillestoke
Thanks Nate - I will definitely check this out, because I am not learning too much when the experts merely "write the code" for me. I'll check it out and let you know how things go.
Hope it goes well. Yes, well there are things to learn if you go through it. The code I posted yesterday is a little difficult to read if you're not fairly familiar with Excel vba, a lot of shorthand stuff, like 3 vs. xlUp and (2,7) instead of .item(2,7 or .offset(1,6). It's more the way I like to type out code (i.e., less typing)... But always feel free to ask, someone, I'm sure will help out.
Have a nice weekend.
Have you ever played with Subtotals or Pivot Tables (under data menu heading)? You may want to search the help file on subtotals, in '97 it kicked back some topics that might be up your alley, e.g., ways to summarize data.
Might want to explore these.
Incidentally, shame about them Leafs eh?
#If you have any other info about this subject , Please add it free.# |