sum column a if column b, c, d, and e are equal
one method
use two helper columns (E,F?)
in E1
=a1
in e2
=if(and(B1=B2,C1=C2,D1=D2),E1+A1,A1)
in F1
=if(and(B1=B2,C1=C2,D1=D2),"",1)
copy E2 and paste to the end of your data
copy f1 and paste to the end of your data
select columns E and F
copy and paste special values
select column F and filter-autofilter
select blanks
select the visable cells and edit-delete rows
remove autofilter
select column E copy ans select Cell a1
paste spectial values
(It sounds more complicated than it is, but make sure you first try it on a
copy of your data)
"jj" wrote:
a b c d e
quantity | size | use |part num |frame
9 65.375 Hd 450-026 A
18 67.3437 Hd 451-CG-004 A
1 67.375 Sill 451T-CG-001 A <
2 67.375 Sill 451T-CG-001 A <
3 67.375 Sill 451T-CG-001 A <
18 70.875 WJambl 451T-CG-001 A
9 67.3437 Horz 451T-CG-002 A
18 67.375 Hd 451T-CG-003 A
9 71.625 SillFp 451T-HP-037 A
18 70.875 WJambl 452-145 A
8 30.6875 Hd 450-026 B
8 32.6562 Hd 451-CG-004 B
4 67.3437 Horz 451-CG-004 B
4 67.375 Sill 451T-CG-001 B
4 21.75 Vert 451T-CG-001 B
8 70.875 WJambl 451T-CG-001 B
4 67.3437 Horz 451T-CG-002 B
I want to eliminate redundancies while summing column "A" and keeping same
comma separated values...
I'm using Excel 2003 and have got real close but I just don't know enuf to
write SUMIF & filter B, C, D, E ARE EQUAL TIL UNIQUE VALUE AND THEN KEEP
GOING?
quantity |size |use |part num |frame
9 65.375 Hd 450-026 A
18 67.3437 Hd 451-CG-004 A
6 67.375 Sill 451T-CG-001 A <--------
18 70.875 WJambl 451T-CG-001 A
9 67.3437 Horz 451T-CG-002 A
18 67.375 Hd 451T-CG-003 A
9 71.625 SillFp 451T-HP-037 A
18 70.875 WJambl 452-145 A
8 30.6875 Hd 450-026 B
8 32.6562 Hd 451-CG-004 B
It is data the CAD program puts out but doesn't do this necessary step.
Thanks in advance,
mike
|