Copy adjacent cells?

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

I am using XL 2002 and want to automatically copy adjacent cells after
subtotalling:

5/1/04 TAPCO 133121 PROMAX SYSTEM, INC 1,197.00 TAPCO
5/1/04 TAPCO 133121 PROMAX SYSTEM, INC 229.00 TAPCO
PROMAX SYSTEM, INC Total 1,426.00


I want to somehow have the cell to the left of the total cell, reflect the
cell right above it - in this case it would be "TAPCO 133121". The
challenge is that the tables I work with, once subtotaled, have more than
30,000 records. Some of the subtotals are based on two rows like the one
above, though they vary from one to 25, so there are never a fixed amount of
rowns between them.

Any help , guidance would be appreciated - I've been copying and pasting for
days and days and days.............

Thank you !

Alan
 
It's kind of difficult to see how your data is laid out (for me anyway).

But if you have a cell (column A???) that's only empty on that total row--and
always filled in on the detail rows, you could use something like this:

Select your range in A1:A30000 (only as far down as your last total row).
then Edit|goto special|Blanks
now only those cells are selected.

Say you're in A15 and you want to retrieve the text from B14.
Type =B14 (in A15)
but instead of hitting enter, hit ctrl-enter. All those blank cells will be
filled with formulas that refer to the cell one row up and one column to the
right.

======
If that doesn't match your data, you could insert a new column (say A).

then put this kind of formula in A2:
=if(countif(c2,"*total*")>0,C1,"")

Adjust c2 to the cell that contains the word Total. And adjust C1 to be the
cell that contains your value you want.

And drag down.

=======

But if you've worked with Data|subtotals long enough, you know how slow they can
be when your data gets as large as 30000 rows.

You may want to look at Data|pivottable. If you've used them and they don't fit
your situation, never mind. But if you've never used them, you could invest an
hour struggling and save lots and lots of time later. (You'll find that the
things you did in 2 hours are done in 10 minutes!)

If you want to read more about the pivottable stuff, you may want to look at
some links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx
 
Dave -
many many many thanks! I tried your first suggestion and indeed it worked.
no kidding you just saved me4-6 hours or relentless copying and
pasting.......

thank you !

Alan Geer
 
Back
Top