| 0 |
Subject: My umpteenth Excel question
Posted by: rockafellerskank
- Dude [27652109] Mon, Jan 10, 2005, 18:43
Here's my latest little data gem.
I have a file with 1500+ line items and about 20 columns of data. Here's what I'm trying to do:
Column C = Account number.
Column L = Date of service
Column O = balance
It's possible to have duplicate account numbers IE I could have the account # '1234' repeating in Column C 1,2,3+ times. Sometimes Column L will be the same date, sometime different.
I want to look at Column C find it's matches (IE all the '1234' accounts), then look at Column L and finf the same dates (if any). If I find a match in Column L, I want to create a new column performing and additin function in Column Q adding together the various balances in Column Os.
If anyone can help, I can send you a samploe of data to work with so you can better visualize what i am talking about.
|
| 1 | rockafellerskank
Dude
ID: 27652109
Mon, Jan 10, 2005, 19:05
|
To add one more thing. I just found out Column P = facility has to match inroder to qualify to "sum up" as well.
|
| 2 | FRICK
ID: 3410101718
Mon, Jan 10, 2005, 19:26
|
I haven't personally tried it, but I think you could use SUMIF and the IF statements. How many different store # and dates do you have. If I recall one of your earlier posts you have something like 30 different dates don't you. Can you sort the data by store and then run the SUMIF statements.
I'm not positive, but it might be easier to write a macro. Do you have any experience writing macros?
|
| 3 | Guru
ID: 330592710
Wed, Jan 12, 2005, 16:43
|
Is this a one-time exercise, or something that will need to be repeated over and over? If it's a one shot operation, your best bet might be to do a sequence of several steps, as follows.
Try creating a new column which concatenates all of the data you want to match (acct#, date, and facility). You may need to convert the date to a text string (use the =TEXT function). Let's call that combination variable "ID". Your objective will be to sum the balances for all matching IDs.
You can then sort the data on that new column of IDs, and then create a new column that sums the current row's balance with the balance of the above row if the IDs match. Otherwise, just copy the current row's balance.
Then, you need a way to eliminate the duplicate rows. Create a new column of 1s and 0s, with 1 signifying that the ID below is different, and 0 signifying that the id below is a match. Convert the values in those last two columns (summed balances and the 1/0 column) to fixed numbers by copying the columns, and then paste special->values. Re-sort the data by the 1/0 column, delete the rows that are not wanted (they'll be the 0s using my example), and I think you'll have what you want.
An alternative way to attack this might be to use the pivot table function (it's an item in the Data menu), although if you are unfamiliar with pivot tables, this might be a challenging exercise to cut your teeth on.
|
| 4 | Mattinglyinthehall
Leader
ID: 01629107
Sun, Mar 13, 2005, 14:24
|
How do I get it to stop recognizing a number as a date and/or time?
|
| 5 | TB
Leader
ID: 031811922
Sun, Mar 13, 2005, 14:30
|
Under Format/Cells under the Number tab you can then click on date or number and choose how you want it set up.
|
| 6 | Mattinglyinthehall
Leader
ID: 01629107
Sun, Mar 13, 2005, 15:29
|
thanks!
|
|
|
|
Post a reply to this message: (But first, how about checking out this sponsor?)
|
|