Distilling a list

dcxkraze

New Member
Joined
Oct 20, 2005
Messages
12
Is there a way to boil down a list and get rid of duplicates w/o macros.

For example: Column A has 100 entries. There are only 30 unique entries with many duplicates. How could you show only the unique list in Column A (starting at row 101) or in Column B?

Is this feasible w/o macros or pivot tables?

Best,

Brandon
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

TrippyTom

Well-known Member
Joined
Nov 16, 2004
Messages
587
This is directly taken from the VBA Macros CD available on this website (I do not take the credit). It will put the new data on the 2nd sheet.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CopyingWithoutDoubled()
   <SPAN style="color:#00007F">Dim</SPAN> wks <SPAN style="color:#00007F">As</SPAN> Worksheet
   <SPAN style="color:#00007F">Dim</SPAN> rngFind <SPAN style="color:#00007F">As</SPAN> Range
   <SPAN style="color:#00007F">Dim</SPAN> intRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, intCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, intRowT <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
   <SPAN style="color:#00007F">Set</SPAN> wks = Worksheets("Sheet2")
   intRow = 1
   intCol = 1
   wks.Columns("A").ClearContents
   <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> IsEmpty(Cells(intRow, intCol))
      <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> IsEmpty(Cells(intRow, intCol))
         <SPAN style="color:#00007F">Set</SPAN> rngFind = wks.Columns(1).Find(Cells(intRow, intCol), _
            lookat:=xlWhole, LookIn:=xlValues)
         <SPAN style="color:#00007F">If</SPAN> rngFind <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
            intRowT = intRowT + 1
            wks.Cells(intRowT, 1).Value = Cells(intRow, intCol).Value
         <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
         intCol = intCol + 1
      <SPAN style="color:#00007F">Loop</SPAN>
      intRow = intRow + 1
      intCol = 1
   <SPAN style="color:#00007F">Loop</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,499
Members
412,670
Latest member
Khin Zaw Htwe
Top