list of entries from a column without duplicates

  • Thread starter Thread starter rpick60
  • Start date Start date
R

rpick60

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
 
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,B2: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(FREQUENCY(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
Click to expand...
OW($B$2:$B$10)-ROW($B$2)+1),ROWS(E$2:E2))),"")

Hope this helps!
 
=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(INDIRECT("1:"&ROWS(rngB))),MATCH(rngB,rngB,0)),ROWS($1:1))))

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUMIF Function 2
How do I create a drop down box within a drop down box? 1
need some beginner help with LOOKUP functions 0
i have info many times duplicated and need to make a list of just. 2
Formulas that recognise data from two worksheets 1
How do I create a drop down list that fills multiple cells? 1
How can I sum data on a row from certain named columns? 1
Consolidate two Data Sets for Making a Report 1

Back
Top