Problems with Excel (again)

[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.

 

[oracleslave]

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

 

[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.

 

[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.

 

[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?

 

[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)

 

[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...........?

 

[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?

 

[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

 

[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