I would use a pivot table but.....

[Ernie Fenwick]

Hi

I would use a pivot table but there are other restrictions
that apply. 1. Some columns are merged and the pivot table
doesn't work. 2. I need to do further data manipulation
with the results.

I have a table with several identical costcodes each with
different values.

I need to create a table with each cost code in column A,
the subtotal of the values in column B which will then
allow me to add formulae to manipulate these results.

Does the original table need to be sorted in costcode
order to return only one instance and if so what is the
workbook command.

Thanks

Ernie

 

[Paul]

Hi

I would use a pivot table but there are other restrictions
that apply. 1. Some columns are merged and the pivot table
doesn't work. 2. I need to do further data manipulation
with the results.

I have a table with several identical costcodes each with
different values.

I need to create a table with each cost code in column A,
the subtotal of the values in column B which will then
allow me to add formulae to manipulate these results.

Does the original table need to be sorted in costcode
order to return only one instance and if so what is the
workbook command.

Thanks

Ernie

As an example, suppose your costcodes were in D1100 and the corresponding
values in E1:E100. If I understand correctly, D1100 can contain each
costcode any number of times, and you are wanting a sum of the corresponding
values for each unique costcode. This table (D1:E100) does not need to be
sorted.

Suppose also that the first unique costcode in the table you want to
construct is in A1.
In B1, you could put the formula
=SUMPRODUCT(($D$1:$D$100=A1)*$E$1:$E$100)
Then copy this formula down column B as far as you have costcodes in column
A.

Does this give what you want?

 

[Ernie Fenwick]

Well No it doesn't.
I have created a table as you suggest with the data in
columns D and E and cut and pasted your formula into B1,
replicated down several rows with cost codes manually
inserted into column A but this gives values of zero in
column B.

I also want to create column A dynamically from the
original table whereas with the sumproduct you have to
manually input the costcodes in column A.

Regards

Ernie

 

[Paul]

You will only get a zero result if the costcode in A1 does not match any in
column D (or, trivially, if the values summed are zero). The match must be
exact; an extra space, or a number in one and its text equivalent in the
other, will not work. Make sure A1 is formatted the same as column D. Try
copying from, say, D7 and pasting into A1.

I agree that you have to manually input costcodes into column A. But surely
you know what costcodes are possible?

 

[Ernie Fenwick]

Paul

Thanks. Formatting was the problem. As to the other
question There are several hundred costcodes and I am
wanting to use this for several people, some of which are
not all that computer literate, so I was trying to
automate as much as possible. I am sure I have seen it
somewhere that a shortened list of each incidence of
several codes or values can be created.

Regards

Ernie

 

[Ernie Fenwick]

Paul

The formatting only appears to have worked on the first
row, the other rows are all returning zero.

Regards

Ernie

 

[Ernie Fenwick]

Paul

I have input the data again and the formatting has worked
this time so that part is fine.

Regards

Ernie

 

[Paul]

Chip Pearson has details of how to extract unique items from a list
containing duplicates, which might help you:
http://www.cpearson.com/excel/duplicat.htm#ExtractingUnique

 

[Ernie Fenwick]

Paul
Many thanks
I now have a brilliant spreadsheet. Your advice was great.
Regards
Ernie