Consolidating values

  • Thread starter Thread starter RD Wirr
  • Start date Start date
R

RD Wirr

I have a list of names and values that has many repeated
names. These names correspond in the second column to
values (invoice values) I am trying to find a method
(without filtering) of consolidating these names into
another column with a single instance or cell for each
unique name and a corresponding cell next to it with the
sum of all values corresponding to that name in the
original list of data.
Can anyone help me with this?
Thanks,
RDW
 
Hi Frank,

Thanks for you input. Yes, I thought about that but I
have a whole lot of other things happening on the same
page that would be complicated by a pivot table. Is there
any straight formula or array formula for achieving this?

Rgds,
RDW
 
Hi
The problem is creating the list of inique entries with a formula
(without blank rows in between). If you have this list of unique
entries a straight SUMIF formula will do. Some ways to create a list of
inique filters:
1. Use Advanced filters ('Data - Filter') to create a list of unique
entries
2. If you need to do this automatically with a formula try the
following (Assumptions: column A: Names, column B: values. row 1:
heading row)
- Enter a new column A (used as helper column). In A2 (first data row)
enter the following:
=IF(COUNTIF(B$1:B2,B2)=1,MAX(A$1:A1)+1,"")
copy this down for all rows

- So now lets assume the put your list of unique entries in column F
(also starting in row 2). Put the following in F2:
=IF(MAX(OFFSET($A$1,0,0,COUNTIF($A:$A),1))<ROW(1:1),"",VLOOKUP(ROW(1:1)
,OFFSET($A$1,0,0,COUNTIF($A:$A),2),2))
copy this down

- in column G the put your sums. Enter the following formula in G2:
=IF(F2<>"",SUMIF($B$1000,F2,$C$1000),"")
copy down

Frank
P.S.: see http://www.contextures.com/excelfiles.html#Function for an
example worksheet using the above procedure to create unique entries
 
Hi Frank,

I used the formulas you indicated below and it worked
perfectly. Thanks very much, you have helped a lot. Also
thanks for the tip to see d. dalgleish's site. There is a
lot of good ideas on there.

RDW
-----Original Message-----
Hi
The problem is creating the list of inique entries with a formula
(without blank rows in between). If you have this list of unique
entries a straight SUMIF formula will do. Some ways to create a list of
inique filters:
1. Use Advanced filters ('Data - Filter') to create a list of unique
entries
2. If you need to do this automatically with a formula try the
following (Assumptions: column A: Names, column B: values. row 1:
heading row)
- Enter a new column A (used as helper column). In A2 (first data row)
enter the following:
=IF(COUNTIF(B$1:B2,B2)=1,MAX(A$1:A1)+1,"")
copy this down for all rows

- So now lets assume the put your list of unique entries in column F
(also starting in row 2). Put the following in F2:
=IF(MAX(OFFSET($A$1,0,0,COUNTIF($A:$A),1))<ROW (1:1),"",VLOOKUP(ROW(1:1)
,OFFSET($A$1,0,0,COUNTIF($A:$A),2),2))
copy this down

- in column G the put your sums. Enter the following formula in G2:
=IF(F2<>"",SUMIF($B$1000,F2,$C$1000),"")
copy down

Frank
P.S.: see
Click to expand...
http://www.contextures.com/excelfiles.html#Function for an
 
Back
Top