Displaying Number Only Once in a New List

[cardan]

Hello All,

I have a list of code numbers in a column in a master table. Some
repeat, some do not. I am trying create a new consolidated list from
the original list that will display each code that is used in the
master list only once. It would look something like this:

Master List

Code 555000
Code 555000
Code 555000
Code 111000
Code 111000
Code 222000
Code 555000
Code 333000

Into this

Code 555000
Code 111000
Code 222000
Code 333000

Is this possible? Any help would be greatly appreciated! Thank you in
advance for your time.

 

[dolivastro]

I think you need a VBA subroutine for that. I assume your master list
is in A1:A15, and you want the new list in B1:B15. Then you call the
subroutine as "Create_Unique (A1:a15, B1:B15). It looks like this
(untested)

public sub Create_unique (s a Range, e as Range)
dim i as long
dim j as long
dim w as long
dim Target as long

for i = s.Rows(1).Row to s.Rows(s.Rows.Count).Row
Target = s.Cells(i,1).Value
Status = IsUnique (Target, s, i+1)
if (Status) then
w = w + 1
e.Cells (w, 1).Value = Target
end if
next i
end sub


private function IsUnique (Target as long, s as Range, FirstRow as
long) as Boolean
dim i as long

for i = FirstRow to s.Rows(s.Rows.Count).Row
if (Target = s.Cells(i,1).Value) then
IsUnique = false
exit function
endif
next i

isUnique = true

end function


Hope this helps (and works)
Dom

 

[smw226 via OfficeKB.com]

Hi,

If you highlight your list then, Data>Filter>Advanced Filter

Check the "Unique Records" box and OK

That should take care of it.

Either that or create a pivot table on the codes, and that will have the same
effect.

HTH

Simon

Hello All,

I have a list of code numbers in a column in a master table. Some
repeat, some do not. I am trying create a new consolidated list from
the original list that will display each code that is used in the
master list only once. It would look something like this:

Master List

Code 555000
Code 555000
Code 555000
Code 111000
Code 111000
Code 222000
Code 555000
Code 333000

Into this

Code 555000
Code 111000
Code 222000
Code 333000

Is this possible? Any help would be greatly appreciated! Thank you in
advance for your time.

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200610/1