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

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" 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

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

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

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