Excel data from one sheet to another 1

[mattmontalto]

Hello All,

I am not the most fluent in excel, and I have a task that I am trying to accomplish that I need some assistance with. Let me explain the situation:

I have an application that is able to total the number of times a characteristic is chosen in a given month for a number of different characteristics. This application allows me to export the data set to excel. However, it comes out as a bunch of columns the represent the characteristics and a variable number of rows indicating all the records retrieved for a given month.

The only row of concern to me is the last one which has all the totals of the characteristics. This row number changes with every export, but the columns are always the same.

What I need to do is be able to take the data that is in each columns last row and put it into another excel sheet... preferable one that I can setup as a template. The reason for this is that the data when first exported is not easy to look at and decipher, but if I can just take the totals and put them into a template, my end users would be able to use it productively.

Any ideas how I can accomplish this anyone? Thank you

Matt M

 

[SkipVought]

HI,

It would sure help if you would upload your workbook. First this example would clarify what words cannot and second it would give those of us who want to help, a working model to use and even upload back to you.

Your explanation is confusing.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mattmontalto]

Sorry about that skip.... here it is....

Basically, columns A - F I dont need to do anything with,.... its columns G - CX that I need.... In the case of this excample.... I need the value that each of those columns contains in Row # 207

I want to take this data and be able to easily drop it into a template which essentially would be a neater way of displaying each columns total...... hope this makes it a little clearer... thanks

Matt M

 

[JVFriederick]

Is the original data in XL format or something else (csv ?)

If it is, would you also post this file?

We could accomplish what you need in a single step.

 

[mattmontalto]

The original data is being pulled from an SQL database by a PHP web application

But it is being exported out directly into this Excel file[sup][/sup]

 

[SkipVought]

What is the structure of the target worksheet where you want the results?

I assume that this is an ongoing project.

Where does this data come from and the type of file?

Are the sums (the data in row 207) ALWAYS in the same columns?

Please answer all these question fully.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JVFriederick]

Matt

There are MANY ways to do this. If you are comfortable using a file with VB code today is your birthday.

The attached file is designed to :
1) Browse to most recent XL file exported from PHP process.
2) Opens export file, finds last row and copies data into array. Closes export file.
3) Writes array data to last row of summary file (attached).
4) Edits the new data row to remove 'Total:' prefix so we now have numbers.
5) Formats new data row and adds a few formulas for review.

The file is set up with proper headings and subtotal formulas for long term use. Small section of file is shown here

The colored buttons are linked to VB code, and also contain 'mouse-overs' to explain their purpose.

I added more features than you asked because I find them useful when working with tabular data in XL.

JVF

 

[mattmontalto]

JVF

Thanks very much.... this is pretty incredible..... Is there a way that I can do this so that the columns become rows in column 1 and all the data (totals) become rows in column 3 of the sheet

My reason is that all the columns are heavily abbreviated so I would like to be able to input their full definitions in the rows of one column and their totals in the 3 column's rows.

I have to say though.... you did a brilliant job on this as it is.... really great stuff.... I do mostly servers and networking stuff.... if ever I can help you, would love to return the favor... thanks

Matt M

 

[JVFriederick]

Matt M

I may have missed in the original post where you wanted to transpose the results?

I had to learn how to do that ! Thank you for the challenge, now I can use that for someone else.

Since it's your birthday, I went ahead and added a sheet named 'Vertical'. I took a guess at the group classifications, change them as needed.

A screen shot follows :

After import change the date to whatever you wish.
You may use either sheet, or simply hide the one you do not wish to use.

JVF

 

[mattmontalto]

JVF,

This is fantastic and thank you.... would it be too much to ask you to remove the audio though....

Also.... I ran into an interesting dilemma that maybe you can solve?.... If you look on the initial exported sheet (Which I attached here)... you will see that there will be a number of records for the same file #... for five of the columns... (Inforeferral, Personaladvocacy, Counseling, Emergencyassist, & Crimejustsupport) I need to only count 1 instance of the file # per month... but for all the sub-categories of that particular service, I need to count them all...

Let me explain and maybe this will make more sense... Essentially columns G through AG are all demographical data and then moving forward from there... beginning with column AH, you have services and their sub categories (i.e. the column headers that have the A1 A2 A3 ...etc etc....)

Whenever someone gets a service... they also get a sub-cat. so every time a record is created for an individual the service itself is checked and so is the sub cat..... but if that person then gets another service that month a second record with that same file number is created.... and in my reporting, I need to count the number of individuals who received each of the services..... (the category).... but the total number of times the subcategory was selected in a month.

I realize how this may sound a little silly.... but I am not the one who creates these requirements.... Is there any way your tool can do this?

Thanks again for all the help... please let me know if I am not being clear and I will try and clarify....

Matt M.

 

[JVFriederick]

Matt
Have been out for past week, unavailable
to reply.

I will post final results back to
here so others may benefit.

JVF

 

[mattmontalto]

Thanks for the quick reply.... please take your time... no rush... and thank you.

Matt M

 

[mattmontalto]

JVF... I just had to add a column to the beginning of my export, so I wanted to send you an update version. thanks

Matt