Hi all! I desparately need help with an Excel problem. Here's an
example of what I'm trying to achive below:

In Cell B2 I have a drop down list which contains names of our
brokers.
In Cell C2 I have a drop down list describing types of deals (values
are C, A, X, or T)


Once the user chooses the name and type of deal, they would press a
button which would then take the values in B2 and C2 together and
find
matches in another sheet which contains 3 columns worth of data:
Name,
Type of Deal, and Deal Number (alphanumeric). There will never be 0
deal numbers or
more than 8 answers for any given Criteria (e.g. If I select Tom and
C, the resulting answer will have between 1 and 8 different Deal
Numbers.)


Once Excel or VBA finds these deal numbers, they need to be "placed"
or copied into cells B17 all the way down to B24 (if there are 8
deals
numbers.) So, if there are 2 deal numbers corresponding to Tom and C,
Excel will copy both deal numbers and paste them into B17 and B18.


I'm not sure if VLOOKUP or HLOOKUP or INDEX - MATCH function can do
this but if possible, please let me know.


Thank you in advance for your help!

How is the data on the other sheet setup?

Is it like this:

Tom...C...10
Tom...C...20
Tom...C...52
Tom...X...22
Tom...A...10
Tom...A...37

Or, is it random:

Tom...C...10
Bill...X...22
Sue...A...17
Bob...C...20
Tom...C...15

Biff

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi all! I desparately need help with an Excel problem. Here's an
> example of what I'm trying to achive below:
>
> In Cell B2 I have a drop down list which contains names of our
> brokers.
> In Cell C2 I have a drop down list describing types of deals (values
> are C, A, X, or T)
>
>
> Once the user chooses the name and type of deal, they would press a
> button which would then take the values in B2 and C2 together and
> find
> matches in another sheet which contains 3 columns worth of data:
> Name,
> Type of Deal, and Deal Number (alphanumeric). There will never be 0
> deal numbers or
> more than 8 answers for any given Criteria (e.g. If I select Tom and
> C, the resulting answer will have between 1 and 8 different Deal
> Numbers.)
>
>
> Once Excel or VBA finds these deal numbers, they need to be "placed"
> or copied into cells B17 all the way down to B24 (if there are 8
> deals
> numbers.) So, if there are 2 deal numbers corresponding to Tom and C,
> Excel will copy both deal numbers and paste them into B17 and B18.
>
>
> I'm not sure if VLOOKUP or HLOOKUP or INDEX - MATCH function can do
> this but if possible, please let me know.
>
>
> Thank you in advance for your help!
>

Create a macro button or a code that activates when you change the drop down
list and insert the following code.

Sub Haaas()

Range("B3").Activate

Do Until ActiveCell.Value = ""

ActiveCell.Copy
ActiveCell.Offset(14, 0).PasteSpecial
ActiveCell.Offset(-13, 0).Activate

Loop

End Sub

"(E-Mail Removed)" wrote:

> Hi all! I desparately need help with an Excel problem. Here's an
> example of what I'm trying to achive below:
>
> In Cell B2 I have a drop down list which contains names of our
> brokers.
> In Cell C2 I have a drop down list describing types of deals (values
> are C, A, X, or T)
>
>
> Once the user chooses the name and type of deal, they would press a
> button which would then take the values in B2 and C2 together and
> find
> matches in another sheet which contains 3 columns worth of data:
> Name,
> Type of Deal, and Deal Number (alphanumeric). There will never be 0
> deal numbers or
> more than 8 answers for any given Criteria (e.g. If I select Tom and
> C, the resulting answer will have between 1 and 8 different Deal
> Numbers.)
>
>
> Once Excel or VBA finds these deal numbers, they need to be "placed"
> or copied into cells B17 all the way down to B24 (if there are 8
> deals
> numbers.) So, if there are 2 deal numbers corresponding to Tom and C,
> Excel will copy both deal numbers and paste them into B17 and B18.
>
>
> I'm not sure if VLOOKUP or HLOOKUP or INDEX - MATCH function can do
> this but if possible, please let me know.
>
>
> Thank you in advance for your help!
>
>

On May 12, 7:37 pm, AKphidelt <AKphid...@discussions.microsoft.com>
wrote:
> Create a macro button or a code that activates when you change the drop down
> list and insert the following code.
>
> Sub Haaas()
>
> Range("B3").Activate
>
> Do Until ActiveCell.Value = ""
>
> ActiveCell.Copy
> ActiveCell.Offset(14, 0).PasteSpecial
> ActiveCell.Offset(-13, 0).Activate
>
> Loop
>
> End Sub
>
>
>
> "haas...@yahoo.com" wrote:
> > Hi all! I desparately need help with an Excel problem. Here's an
> > example of what I'm trying to achive below:
>
> > In Cell B2 I have a drop down list which contains names of our
> > brokers.
> > In Cell C2 I have a drop down list describing types of deals (values
> > are C, A, X, or T)
>
> > Once the user chooses the name and type of deal, they would press a
> > button which would then take the values in B2 and C2 together and
> > find
> > matches in another sheet which contains 3 columns worth of data:
> > Name,
> > Type of Deal, and Deal Number (alphanumeric). There will never be 0
> > deal numbers or
> > more than 8 answers for any given Criteria (e.g. If I select Tom and
> > C, the resulting answer will have between 1 and 8 different Deal
> > Numbers.)
>
> > Once Excel or VBA finds these deal numbers, they need to be "placed"
> > or copied into cells B17 all the way down to B24 (if there are 8
> > deals
> > numbers.) So, if there are 2 deal numbers corresponding to Tom and C,
> > Excel will copy both deal numbers and paste them into B17 and B18.
>
> > I'm not sure if VLOOKUP or HLOOKUP or INDEX - MATCH function can do
> > this but if possible, please let me know.
>
> > Thank you in advance for your help!- Hide quoted text -
>
> - Show quoted text -

Hey AKphidelt ,

I don't know what angle you're taking and thanks for your help, but I
have no idea what you're trying to achieve with that code. Thanks for
trying though!

On May 12, 6:46 pm, "T. Valko" <biffinp...@comcast.net> wrote:
> How is the data on the other sheet setup?
>
> Is it like this:
>
> Tom...C...10
> Tom...C...20
> Tom...C...52
> Tom...X...22
> Tom...A...10
> Tom...A...37
>
> Or, is it random:
>
> Tom...C...10
> Bill...X...22
> Sue...A...17
> Bob...C...20
> Tom...C...15
>
> Biff
>
> <haas...@yahoo.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > Hi all! I desparately need help with an Excel problem. Here's an
> > example of what I'm trying to achive below:
>
> > In Cell B2 I have a drop down list which contains names of our
> > brokers.
> > In Cell C2 I have a drop down list describing types of deals (values
> > are C, A, X, or T)
>
> > Once the user chooses the name and type of deal, they would press a
> > button which would then take the values in B2 and C2 together and
> > find
> > matches in another sheet which contains 3 columns worth of data:
> > Name,
> > Type of Deal, and Deal Number (alphanumeric). There will never be 0
> > deal numbers or
> > more than 8 answers for any given Criteria (e.g. If I select Tom and
> > C, the resulting answer will have between 1 and 8 different Deal
> > Numbers.)
>
> > Once Excel or VBA finds these deal numbers, they need to be "placed"
> > or copied into cells B17 all the way down to B24 (if there are 8
> > deals
> > numbers.) So, if there are 2 deal numbers corresponding to Tom and C,
> > Excel will copy both deal numbers and paste them into B17 and B18.
>
> > I'm not sure if VLOOKUP or HLOOKUP or INDEX - MATCH function can do
> > this but if possible, please let me know.
>
> > Thank you in advance for your help!- Hide quoted text -
>
> - Show quoted text -

Biff,

The data is random, similar to your 2nd example, and the last column
contains alphanumeric entries instead of just pure numbers.

Thanks...

Here's a small sample file that demonstrates this:

Haas(1).xls 16kb

http://cjoint.com/?fngLRAk3eZ

The formulas used are array** formulas. A single formula was entered in cell
B17 then drag copied down to B24.

Try making some selections from the drop downs and see what happens (note:
the sample data does not cover every possibility. I used just enough data
for demonstration purposes)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Note: if you have 1000's of rows of data then this might not be the best
approach.

Biff

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On May 12, 6:46 pm, "T. Valko" <biffinp...@comcast.net> wrote:
>> How is the data on the other sheet setup?
>>
>> Is it like this:
>>
>> Tom...C...10
>> Tom...C...20
>> Tom...C...52
>> Tom...X...22
>> Tom...A...10
>> Tom...A...37
>>
>> Or, is it random:
>>
>> Tom...C...10
>> Bill...X...22
>> Sue...A...17
>> Bob...C...20
>> Tom...C...15
>>
>> Biff
>>
>> <haas...@yahoo.com> wrote in message
>>
>> news:(E-Mail Removed)...
>>
>>
>>
>> > Hi all! I desparately need help with an Excel problem. Here's an
>> > example of what I'm trying to achive below:
>>
>> > In Cell B2 I have a drop down list which contains names of our
>> > brokers.
>> > In Cell C2 I have a drop down list describing types of deals (values
>> > are C, A, X, or T)
>>
>> > Once the user chooses the name and type of deal, they would press a
>> > button which would then take the values in B2 and C2 together and
>> > find
>> > matches in another sheet which contains 3 columns worth of data:
>> > Name,
>> > Type of Deal, and Deal Number (alphanumeric). There will never be 0
>> > deal numbers or
>> > more than 8 answers for any given Criteria (e.g. If I select Tom and
>> > C, the resulting answer will have between 1 and 8 different Deal
>> > Numbers.)
>>
>> > Once Excel or VBA finds these deal numbers, they need to be "placed"
>> > or copied into cells B17 all the way down to B24 (if there are 8
>> > deals
>> > numbers.) So, if there are 2 deal numbers corresponding to Tom and C,
>> > Excel will copy both deal numbers and paste them into B17 and B18.
>>
>> > I'm not sure if VLOOKUP or HLOOKUP or INDEX - MATCH function can do
>> > this but if possible, please let me know.
>>
>> > Thank you in advance for your help!- Hide quoted text -
>>
>> - Show quoted text -
>
> Biff,
>
> The data is random, similar to your 2nd example, and the last column
> contains alphanumeric entries instead of just pure numbers.
>
> Thanks...
>

On May 13, 12:41 am, "T. Valko" <biffinp...@comcast.net> wrote:
> Here's a small sample file that demonstrates this:
>
> Haas(1).xls 16kb
>
> http://cjoint.com/?fngLRAk3eZ
>
> The formulas used are array** formulas. A single formula was entered in cell
> B17 then drag copied down to B24.
>
> Try making some selections from the drop downs and see what happens (note:
> the sample data does not cover every possibility. I used just enough data
> for demonstration purposes)
>
> ** array formulas need to be entered using the key combination of
> CTRL,SHIFT,ENTER (not just ENTER)
>
> Note: if you have 1000's of rows of data then this might not be the best
> approach.
>
> Biff
>
> <haas...@yahoo.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > On May 12, 6:46 pm, "T. Valko" <biffinp...@comcast.net> wrote:
> >> How is the data on the other sheet setup?
>
> >> Is it like this:
>
> >> Tom...C...10
> >> Tom...C...20
> >> Tom...C...52
> >> Tom...X...22
> >> Tom...A...10
> >> Tom...A...37
>
> >> Or, is it random:
>
> >> Tom...C...10
> >> Bill...X...22
> >> Sue...A...17
> >> Bob...C...20
> >> Tom...C...15
>
> >> Biff
>
> >> <haas...@yahoo.com> wrote in message
>
> >>news:(E-Mail Removed)...
>
> >> > Hi all! I desparately need help with an Excel problem. Here's an
> >> > example of what I'm trying to achive below:
>
> >> > In Cell B2 I have a drop down list which contains names of our
> >> > brokers.
> >> > In Cell C2 I have a drop down list describing types of deals (values
> >> > are C, A, X, or T)
>
> >> > Once the user chooses the name and type of deal, they would press a
> >> > button which would then take the values in B2 and C2 together and
> >> > find
> >> > matches in another sheet which contains 3 columns worth of data:
> >> > Name,
> >> > Type of Deal, and Deal Number (alphanumeric). There will never be 0
> >> > deal numbers or
> >> > more than 8 answers for any given Criteria (e.g. If I select Tom and
> >> > C, the resulting answer will have between 1 and 8 different Deal
> >> > Numbers.)
>
> >> > Once Excel or VBA finds these deal numbers, they need to be "placed"
> >> > or copied into cells B17 all the way down to B24 (if there are 8
> >> > deals
> >> > numbers.) So, if there are 2 deal numbers corresponding to Tom and C,
> >> > Excel will copy both deal numbers and paste them into B17 and B18.
>
> >> > I'm not sure if VLOOKUP or HLOOKUP or INDEX - MATCH function can do
> >> > this but if possible, please let me know.
>
> >> > Thank you in advance for your help!- Hide quoted text -
>
> >> - Show quoted text -
>
> > Biff,
>
> > The data is random, similar to your 2nd example, and the last column
> > contains alphanumeric entries instead of just pure numbers.
>
> > Thanks...- Hide quoted text -
>
> - Show quoted text -

Thanks a million - this did the trick! God bless you!

You're welcome. Thanks for the feedback!

Biff

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On May 13, 12:41 am, "T. Valko" <biffinp...@comcast.net> wrote:
>> Here's a small sample file that demonstrates this:
>>
>> Haas(1).xls 16kb
>>
>> http://cjoint.com/?fngLRAk3eZ
>>
>> The formulas used are array** formulas. A single formula was entered in
>> cell
>> B17 then drag copied down to B24.
>>
>> Try making some selections from the drop downs and see what happens
>> (note:
>> the sample data does not cover every possibility. I used just enough data
>> for demonstration purposes)
>>
>> ** array formulas need to be entered using the key combination of
>> CTRL,SHIFT,ENTER (not just ENTER)
>>
>> Note: if you have 1000's of rows of data then this might not be the best
>> approach.
>>
>> Biff
>>
>> <haas...@yahoo.com> wrote in message
>>
>> news:(E-Mail Removed)...
>>
>>
>>
>> > On May 12, 6:46 pm, "T. Valko" <biffinp...@comcast.net> wrote:
>> >> How is the data on the other sheet setup?
>>
>> >> Is it like this:
>>
>> >> Tom...C...10
>> >> Tom...C...20
>> >> Tom...C...52
>> >> Tom...X...22
>> >> Tom...A...10
>> >> Tom...A...37
>>
>> >> Or, is it random:
>>
>> >> Tom...C...10
>> >> Bill...X...22
>> >> Sue...A...17
>> >> Bob...C...20
>> >> Tom...C...15
>>
>> >> Biff
>>
>> >> <haas...@yahoo.com> wrote in message
>>
>> >>news:(E-Mail Removed)...
>>
>> >> > Hi all! I desparately need help with an Excel problem. Here's an
>> >> > example of what I'm trying to achive below:
>>
>> >> > In Cell B2 I have a drop down list which contains names of our
>> >> > brokers.
>> >> > In Cell C2 I have a drop down list describing types of deals (values
>> >> > are C, A, X, or T)
>>
>> >> > Once the user chooses the name and type of deal, they would press a
>> >> > button which would then take the values in B2 and C2 together and
>> >> > find
>> >> > matches in another sheet which contains 3 columns worth of data:
>> >> > Name,
>> >> > Type of Deal, and Deal Number (alphanumeric). There will never be 0
>> >> > deal numbers or
>> >> > more than 8 answers for any given Criteria (e.g. If I select Tom and
>> >> > C, the resulting answer will have between 1 and 8 different Deal
>> >> > Numbers.)
>>
>> >> > Once Excel or VBA finds these deal numbers, they need to be "placed"
>> >> > or copied into cells B17 all the way down to B24 (if there are 8
>> >> > deals
>> >> > numbers.) So, if there are 2 deal numbers corresponding to Tom and
>> >> > C,
>> >> > Excel will copy both deal numbers and paste them into B17 and B18.
>>
>> >> > I'm not sure if VLOOKUP or HLOOKUP or INDEX - MATCH function can do
>> >> > this but if possible, please let me know.
>>
>> >> > Thank you in advance for your help!- Hide quoted text -
>>
>> >> - Show quoted text -
>>
>> > Biff,
>>
>> > The data is random, similar to your 2nd example, and the last column
>> > contains alphanumeric entries instead of just pure numbers.
>>
>> > Thanks...- Hide quoted text -
>>
>> - Show quoted text -
>
> Thanks a million - this did the trick! God bless you!
>