Can anyone please help with this?

[haas786]

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!

 

[T. Valko]

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

 

[Guest]

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

 

[haas786]

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










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

 

[haas786]

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











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

 

[T. Valko]

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

 

[haas786]

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







- Show quoted text -

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

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