Ext5ract commn number from lists

[Brian]

I have a workbook in Excel 2003 with 6 worksheets named List1, List2,
List3, List4, List5 and List6
Each worksheet has a list of numbers in column A from A3 and all the
numbers in each list are unique numbers in that list. A2 is the column
header. The list can vary in length from 10 to 1,000 numbers. The
numbers are not in any order
What I want to do on Sheet 7 is to extract to Column C the numbers
which are common to all the lists. – without blank rows in the list on
sheet 7. The numbers can be in any order.
On sheet 7 in column A1 to A6 I have the list of the sheet names and
in column B next to the sheet name I have “True” or “False”
If “False” is next to any of these sheet names then the list of
numbers on that sheet must not be considered when drawing out the
numbers common to each list.
I know how to use a formula for this when applying it to 2 lists but I
think I need a VB solution for this. Can anyone help.

 

[Don Guillett]

Or

Cells(ActiveCell.Row, Selection(Selection.Count).Column + 2) = "X"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
I have a workbook in Excel 2003 with 6 worksheets named List1, List2,
List3, List4, List5 and List6
Each worksheet has a list of numbers in column A from A3 and all the
numbers in each list are unique numbers in that list. A2 is the column
header. The list can vary in length from 10 to 1,000 numbers. The
numbers are not in any order
What I want to do on Sheet 7 is to extract to Column C the numbers
which are common to all the lists. – without blank rows in the list on
sheet 7. The numbers can be in any order.
On sheet 7 in column A1 to A6 I have the list of the sheet names and
in column B next to the sheet name I have “True” or “False”
If “False” is next to any of these sheet names then the list of
numbers on that sheet must not be considered when drawing out the
numbers common to each list.
I know how to use a formula for this when applying it to 2 lists but I
think I need a VB solution for this. Can anyone help.

 

[Don Guillett]

wrong post

 

[Don Guillett]

Does this idea help?

Sub ifnum6()
j = 1
For Each c In Sheets("List1").Range("a2:a5")
sc = Sheets.Count - 1
mc = 0
On Error Resume Next
For i = 1 To sc
If Not Sheets(i).Columns(1).Find(c, lookat:=xlWhole) Is Nothing Then mc = mc
+ 1
Next i
If mc >= sc - 1 Then
Sheets("Sheet7").Cells(j, 1).Value = c
j = j + 1
End If
Next c
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
I have a workbook in Excel 2003 with 6 worksheets named List1, List2,
List3, List4, List5 and List6
Each worksheet has a list of numbers in column A from A3 and all the
numbers in each list are unique numbers in that list. A2 is the column
header. The list can vary in length from 10 to 1,000 numbers. The
numbers are not in any order
What I want to do on Sheet 7 is to extract to Column C the numbers
which are common to all the lists. – without blank rows in the list on
sheet 7. The numbers can be in any order.
On sheet 7 in column A1 to A6 I have the list of the sheet names and
in column B next to the sheet name I have “True” or “False”
If “False” is next to any of these sheet names then the list of
numbers on that sheet must not be considered when drawing out the
numbers common to each list.
I know how to use a formula for this when applying it to 2 lists but I
think I need a VB solution for this. Can anyone help.