PDA

View Full Version : Solved: Found data in another Column using Formula



loveguy1977
06-28-2012, 04:47 AM
Hello guys,

Column (F) has list of numbers as data.
Column (A) will be enter numbers. Sometimes, may content numbers that are listed in Column (F).

How can I let cell B1 tell me that one number, at least, from Column (F) had enter into Column (A).

Actualy, I wanna run Macro1 if column (A) don't content any Data from Column (F).
And Macro2 if one or more numbers of Column (F) where found in Column (A).

Thank you

CodeNinja
06-28-2012, 09:01 AM
loveguy1977,
I am not 100% sure of exactly what you are asking, but maybe you can adapt the following code to suit your exact needs...

This will put in column B the number of occurrences column F has of the corresponding cell A.

Sub test()
Dim i As Integer
Dim rng As Range
Dim rngFoundRange As Range
Dim rngFirstRange As Range
Dim iCountFound As Integer

Set rng = Sheet1.Range("F:F")

For i = 1 To Sheet1.Range("A65536").End(xlUp).Row
iCountFound = 0
Set rngFoundRange = rng.Find(what:=Sheet1.Cells(i, 1), after:=Sheet1.Range("F65536").End(xlUp), lookat:=xlWhole)
If Not rngFoundRange Is Nothing Then
Set rngFirstRange = rngFoundRange

Do
Set rngFoundRange = rng.Find(what:=Sheet1.Cells(i, 1), after:=rngFoundRange, lookat:=xlWhole)
iCountFound = iCountFound + 1
Loop Until rngFoundRange.Address = rngFirstRange.Address
End If
Sheet1.Cells(i, 2) = iCountFound
Next i
End Sub

loveguy1977
06-28-2012, 09:34 AM
Thank you but I wish it as formula if possible please
I need an indicator only in Cell B1 if Column (A) content any number from Column (F)

CatDaddy
06-28-2012, 09:48 AM
Sub test()
Dim MyNums() As Variant
Dim cell As Range
Dim x As Long

MyNums = Application.WorksheetFunction.Transpose(Range("F1:F" & Range("F" & Rows.Count).End(xlUp).Row))
For Each cell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)

On Error Resume Next
x = WorksheetFunction.Match(cell.Value, MyNums, False)
If Err = 0 Then
cell.Offset(0, 1).Value = "MATCH"
Else
Err.Clear
End If
On Error GoTo 0

Next cell
End Sub

CodeNinja
06-28-2012, 12:24 PM
Again with the Array formulas... I am not the expert on array formulas, but you can learn a lot of information on them here:

http://www.cpearson.com/excel/ArrayFormulas.aspx

So, go to the cell you want the data, and paste this formula in it:

=SUM(IF(COUNTIF(A1:A3,F1:F3)>0,1,FALSE))

Of course change A1:A3,F1,F3 to your actual range...

Then you need to go back into the formula (press F2) and hold Control, Shift and press Enter, then let go of all 3. A french (?) bracket {} will appear around the formula, and it should work for you. The formula will not work without the Control Shift and Enter.

This will tell you how many times column F repeats something in column A, not the other way around...

You can play with the and/or operators of */+ to figure out how to get all options, I think it might be something like:
=SUM(IF((COUNTIF(A1:A3,F1:F3)+COUNTIF(F1:F3,A1:A3))>0,1,FALSE))
To give you a count of items in A that are in F + items in F that are in A, but some of the nuances of array formulas escape me as of yet.

Another option is:
=SUM(IF(COUNTIF(A1:A3,F1:F3)>0,COUNTIF(A1:A3,F1:F3),FALSE))
To get all multiples of re-occurrence...

Good luck.

loveguy1977
06-28-2012, 12:29 PM
Thank you

That is what I wanna. Thanks again


Again with the Array formulas... I am not the expert on array formulas, but you can learn a lot of information on them here:

http://www.cpearson.com/excel/ArrayFormulas.aspx

So, go to the cell you want the data, and paste this formula in it:

=SUM(IF(COUNTIF(A1:A3,F1:F3)>0,1,FALSE))

Of course change A1:A3,F1,F3 to your actual range...

Then you need to go back into the formula (press F2) and hold Control, Shift and press Enter, then let go of all 3. A french (?) bracket {} will appear around the formula, and it should work for you. The formula will not work without the Control Shift and Enter.

This will tell you how many times column F repeats something in column A, not the other way around...

You can play with the and/or operators of */+ to figure out how to get all options, I think it might be something like:
=SUM(IF((COUNTIF(A1:A3,F1:F3)+COUNTIF(F1:F3,A1:A3))>0,1,FALSE))
To give you a count of items in A that are in F + items in F that are in A, but some of the nuances of array formulas escape me as of yet.

Another option is:
=SUM(IF(COUNTIF(A1:A3,F1:F3)>0,COUNTIF(A1:A3,F1:F3),FALSE))
To get all multiples of re-occurrence...

Good luck.

CodeNinja
06-28-2012, 12:32 PM
Wow, I really complicated that last formula... got stuck in if/countif... simpler way of doing the final one would be

=SUM(COUNTIF(A1:A3,B1:B3))

Glad to be of help...

If you are satisfied that this answers your questions, please mark the thread as solved.