Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Find data using three variables, all of which may repeat.
The worksheet lists card numbers in one column and text codes, package
labels, and status in other columns. Some card numbers repeat due to multiple codes and package labels but remain in ascending order. The text columns are in random order and some also repeat for other card numbers, but there is no duplication of the same three values. The task is finding a text code using an active card number and a package label. See simplified worksheet layout . . . Cards Package Code Status 1145 BC02A BCAA Active 1250 BC07A BCAS Inactive 1250 BC07A BCAR Active 1250 BC07A BCDR Inactive 1300 BC01X BCAA Active 1350 BC07A BCBY Active 1375 BC07F BCBQ Inactive 1375 BC07F BCBR Inactive 1375 BC07F BCBS Inactive 1st Question: What formula can look up the "Active" card 1250 in package BC07A and provide the code BCAR? 2nd Question: What formula can look up card 1375 in package BC07F and seeing no active cards in all three listings for that card number and package, insert the word "Inactive". |
#2
|
|||
|
|||
try
=if(iserror(sumproduct(--(Statusrange="Active"),--(cardsrange=1250),--(packagerange="BC07A"),coderange)),"inactive",sump roduct(--(Statusrange="Active"),--(cardsrange=1250),--(packagerange="BC07A"),coderange)) the --( changes the logical true false to a numeric 1-0 the arrays in each section of sumproduct must be the same size but cannot be the shorthand reference for entire rows or columns (A:A wont work) you can, of course, reference a cell in the argument instead of using the value . "Joe R." wrote: The worksheet lists card numbers in one column and text codes, package labels, and status in other columns. Some card numbers repeat due to multiple codes and package labels but remain in ascending order. The text columns are in random order and some also repeat for other card numbers, but there is no duplication of the same three values. The task is finding a text code using an active card number and a package label. See simplified worksheet layout . . . Cards Package Code Status 1145 BC02A BCAA Active 1250 BC07A BCAS Inactive 1250 BC07A BCAR Active 1250 BC07A BCDR Inactive 1300 BC01X BCAA Active 1350 BC07A BCBY Active 1375 BC07F BCBQ Inactive 1375 BC07F BCBR Inactive 1375 BC07F BCBS Inactive 1st Question: What formula can look up the "Active" card 1250 in package BC07A and provide the code BCAR? 2nd Question: What formula can look up card 1375 in package BC07F and seeing no active cards in all three listings for that card number and package, insert the word "Inactive". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I compare data from 2 worksheets to find duplicate entries | Excel Discussion (Misc queries) | |||
How do I find the last row of data and then use that as a variable | New Users to Excel | |||
Formula to find cell with data on a row | Excel Worksheet Functions | |||
Removing blank rows in a worksheet | Excel Worksheet Functions | |||
Find repeted data??? | Excel Discussion (Misc queries) |