0

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?

  • It is unclear what you want. Do you want a list of each unique manufacturer, or do you want to create a filter that only shows those lines from a select group of manufacturers? – Scott Craner Apr 20 '16 at 20:23
  • I want to list each unique manufacturer. – Steve Apr 20 '16 at 20:35
  • But I would like to see this list update regularly as we build from 1500 rows to 3000 rows over the next few weeks. I know I have 170 unique manufacturers today, I just want to build a solid process for tracking over time. Thanks. – Steve Apr 20 '16 at 20:36
  • Then the best bet would be vba. – Scott Craner Apr 20 '16 at 20:42
  • I've never used it, so I guess start with that learning process first, you would suggest a ground up approach? – Steve Apr 20 '16 at 20:51
1

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:

  1. right-click the tab name near the bottom of the Excel window
  2. select View Code - this brings up a VBE window
  3. 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:

  1. bring up the VBE windows as above
  2. clear the code out
  3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

0

Learn to use pivot tables. Then you can do this sort of thing (and much much more) in your sleep!

https://support.office.com/en-us/article/Create-a-PivotTable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576

Drag the vendor column to the row labels and it will automatically condense the list into the distinct values for you.

0
  1. Select all your data in manufacturer column.
  2. 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.