sum column a if column b, c, d, and e are equal

[jj]

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

 

[ShaneDevenshire]

Hi,

You can use the Data, Filter, Advanced Filter, Unique records only option.
First put one row of titles at the top of each column. Only highlight
columns B:E for this source range and use the Filter in place option. You
can copy the results with column A to a new location.

Cheers,
Shane Devenshire

 

[jj]

I'm missing something here, did what you said ( filter in place and unique)
but when it comes to column A filter I select only column A and unique only?
then copy to another column it just repeats. not summing quantity deleting
redundacies

 

[bj]

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]

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)


(Me) YOU'VE DONE IT!!! AWESOME!!!


Kewl I can't Thank You enuf...
A few little tweaks and it worked!
this is how it worked for me.....


one method
use two helper columns (E,F?).......(changed because of there was a row "e")
in F1
=A1
in F2
=if(and(B1=B2,C1=C2,D1=D2,E1=E2),F1+A1,A2)<-----
in G1
=if(and(B1=B2,C1=C2,D1=D2,E1=E2),"",1)


copy F2 and paste to the end of your data

copy G1 and paste to the end of your data

select columns F and G
copy and paste special values
select column G and filter-autofilter
select blanks
select the visable cells and edit-delete rows (execpt the first row)
remove autofilter
select column F copy ans select Cell a1
paste spectial values


Wow, this crash course in EXCEL is still making my head spin.
many thanx,
mike