unique values from multiple columns in different worksheets

[chartmunger]

Hi -- I need some help with a problem.

Each tab (sheet) is a survey, and I need to create a summary tab that automatically combines user inputs:

• Users (my colleagues) will fill out a bunch of random names under each column
• The title on Row 1 of each tab is fixed & the same
• The names below are the user inputs: there will be duplicates among the users within the same column (e.g. Mark's input under "Amazon" might have overlaps with Tim's input on his own tab), and I need to remove the duplicates for same columns only (in case there are 2 "John Doe30" under Facebook and Google)

Ideally, the result would be a summary tab combining all user inputs under same columns, also removing any duplicates

Other information:

• # of columns: <=20, but we might add or remove columns in the future
• # of rows (inputs): <=20
• # of sheets: <=20
• This sheet is a tool that we will update (asking for new user inputs) from time to time, so we want automation

Example sheet for one user (each sheet will have the exact same headers):

 

[DanteAmor]

Hi and welcome to MrExcel!

Change the "Summary" in the macro to the name of your sheet with the summary, on this line: Set sh1 = Sheets("Summary")
Also update in the macro, on this line: Case sh1.Name, "Sheet1", "Sheet2", "etc" , if you have sheets that you don't want to be considered.

Run this macro:

Sub UniqueValuesFromMultipleColumns()
  Dim sh1 As Worksheet, sh As Worksheet
  Dim i As Long, j As Long
  Dim f As Range
 
  Set sh1 = Sheets("Summary")   'Summary sheet name
  sh1.Rows("2:" & Rows.Count).ClearContents
 
  Application.ScreenUpdating = True
  For Each sh In Sheets
    Select Case sh.Name
      Case sh1.Name, "Sheet1", "Sheet2", "etc"    'Sheet names to exclude from review.
      Case Else
        For j = 1 To sh.Cells(1, Columns.Count).End(1).Column
          For i = 2 To sh.Cells(Rows.Count, j).End(3).Row
            Set f = sh1.Columns(j).Find(sh.Cells(i, j).Value, , xlValues, xlWhole, , , False)
            If f Is Nothing Then
              sh1.Cells(Rows.Count, j).End(3)(2).Value = sh.Cells(i, j).Value
            End If
          Next
        Next
    End Select
  Next
  Application.ScreenUpdating = False
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (UniqueValuesFromMultipleColumns) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
----------------------------------

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

For example:

Dante Amor
	A	B	C
1	Amazon	Google	Face
2	John Doe1	John Doe12	John Doe31
3	John Doe2	John Doe13	John Doe32
4	John Doe3	John Doe14	John Doe33
5	John Doe4	John Doe15	John Doe34
6	John Doe5	John Doe16	John Doe35
7	John Doe6	John Doe17	John Doe36
8	John Doe7	John Doe18	John Doe37
9		John Doe19
10		John Doe20
Summary

 

[chartmunger]

Hi and welcome to MrExcel!

Change the "Summary" in the macro to the name of your sheet with the summary, on this line: Set sh1 = Sheets("Summary")
Also update in the macro, on this line: Case sh1.Name, "Sheet1", "Sheet2", "etc" , if you have sheets that you don't want to be considered.

Run this macro:

Sub UniqueValuesFromMultipleColumns()
  Dim sh1 As Worksheet, sh As Worksheet
  Dim i As Long, j As Long
  Dim f As Range
 
  Set sh1 = Sheets("Summary")   'Summary sheet name
  sh1.Rows("2:" & Rows.Count).ClearContents
 
  Application.ScreenUpdating = True
  For Each sh In Sheets
    Select Case sh.Name
      Case sh1.Name, "Sheet1", "Sheet2", "etc"    'Sheet names to exclude from review.
      Case Else
        For j = 1 To sh.Cells(1, Columns.Count).End(1).Column
          For i = 2 To sh.Cells(Rows.Count, j).End(3).Row
            Set f = sh1.Columns(j).Find(sh.Cells(i, j).Value, , xlValues, xlWhole, , , False)
            If f Is Nothing Then
              sh1.Cells(Rows.Count, j).End(3)(2).Value = sh.Cells(i, j).Value
            End If
          Next
        Next
    End Select
  Next
  Application.ScreenUpdating = False
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (UniqueValuesFromMultipleColumns) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
----------------------------------

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

For example:

Dante Amor
	A	B	C
1	Amazon	Google	Face
2	John Doe1	John Doe12	John Doe31
3	John Doe2	John Doe13	John Doe32
4	John Doe3	John Doe14	John Doe33
5	John Doe4	John Doe15	John Doe34
6	John Doe5	John Doe16	John Doe35
7	John Doe6	John Doe17	John Doe36
8	John Doe7	John Doe18	John Doe37
9		John Doe19
10		John Doe20
Summary

Wow, this worked like a charm and is written beautifully. Thank you so much for doing this!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.