Re: Need some help with excel spreadsheet...

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: warpman (warpman999_at_netscape.net)
Date: 11/18/04 

Date: Thu, 18 Nov 2004 16:17:02 GMT

Hi Dave. Thanks so much for your info.
I think I'm a little confuse about the "draw a line after a 1" I'm not
sure what exactly are you talking about.

The first thing I would like to do is change the date.
The second thing is the subtotals.

On the first one I found the MID function to work. Using the MID
function I was able to break down the data and get the correct format on
a separate column. I used the following formula:
=MID(C1,4,2)&"/"&MID(C1,6,2)&"/"&MID(C1,2,2)

After I got this on the first working for the first row I did a special
paste for the rest of the rows and it worked!

Now I got to work on the second issue. I'm going to try your suggestion.
I would let you know if this is going to work. Thanks again.

Dave Peterson wrote:
> I think I'd convert those 1yymmdd strings to real dates.
>
> Select that range that has those values.
> data|text to values
> draw a line after the 1
> Make sure you ignore that field (do not import)
> and select ymd for the "real" field
> Plop it right back where you got it.
>
> Then format|cells|number tab|custom category:
> In the type box:
> mmddyy
>
> The values will really be dates--just formatted to look like you want.
>
> And I'm guessing that you already sorted your range and did Data|subtotals to
> get those subtotals.
>
> But for some reason, those values aren't really numbers. Either they were
> imported as text or they have some extra characters in those cells that make the
> values non-numeric.
>
> =isnumber(a1)
> would be one way to tell.
>
> A quick way to convert text numbers to number numbers...
> Select an empty cell
> edit|copy
> select your offending range
> edit|paste special|check that Add button
>
> If that doesn't work--and it may not if your data came from a web page (HTML
> non-breaking spaces are a problem).
>
> You can use David McRitchie's Trimall subroutine to clean up that range.
> http://www.mvps.org/dmcritchie/excel/join.htm#trimall
> (look for "Sub Trimall()")
>
> And if you're new to macros, you may want to read David's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> warpman wrote:
>
>>I'm running excel 2003 on a winxp pro machine.
>>
>>I have a spreadsheet with the following information.
>>
>>Cust Number, Cust Name, Inv Num, Inv Date, Description, Amount
>>
>>There are many invocices per customer number. The date appears as
>>1YYMMDD.
>>There are a couple of things I would like to Change. I want to change
>>the date to MMDDYY format. I want to add the amount on the invoices
>>and have the following per customer numnber: Cust Number, Cust Name
>>and a Total amount from the invoice amount. Instead of having one 5
>>lines for 5 invoices for 1 customer number I want to have 1 customer
>>number an a grand total on the invoices.
>>I have been able to run the subtotals on the amount colunm but it only
>>shows Customer Num Total $00000.00 (as an example). It also shows the
>>other rows with the information. I need to press the + or - sign to
>>open or close the rest of the rows. I don't want this.
>>I have not been able to find the substring function to break the date
>>field or may be I have not been looking in the right location.
>>Any help on this would be greatly appreciated.
>
>

Relevant Pages

  • Re: Problem with Data Subtotals
    ... I have never been a fan of Subtotals, and prefer to use Pivot Tables to ... Drag Amount to the Data Area and Hours to Data area underneath ... Drag the Data button to sit over the Total column, and Amount and hours ... Excel version 2003 SP2. ...
    (microsoft.public.excel)
  • Re: The difference between two thousand and ones and two thousand one.
    ... >With the entire pre-printed sentence being ... >>> writing strange things on the amount line. ... >> that nobody is likely to forge the pence amount. ... There's isn't any word to draw a line up to - I draw a line to the end ...
    (alt.usage.english)
  • RE: Sort a Subtotalled List?
    ... Then I clicked Data/Subtotals ... Try collapsing the subtotals so you ... >> column has the subtotal amount). ...
    (microsoft.public.excel.misc)
  • Re: Problem with Data Subtotals
    ... And few people will open files from unknown individuals. ... Multilevel subtotals are in the wrong position in Excel 2002 and in Excel 2003 ... Hours, Amount and State. ...
    (microsoft.public.excel)
  • Re: Why does my record pointer move
    ... Either do a Select Invoices and Select Customer ... before and after the Sum command or use SQL Select statement as suggested by ... > current table and for each record add up the value of the Amount ...
    (microsoft.public.fox.programmer.exchange)