help with lookup?

Originally Posted by grizrowe

Hi everyone,
I need help coming up with two formulas...Ive tried and tried, searched the web for help, but still can't figure out how to do it. I've come here for help!!!

Here my dilemma:
A B C D E F G H
1 9-6-1996 5 11 29 47 50 17 142
2 9-13-1996 3 4 9 30 47 1 93
3 9-20-1996 5 24 31 34 48 6 142
4 9-27-1996 8 25 35 37 48 8 153
5 10-4-1996 8 16 18 36 38 1 116

I am trying to come up with a formula which will
lookup or find all instances of a number in column B
say for example number 5 and return the contents of
each row with a number 5 in column B. But also, at
the same time must have a certian number in
column H, say for example 142. So in a nutshell
look for all occurences of number 5 in column B, and
the number 142 in column H (same row) and return
the contents of both of those rows... make sense?

Second, I'm trying to come up with a formula which will
give all different variations of a number using only numbers
1 to 56.. so, if given a number, for example 104, I need
to know how many 5 number combinations (using numbers
1-56 only) could be used to add up to 104..

I would greatly appreciate the forums help and expertise with
coming up with these two formulas.. I just don't have the
experience, expertise or brains enough to do it.

grizrowe

Hi,

the first question should be with

=SUMPRODUCT(--(B$1:B$5=5),--(H$1:H$5=142))

adjust the range 1:5 to suit the real data


more follows for the 1-56

hth
---

Originally Posted by grizrowe

Thanks Bryan ,
this formula works but not exactly what I was looking for. This formula returns only how times the instances occured. ie.. only 2 times in the example in my original post.

I was hoping for a formula that would return the contents of the range of
cells which match the criteria:
5 11 29 47 50
5 24 31 34 48

griz

Hi,

To get a list of items that match specific (multiple) criteria,

Select the columns (or select AllData)
Data, Filter and tick AutoFilter

use the dropdown for column B and select 5
use the dropdown for column H and select 142

hth
---

Originally Posted by grizrowe

Hi everyone,
I need help coming up with two formulas...Ive tried and tried, searched the web for help, but still can't figure out how to do it. I've come here for help!!!

Here my dilemma:
A B C D E F G H
1 9-6-1996 5 11 29 47 50 17 142
2 9-13-1996 3 4 9 30 47 1 93
3 9-20-1996 5 24 31 34 48 6 142
4 9-27-1996 8 25 35 37 48 8 153
5 10-4-1996 8 16 18 36 38 1 116

I am trying to come up with a formula which will
lookup or find all instances of a number in column B
say for example number 5 and return the contents of
each row with a number 5 in column B. But also, at
the same time must have a certian number in
column H, say for example 142. So in a nutshell
look for all occurences of number 5 in column B, and
the number 142 in column H (same row) and return
the contents of both of those rows... make sense?

Second, I'm trying to come up with a formula which will
give all different variations of a number using only numbers
1 to 56.. so, if given a number, for example 104, I need
to know how many 5 number combinations (using numbers
1-56 only) could be used to add up to 104..

I would greatly appreciate the forums help and expertise with
coming up with these two formulas.. I just don't have the
experience, expertise or brains enough to do it.

grizrowe

If I have this correctly, put 56 in A1, 104 in B1 and run this (macro) for a reply of 3529450

Sub count()
Dim Ctr1 As Long, Ctr2 As Long, Ctr3 As Long, Ctr4 As Long, Ctr5 As Long
Dim myCount As Long, countToWhat As Long, CountMany As Long
myCount = Range("A1").Value
countToWhat = Range("B1").Value
For Ctr1 = 1 To myCount
    For Ctr2 = 1 To myCount
        For Ctr3 = 1 To myCount
            For Ctr4 = 1 To myCount
                For Ctr5 = 1 To myCount
                    If Ctr1 + Ctr2 + Ctr3 + Ctr4 + Ctr5 = countToWhat Then
                        CountMany = CountMany + 1
                        End If
                    Next
                Next
            Next
        Next
    Next
MsgBox "for " & myCount & " " & countToWhat & " the count was " & CountMany
End Sub

note, give it a minute or two to run.

hth
---
added, and if you want to know the first 65530 of these combinations that total 104 then

Sub count()
Dim Ctr1 As Long, Ctr2 As Long, Ctr3 As Long, Ctr4 As Long, Ctr5 As Long
Dim myCount As Long, countToWhat As Long, CountMany As Long, iRow As Long
iRow = 3
myCount = Range("A1").Value
countToWhat = Range("B1").Value
For Ctr1 = 1 To myCount
    For Ctr2 = 1 To myCount
        For Ctr3 = 1 To myCount
            For Ctr4 = 1 To myCount
                For Ctr5 = 1 To myCount
                    If Ctr1 + Ctr2 + Ctr3 + Ctr4 + Ctr5 = countToWhat Then
                        CountMany = CountMany + 1
                        If iRow < 65530 Then
                            Cells(iRow, 5) = Ctr1
                            Cells(iRow, 6) = Ctr2
                            Cells(iRow, 7) = Ctr3
                            Cells(iRow, 8) = Ctr4
                            Cells(iRow, 9) = Ctr5
                            iRow = iRow + 1
                            End If
                        End If
                    Next
                Next
            Next
        Next
    Next
MsgBox "for " & myCount & " " & countToWhat & " the count was " & CountMany
End Sub

---

Originally Posted by grizrowe

Thanks for the replies... at the risk of sounding stupid.. how do I run the macro? and where?....

grizrowe

In Tools, Macro, Macros
enter a name and Create.

copy the code to the window given

close the Visual Basic editor, and Tools, Macro, Macros select the macro and Run

You can use either macro, the 'show the possibles' or the 'just calculate' version.

hth

---