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

  • Thread starter Thread starter Ernie Fenwick
  • Start date Start date
E

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
 
Ernie Fenwick said:
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
Click to expand...

As an example, suppose your costcodes were in D1:D100 and the corresponding
values in E1:E100. If I understand correctly, D1:D100 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?
 
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
 
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?
 
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
 
Paul

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

Regards

Ernie
 
Paul

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

Regards

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