I have a list of materials in column B that may have the same material
listed several times, I would like to create a list of all materials
used in another column without duplicating the same material. I have
tried lists but I can get one material at a time or all of them.
Can anyone help with just a single list of material types?

Insert a new worksheet and copy your list from column B of the first
sheet into column A of the new sheet. If you do not have a header then
insert a new row at the top of your data in the new sheet and put
"Material" in A1.

Then highlight all the data and the header from A1 down and click on
Data | Filter | Advanced Filter. In the pop-up you should check Unique
Records only and Copy to another location. Put C1 in the destination
box and click OK.

You will now have a unique list in column C - you can delete columns A
and B.

Hope this helps.

Pete

On Dec 9, 8:18 pm, rpick60 wrote:
I have a list of materials in column B that may have the same material
listed several times, I would like to create a list of all materials
used in another column without duplicating the same material. I have
tried lists but I can get one material at a time or all of them.
Can anyone help with just a single list of material types?

Advanced Filter can be used to generate a list of unique values. Select
'Unique records only'. Otherwise, by formula...

D2:

=SUM(IF(FREQUENCY(IF(B2:B10<"",MATCH("~"&B2:B10,B 2:B10&"",0)),ROW(B2:B10
)-ROW(B2)+1),1))

E2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(E$2:E2)<=$D$2,INDEX($B$2:$B$10,SMALL(IF(F REQUENCY(IF($B$2:$B$10<
"",MATCH("~"&$B$2:$B$10,$B$2:$B$10&"",0)),ROW($B$ 2:$B$10)-ROW($B$2)+1),R
OW($B$2:$B$10)-ROW($B$2)+1),ROWS(E$2:E2))),"")

Hope this helps!

In article
,
rpick60 wrote:

I have a list of materials in column B that may have the same material
listed several times, I would like to create a list of all materials
used in another column without duplicating the same material. I have
tried lists but I can get one material at a time or all of them.
Can anyone help with just a single list of material types?

See Debra Dalgleish's site for copying Unique Values to another sheet.

http://www.contextures.on.ca/xladvfi....html#FilterUR


Gord Dibben MS Excel MVP

On Sun, 9 Dec 2007 12:18:39 -0800 (PST), rpick60 wrote:

I have a list of materials in column B that may have the same material
listed several times, I would like to create a list of all materials
used in another column without duplicating the same material. I have
tried lists but I can get one material at a time or all of them.
Can anyone help with just a single list of material types?

On Dec 9, 3:58 pm, Gord Dibben <gorddibbATshawDOTca wrote:
See Debra Dalgleish's site for copying Unique Values to another sheet.

http://www.contextures.on.ca/xladvfi....html#FilterUR

Gord Dibben MS Excel MVP



On Sun, 9 Dec 2007 12:18:39 -0800 (PST), rpick60 wrote:
I have a list of materials in column B that may have the same material
listed several times, I would like to create a list of all materials
used in another column without duplicating the same material. I have
tried lists but I can get one material at a time or all of them.
Can anyone help with just a single list of material types?- Hide quoted text -

- Show quoted text -

Thaks for the quick reponse I will give it a try.

=IF(ISERR(SMALL(IF(MATCH(rngB,rngB,0)=ROW(INDIRECT ("1:"&ROWS(rngB))),MATCH(rngB,rngB,0)),ROWS($1:1)) ),"",INDEX(rngB,SMALL(IF(MATCH(rngB,rngB,0)=ROW(IN DIRECT("1:"&ROWS(rngB))),MATCH(rngB,rngB,0)),ROWS( $1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed


"rpick60" wrote:

I have a list of materials in column B that may have the same material
listed several times, I would like to create a list of all materials
used in another column without duplicating the same material. I have
tried lists but I can get one material at a time or all of them.
Can anyone help with just a single list of material types?