help with lookup?

[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

[Bryan Hessey]

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
---

[Bryan Hessey]

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

---

[grizrowe]

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

grizrowe

[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
---

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

[Bryan Hessey]

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
---

[Bryan Hessey]

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

---