Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Lookup Help - Please
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Lookup Help - Please
No one else has replied yet so I thought I'd post my not-so-simple offering.
Assuming: Cells B2, C2, B17:B24 referred to below are all on sheet2. There's a table on Sheet1 with headers on row1, say: Name/Deal Type/Deal Number in columns A,B and C respectively. and this table is populated from row2 to row 16. Then on Sheet2, cell B17, array-enter (using Shift+Ctrl+Enter) the following formula: =IF(ISERROR(INDIRECT("Sheet1!C" &SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1! $B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),1) )),"-",INDIRECT("Sheet1!C" &SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1! $B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),1) )) If this has been entered properly, when you look at the formula it should have curly braces around it viz. {=theformula}. In the same way, enter this formula into B18: =IF(ISERROR(INDIRECT("Sheet1!C" &SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1! $B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),2) )),"-",INDIRECT("Sheet1!C" &SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1! $B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),2) )) and this in cell B19: =IF(ISERROR(INDIRECT("Sheet1!C" &SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1! $B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),3) )),"-",INDIRECT("Sheet1!C" &SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1! $B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),3) )) in B20: =IF(ISERROR(INDIRECT("Sheet1!C" &SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1! $B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),4) )),"-",INDIRECT("Sheet1!C" &SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1! $B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),4) )) in B21: =IF(ISERROR(INDIRECT("Sheet1!C" &SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1! $B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),5) )),"-",INDIRECT("Sheet1!C" &SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1! $B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),5) )) on B22: =IF(ISERROR(INDIRECT("Sheet1!C" &SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1! $B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),6) )),"-",INDIRECT("Sheet1!C" &SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1! $B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),6) )) in B23: =IF(ISERROR(INDIRECT("Sheet1!C" &SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1! $B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),7) )),"-",INDIRECT("Sheet1!C" &SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1! $B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),7) )) in B24: =IF(ISERROR(INDIRECT("Sheet1!C" &SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1! $B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),8) )),"-",INDIRECT("Sheet1!C" &SMALL(IF((Sheet1!$A$2:$A$16=Sheet2!$B$2)*(Sheet1! $B$2:$B$16=Sheet2!$C$2),ROW(Sheet1!$C$2:$C$16)),8) )) (There are only two small differences in each case) Obviously, you should amend the ranges to suit or use named ranges: B17: =IF(ISERROR(INDIRECT("Sheet1!C" &SMALL(IF((BrokerNames=Broker)*(DealTypes=DealType ),ROW(DealNumbers)),1))),"-",INDIRECT("Sheet1!C" &SMALL(IF((BrokerNames=Broker)*(DealTypes=DealType ),ROW(DealNumbers)),1))) B18: =IF(ISERROR(INDIRECT("Sheet1!C" &SMALL(IF((BrokerNames=Broker)*(DealTypes=DealType ),ROW(DealNumbers)),2))),"-",INDIRECT("Sheet1!C" &SMALL(IF((BrokerNames=Broker)*(DealTypes=DealType ),ROW(DealNumbers)),2))) B19: =IF(ISERROR(INDIRECT("Sheet1!C" &SMALL(IF((BrokerNames=Broker)*(DealTypes=DealType ),ROW(DealNumbers)),3))),"-",INDIRECT("Sheet1!C" &SMALL(IF((BrokerNames=Broker)*(DealTypes=DealType ),ROW(DealNumbers)),3))) etc. (array-entered, of course) Named ranges we Broker =Sheet2!$B$2 BrokerNames =Sheet1!$A$2:$A$16 DealNumbers =Sheet1!$C$2:$C$16 DealType =Sheet2!$C$2 DealTypes =Sheet1!$B$2:$B$16 I'm still pondering on something simpler (vba would be simpler but I've been trying to do it with formulae only, (and now I've only just noticed the subject of the post "VBA Lookup Help"! I'll try to post something later) -- p45cal " 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Lookup Help - Please
Following on, a VBA solution:
In a standard module: Sub blah() Dim ResultArray() ReDim ResultArray(1 To 1) counter = 0 For Each cll In Range("BrokerNames") If cll.Value = Sheets("Sheet2").Range("B2") And _ UCase(cll.Offset(0, 1).Value) = Sheets("Sheet2").Range("c2") Then counter = counter + 1 ReDim Preserve ResultArray(1 To counter) ResultArray(counter) = cll.Offset(0, 2).Value End If If counter = 8 Then Exit For 'to stop searching if 8 have been found Next cll mySize = UBound(ResultArray) - LBound(ResultArray) Sheets("Sheet2").Range("B17:B24").ClearContents Sheets("Sheet2").Range("B17:B" & 17 + mySize) = _ Application.WorksheetFunction.Transpose(ResultArra y) End Sub In Sheet2's code module: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$2" Or Target.Address = "$C$2" Then blah End If End Sub It requires the Named range: BrokerNames =Sheet1!$A$2:$A$16 -- p45cal " 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Lookup Help - Please
On May 13, 9:54 am, p45cal wrote:
Following on, a VBA solution: In a standard module: Sub blah() Dim ResultArray() ReDim ResultArray(1 To 1) counter = 0 For Each cll In Range("BrokerNames") If cll.Value = Sheets("Sheet2").Range("B2") And _ UCase(cll.Offset(0, 1).Value) = Sheets("Sheet2").Range("c2") Then counter = counter + 1 ReDim Preserve ResultArray(1 To counter) ResultArray(counter) = cll.Offset(0, 2).Value End If If counter = 8 Then Exit For 'to stop searching if 8 have been found Next cll mySize = UBound(ResultArray) - LBound(ResultArray) Sheets("Sheet2").Range("B17:B24").ClearContents Sheets("Sheet2").Range("B17:B" & 17 + mySize) = _ Application.WorksheetFunction.Transpose(ResultArra y) End Sub In Sheet2's code module: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$2" Or Target.Address = "$C$2" Then blah End If End Sub It requires the Named range: BrokerNames =Sheet1!$A$2:$A$16 -- p45cal " 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 - Thank you all for yur help - you guys are amazing! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Lookup looks to the prior column if zero appears in the lookup col | Excel Discussion (Misc queries) | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Lookup Ques - finding value within a string to lookup | Excel Programming |