Need some help with excel spreadsheet...

[warpman]

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.

 

[Dave Peterson]

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]

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]

The Data|Text to columns was an alternative to the formula approach (along with
the copy|paste special values)

But if you select that column, then do Data|Text to columns, you'll see that
wizard pop up to help.

On the first step (1 of 3), you'll be prompted to find out if your data is Fixed
Width or delimited (like with tabs or commas).

You'd choose Fixed width (then Next to step 2)

This is the screen where excel will guess at your columns. If you don't like
its guesses, you can "draw lines" where you want. In your case, you'd only want
one line drawn.

If excel guessed incorrectly, just double click on the existing line(s) to
remove them.

But you'd want a line between the first and second characters.

1YYMMDD
would look (sort of) like:

|
1|yymmdd
|

Then click the next button.

For the first field (that single column), you'd choose "Do not import column
(skip)".

For the second field, you'd choose Date ymd.

Then finish it up.

And format that column to show the date the way you like.

If you try this against a test worksheet, I think you'll find it the quickest
way to convert that kind of data to dates.

 

[warpman]

Hi Dave. You are good. Thanks so much for that info. I think this is a
better way as compare to the formula.

As for the second question...
I would like to get only one row per customer number with the total
amount for thos invoices. Is there a way?

 

[Dave Peterson]

You could sort your data by the customer number
select your data
data|subtotals

Another option would be to use a pivottable. These things are very powerful
summary tools. If you invest just a little bit of time, you'll wonder how you
lived without them.

If you want to try the pivottable stuff, you may want to look at somee 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