VBA – Unique List of Data That Appears At Least Once in All Columns – Excel Macro
Use this VBA code to extract a unique list of items that appear inside of multi-column data. The criteria is that the data needs to appear at least once in each column. This one has user friendly interface to run on any size data and get the results wherever user picks in the worksheet.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
Sub UniqueListMatchingMultipleColumns() Dim rng As Range Dim srcrng As Range Set rng = Range("g1") Set srcrng = Application.InputBox("Please select you data (Do not include column headings!)", , , Type:=8) Set rng = Application.InputBox("Click the cell where you'd like the output to start rendering (a single cell):", , Type:=8) longstr = "||||" finalstring = "||||" For Each i In srcrng If InStr(longstr, "||||" & i.Value & "||||") = 0 Then longstr = longstr & i.Value & "||||" End If Next i For Each word In Split(Mid(longstr, 5, Len(longstr) - 8), "||||") n = 1 got = 0 For Each col In srcrng.Columns mt = Application.Match(word, srcrng.Columns(n), 0) If IsNumeric(mt) Then got = 1 Else got = 0 Exit For End If n = n + 1 Next col If got = 1 Then finalstring = finalstring & word & "||||" End If Next word r = 0 For Each finalword In Split(Mid(finalstring, 5, Len(longstr) - 8), "||||") rng.Offset(r, 0).Value = finalword r = r + 1 Next finalword End Sub |
Posted by Excel Instructor:
http://www.houstoncomputerclasses.com/excel-classes/
