I have a spreadsheet with over 1500 rows and 20 columns, in column C I have manufacturer as my header and below it 1500 listed names, one for each row. How do I extract the names into a single listing of just the 60-70 specific vendors, is there an easy way to set this up so that I can update my listing as I add another 500-1000 rows of data to my spreadsheet over the next few weeks?
Assume that column C holds the data with some duplicated records and we want the unique list (list without duplicates) in column Z. Enter the following code in the worksheet code area:
Private Sub Worksheet_Change(ByVal Target As Range) Dim C As Range, Z As Range Set C = Range("C:C") Set Z = Range("Z:Z") If Intersect(Target, C) Is Nothing Then Exit Sub Application.EnableEvents = False Z.Clear C.Copy Z Z.RemoveDuplicates Columns:=1, Header:=xlNo Application.EnableEvents = True End Sub
whenever changes are made to column C, the list in column Z will be re-created. Because it is worksheet code, it is very easy to install and automatic to use:
- right-click the tab name near the bottom of the Excel window
- select View Code - this brings up a VBE window
- paste the stuff in and close the VBE window
If you have any concerns, first try it on a trial worksheet.
If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx
To remove the macro:
- bring up the VBE windows as above
- clear the code out
- close the VBE window
To learn more about macros in general, see:
To learn more about Event Macros (worksheet code), see:
Macros must be enabled for this to work!
Learn to use pivot tables. Then you can do this sort of thing (and much much more) in your sleep!
Drag the vendor column to the row labels and it will automatically condense the list into the distinct values for you.
- Select all your data in manufacturer column.
- Go to Data > Data Tools section > Remove duplicates.
It will delete your duplicated data. You will get unique manufacturers. I recommend to work on a clean spreadsheet. Copy only manufacturer column and work.