Complicated counting problem

[fender357]

Hello everyone.
If this is the wrong place to post a topic with this kind of question please move me to the right place But I think my issue is only solvable using some kind of programing which is above my knowledge.

Problem:
I have this report generated by a program (Shortel phone management) that shows all the calls for a given period. This report is output as an Excel spreadsheet.
The goal is to be able to see how many calls each person listed has made but the way the report is generated makes this very hard.
There is a bunch of information but only 2 columns have any importance.


If you look at the files I have attached "Excel Example" shows what these two columns come out looking like.
We get the name of a worker on the first column and the date and time of calls in the next column. There are other bits in there since this is not at all clean data but the important parts are the names and the number of calls.
What is currently being done by hand straight in Excel is copy and pasting to get the whole report looking like "Excel Example Result".
I can't think of any way to do this in Excel, but I keep thinking there has got to be a way to automate something with Access to look at the name and then cut and paste it into each row below until it hits the Sub Total below every one. I can import this into Access pretty easily and from there I have been trying to find some way to automatically copy past through the thousands of lines in this report.
Am I thinking straight? Is there any possible way to do this?

Thanks kindly for any input

[ssanfu]

I threw together an example mdb with code I use to import text files. Of course, I modified the code to import your example.

This mdb used DAO (A2K format). I saved "Excel Example.xls" as a CSV file. See the attached zip file.

Once the code ends, open the table. Is this what you want?

[fender357]

OH MY GOD THAT IS EXACTLY WHAT I NEEDED!
You are a magician!!
THANK YOU!

[fender357]

Ok, now one more thing.
This is my fault for not putting the whole thing out there exactly as it comes out, but just to see if there is a way to make it work like this, here is exactly what the document originally looks like. The columns of importance are in B and C. The rest is not really important, I just didn't think someone would be able to answer me so fast with a way to do this!

[ssanfu]

I would delete all columns except B & C, then delete column A so that Columns B & C end up as Columns A & B.

Then save the worksheet as a CSV file. Otherwise, edit the VBA toget the same results.

[fender357]

Ok, and because things can never be easy, how would I allow it to take the columns B through S and keep that information? Essentially just like column C, but for the rest of the rows beside each name that gets put next to its call.
I'm going through the VB code and I think I am slightly understanding what is going on in there, but still I haven't done anything with VB before.
I'm not sure if adding those extra bits into each row would be as simple as specifying each row like it does with the first two or if there is a little more involved to get that data put through as well.

[ssanfu]

Ok, and because things can never be easy, how would I allow it to take the columns B through S and keep that information? Essentially just like column C, but for the rest of the rows beside each name that gets put next to its call.

It will take a re-write of the code

I'm going through the VB code and I think I am slightly understanding what is going on in there, but still I haven't done anything with VB before.
I'm not sure if adding those extra bits into each row would be as simple as specifying each row like it does with the first two or if there is a little more involved to get that data put through as well.

No, not simple, not hard, but it can be done. I'll work on it. Give me a day or two.....

[fender357]

You are a saint.
Learning alot with this too!

[fender357]

I just found an odd problem. If I save the columns to the CSV file type in Excel 2007, it works fine in the program. If I do the same thing in Excel 2003, then the program freezes up when I import the file. I'm not sure what the difference is between the two different files. I can see that one is a couple of kilobits smaller than the other.

[ssanfu]

I just found an odd problem. If I save the columns to the CSV file type in Excel 2007, it works fine in the program. If I do the same thing in Excel 2003, then the program freezes up when I import the file. I'm not sure what the difference is between the two different files. I can see that one is a couple of kilobits smaller than the other.

I'm not sure why that is happening. I think has something to do with A2K7, but I don't have A2K7 to test it with.

Try the import function in the attached mdb. There is data in the table from my last test run. Take a look at it, then delete the data and try importing several call lists.

The trick here is to find data that you can use to know when to add the data and when to loop or quit.
This seems to add the correct data "for this example". You need to test the Import routine.......

-----------------------------
Open the Excel data file (don't delete any columns), then save it as a CSV file.

Open the mdb, click on "IMPORT CALL LIST"

Again, it seems to import correctly, but please, check it.

[fender357]

Bringing this old post back to life because of course something is broken now!
The wondrous ssanfu was able to find a resolution for me last time to this but now the phone system has updated and they've changed the output format of the excel files! Which completely breaks the program!
Now the dumb thing gives a username AND date time in the same column!
So the required end result of the program is the same...
Rework the input file so that for each individual call, the username is put into a column next to the call information... But it's gone to a whole new level of "I don't get this..".
I'm attaching a straight export from the phone system so you can see exactly what it is doing now.
I'm also putting up the last version of the 'program' I had made using what ssanfu had given me to work with.
Any insight will be greatly appreciated.