Reply to: Problems with Excel (again)

[LisaContractorUmbrella]

All sorted The pivot table is the winner.

Thanks for all your suggestions chaps.

[LisaContractorUmbrella]

That's because you're using the wrong tool.

The system that produces the .CSV file - it would probably be easier to get the summarised data extracted straight from there. Somebody must have written that extract, get them to change it to produce what you need, or get them to write another that does what you need.

Alternatively, MS Access is probably your best bet. Sucking the .CSV data into a table will create it for you. Creating a form using the form wizard that produces totals is quite easy. The form can just be copy 'n' pasted into whatever it is you are producing. Add to that a query to purge the table of records for re-use and the job's a good 'un for next time.

I hadn't thought of using Access Richard, that may be an option although I am not as familiar with it as with Excel. The csv extract was created for another purpose which is still required so this would necessitate our system developers writing a brand new report - it may actually still come to that if I can't do what I need to do with the existing data

[RichardCranium]

looks like this is going to be much more complicated than I thought

That's because you're using the wrong tool.

The system that produces the .CSV file - it would probably be easier to get the summarised data extracted straight from there. Somebody must have written that extract, get them to change it to produce what you need, or get them to write another that does what you need.

Alternatively, MS Access is probably your best bet. Sucking the .CSV data into a table will create it for you. Creating a form using the form wizard that produces totals is quite easy. The form can just be copy 'n' pasted into whatever it is you are producing. Add to that a query to purge the table of records for re-use and the job's a good 'un for next time.

[LisaContractorUmbrella]

Thanks for all the replies guys - much appreciated as usual - looks like this is going to be much more complicated than I thought and that pivot tables will be the way to go.

By the way I reached 500 posts during this thread

[MrMark]

Steady on chaps

Perhaps I should clarify - if a pivot table is the only option then it will be possible to integrate it but, for many reasons, a calculation that will do what I need it to do would be much quicker and simpler. Actually if the pivot table is the only option can someone clarify whether or not it will work within a macro if the raw data is different each time (column numbers and cell fomat the same, number of rows vastly different)

As`MF says, you can use a macro on the pivot table; an alternative would be to copy the data and paste-special (as values) and then manipulate it

[scooterscot]

I don't know if I'm being stupid here using

=SUMIF($B$1:$B$7,B1,$C$1:$C$7)

gives you the total sums against the names, it's then a simple case of sorting the list and selecting 'don't show duplicates'

on the plus side it does not matter if the order of the names in the list changes the total shall always be shown correctly.

A B C

A White 50 100
A White 50 100
B Blue 20 20
C Green10 25
C Green15 25
D Red 25 25
E Black 35 35

[RichardCranium]

All right, let's do this properly.

You say you start with a .CSV file but you don't. The .CSV file has to come from somewhere.

The start is a file or database or system which as the data you want in an unsummarised form. Someone or something extracts that into a useless-to-you CSV format.

The real solution is to get the source system changed to be able to provide the summaries and include them in a report.

Where does the .CSV file come from?

[MarillionFan]

You still haven't solved the users problem or even really listened to the requirements have you?

I've sent a PM offering assistance. You've offered...........?

[LisaContractorUmbrella]

Steady on chaps

Perhaps I should clarify - if a pivot table is the only option then it will be possible to integrate it but, for many reasons, a calculation that will do what I need it to do would be much quicker and simpler. Actually if the pivot table is the only option can someone clarify whether or not it will work within a macro if the raw data is different each time (column numbers and cell fomat the same, number of rows vastly different)

[oracleslave]

I think you have that statement in the wrong order.

You still haven't solved the users problem or even really listened to the requirements have you?

[Pondlife]

This thread looks like turning into a classic example of why users/bean-counters think you techies are a useless bunch of chancers

I'm amazed no one's tried to flog her SAP yet.

[MarillionFan]

This thread looks like turning into a classic example of why users/bean-counters think you techies are a useless bunch of chancers

I think you have that statement in the wrong order.

[oracleslave]

This thread looks like turning into a classic example of why users/bean-counters think you techies are a useless bunch of chancers

[LisaContractorUmbrella]

1.A pivot table will do the first part. No problem. If the data is from a database etc, you can link the pivot table to it without ever having to extract the data.

2. What does the macro do?

3. You can run a Vlookup against a pivot table.

Pivots, when used properly are by far the most powerful tool in Excel.

Hi MF

Basically I start with raw data in the form of a csv file which will always have same cell formats and column numbers but the rows will always be different. The raw data is then manipulated and turned into a report which is used on a daily basis. Problem is that the reports needs loads of changes made to be really useful - hence the macro. In this instance the pivot table would have to become part of the macro as it would be part of the changes required to convert the raw data into a meaningful report.

[MarillionFan]

1.A pivot table will do the first part. No problem. If the data is from a database etc, you can link the pivot table to it without ever having to extract the data.

2. What does the macro do?

3. You can run a Vlookup against a pivot table.

Pivots, when used properly are by far the most powerful tool in Excel.