Re: Copy adjacent cells?

• Previous message: Earl Kiosterud: "Re: Problem with graph ranges"
• In reply to: Dave Peterson: "Re: Copy adjacent cells?"
• Messages sorted by: [ date ] [ thread ]

Date: Thu, 2 Dec 2004 10:39:24 -0800

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

"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
news:41AE4B96.7EB1CF7@netscapeXSPAM.com...
> 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
>
> Alan wrote:
>>
>> 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
>
> --
>
> Dave Peterson

• Previous message: Earl Kiosterud: "Re: Problem with graph ranges"
• In reply to: Dave Peterson: "Re: Copy adjacent cells?"
• Messages sorted by: [ date ] [ thread ]