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. |
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. |
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 |
All times are GMT +1. The time now is 09:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com