Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying Number Only Once in a New List
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying Number Only Once in a New List
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 cardan wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying Number Only Once in a New List
Hi,
If you highlight your list then, DataFilterAdvanced 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 cardan wrote: 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.a...mming/200610/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displaying a % as a number | Excel Discussion (Misc queries) | |||
Displaying a list of values | Excel Discussion (Misc queries) | |||
Displaying Last Number that is in a cell | Excel Discussion (Misc queries) | |||
How to reference list using a drop down menu, displaying the list | Excel Programming | |||
displaying a list of info | Excel Programming |