View Full Version : Output common and unique values from two rows
compariniaa
07-21-2006, 09:49 AM
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
07-21-2006, 10:42 AM
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
07-21-2006, 10:49 AM
i'll give that a try...thanks
compariniaa
07-21-2006, 12:13 PM
it worked, but it seems like a lot for a seemingly simple step. is there an easier way to do it?
mdmackillop
08-24-2006, 12:36 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.