Output common and unique values from two rows

[compariniaa]

I've looked around a lot for this and I've tried figuring it out myself, but I'm at a loss for efficient ideas. here's my problem:

I have two different worksheets showing the same types of data for two different months (i.e. May data on revenue, products, dates, etc on one and June data on revenue, products, dates, etc on the other)

I'd like to populate a listbox with certain values shared between these two sheets

Is there any efficient/semi-efficient way to do this? If so, could I get some direction?

Thanks for taking a look

[lenze]

If you place all your data on 1 sheet, you can use a Pivot Table, grouping on your Code(Date) column. I think you can get your list from the table, though I'm not sure.

lenze

[compariniaa]

i'll give that a try...thanks

[compariniaa]

it worked, but it seems like a lot for a seemingly simple step. is there an easier way to do it?

[mdmackillop]

I'm having trouble with the Match function, but I think this (or a variation) is the way to go.

Private Sub UserForm_Initialize()
SharedItems
End Sub

Sub SharedItems()
Dim Arr1, Arr2, a, Fnd As Long, i As Long
Dim d, f

On Error GoTo errH
Set d = CreateObject("Scripting.Dictionary")
'Use function to return range
Arr1 = Rng(Sheets(1).[B2], 1).Value
Arr2 = Rng(Sheets(2).[B2], 1).Value
'Check for each array member in second array
'Use dictionary object to remove duplicates
For Each a In Arr1
On Error Resume Next
Fnd = Application.WorksheetFunction.Match(a, Arr2, 0)
If Fnd > 0 Then d.Add a, a
Fnd = 0
Next
errH:
f = d.Items
For i = 0 To d.Count - 1
Me.ComboBox1.AddItem f(i)
Next
End Sub

Function Rng(Cel As Range, Typ As Long) As Range
Select Case Typ
Case 1
Set Rng = Range(Cel, Cel.End(xlDown))
Case 2
Set Rng = Range(Cel, Cells(Rows.Count, Cel.Column).End(xlUp))
Case 3
Set Rng = Range(Cel, Cel.End(xlToRight))
Case 4
Set Rng = Range(Cel, Cells(Cel.Row, Columns.Count).End(xlToLeft))
End Select
End Function

[OBP]

Access? :hi: