| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a list of cells based on data in a single cell.
Basically, I want to enter a model number in say, cell A1 and if the value of
that cell is the same as the model number in any cell A2:A80, I want any of the corresponding serial numbers which are located in B2:B80 to be returned as a list in another area such as column C. I'm sure that this is going to call for an array and lookup, but I cannot get the formula that I found in another area of this site to work. Thanks for your help. Here is a sample of what I would like.... A B 1 modelx 2 modelw 8495065 3 modelx 7343034 4 modelx 9485895 5 modelu 8589439 6 modelx 9869949 And I would like to have these values returned c 7343034 9485895 9869949 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return a list of cells based on data in a single cell.
Pivot table is the best option.
Try the following formula. =IF(ROWS($1:1)<=COUNTIF($A$2:$A$80,$A$1),INDEX($B$ 2:$B$80,SMALL(IF($A$2:$A$80=$A$1,ROW($A$2:$A$80)-MIN(ROW($A$2:$A$80))+1),ROWS($1:1))),"") Paste the formula in C2. A1= model number. It is an array formula. Enter it with Ctrl+shift+enter. No just enter. Copy down the formula. Change the ranges according to your data. Best wishes Sreedhar "Tim G." wrote: Basically, I want to enter a model number in say, cell A1 and if the value of that cell is the same as the model number in any cell A2:A80, I want any of the corresponding serial numbers which are located in B2:B80 to be returned as a list in another area such as column C. I'm sure that this is going to call for an array and lookup, but I cannot get the formula that I found in another area of this site to work. Thanks for your help. Here is a sample of what I would like.... A B 1 modelx 2 modelw 8495065 3 modelx 7343034 4 modelx 9485895 5 modelu 8589439 6 modelx 9869949 And I would like to have these values returned c 7343034 9485895 9869949 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return a list of cells based on data in a single cell.
*IF* the serial numbers are NUMERIC numbers and none have leading 0s this
array formula** saves a few keystrokes: =IF(ROWS(C$1:C1)<=COUNTIF(A$2:A$6,A$1),SMALL(IF(A$ 2:A$6=A$1,B$2:B$6),ROWS(C$1:C1)),"") Copy down until you get blanks. It will extract the serial numbers sorted in ascending order. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... Pivot table is the best option. Try the following formula. =IF(ROWS($1:1)<=COUNTIF($A$2:$A$80,$A$1),INDEX($B$ 2:$B$80,SMALL(IF($A$2:$A$80=$A$1,ROW($A$2:$A$80)-MIN(ROW($A$2:$A$80))+1),ROWS($1:1))),"") Paste the formula in C2. A1= model number. It is an array formula. Enter it with Ctrl+shift+enter. No just enter. Copy down the formula. Change the ranges according to your data. Best wishes Sreedhar "Tim G." wrote: Basically, I want to enter a model number in say, cell A1 and if the value of that cell is the same as the model number in any cell A2:A80, I want any of the corresponding serial numbers which are located in B2:B80 to be returned as a list in another area such as column C. I'm sure that this is going to call for an array and lookup, but I cannot get the formula that I found in another area of this site to work. Thanks for your help. Here is a sample of what I would like.... A B 1 modelx 2 modelw 8495065 3 modelx 7343034 4 modelx 9485895 5 modelu 8589439 6 modelx 9869949 And I would like to have these values returned c 7343034 9485895 9869949 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return a list of cells based on data in a single cell.
It worked EXACTLY like I wanted. Thanks y'all.
"T. Valko" wrote: *IF* the serial numbers are NUMERIC numbers and none have leading 0s this array formula** saves a few keystrokes: =IF(ROWS(C$1:C1)<=COUNTIF(A$2:A$6,A$1),SMALL(IF(A$ 2:A$6=A$1,B$2:B$6),ROWS(C$1:C1)),"") Copy down until you get blanks. It will extract the serial numbers sorted in ascending order. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... Pivot table is the best option. Try the following formula. =IF(ROWS($1:1)<=COUNTIF($A$2:$A$80,$A$1),INDEX($B$ 2:$B$80,SMALL(IF($A$2:$A$80=$A$1,ROW($A$2:$A$80)-MIN(ROW($A$2:$A$80))+1),ROWS($1:1))),"") Paste the formula in C2. A1= model number. It is an array formula. Enter it with Ctrl+shift+enter. No just enter. Copy down the formula. Change the ranges according to your data. Best wishes Sreedhar "Tim G." wrote: Basically, I want to enter a model number in say, cell A1 and if the value of that cell is the same as the model number in any cell A2:A80, I want any of the corresponding serial numbers which are located in B2:B80 to be returned as a list in another area such as column C. I'm sure that this is going to call for an array and lookup, but I cannot get the formula that I found in another area of this site to work. Thanks for your help. Here is a sample of what I would like.... A B 1 modelx 2 modelw 8495065 3 modelx 7343034 4 modelx 9485895 5 modelu 8589439 6 modelx 9869949 And I would like to have these values returned c 7343034 9485895 9869949 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return a list of cells based on data in a single cell.
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Tim G." wrote in message ... It worked EXACTLY like I wanted. Thanks y'all. "T. Valko" wrote: *IF* the serial numbers are NUMERIC numbers and none have leading 0s this array formula** saves a few keystrokes: =IF(ROWS(C$1:C1)<=COUNTIF(A$2:A$6,A$1),SMALL(IF(A$ 2:A$6=A$1,B$2:B$6),ROWS(C$1:C1)),"") Copy down until you get blanks. It will extract the serial numbers sorted in ascending order. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... Pivot table is the best option. Try the following formula. =IF(ROWS($1:1)<=COUNTIF($A$2:$A$80,$A$1),INDEX($B$ 2:$B$80,SMALL(IF($A$2:$A$80=$A$1,ROW($A$2:$A$80)-MIN(ROW($A$2:$A$80))+1),ROWS($1:1))),"") Paste the formula in C2. A1= model number. It is an array formula. Enter it with Ctrl+shift+enter. No just enter. Copy down the formula. Change the ranges according to your data. Best wishes Sreedhar "Tim G." wrote: Basically, I want to enter a model number in say, cell A1 and if the value of that cell is the same as the model number in any cell A2:A80, I want any of the corresponding serial numbers which are located in B2:B80 to be returned as a list in another area such as column C. I'm sure that this is going to call for an array and lookup, but I cannot get the formula that I found in another area of this site to work. Thanks for your help. Here is a sample of what I would like.... A B 1 modelx 2 modelw 8495065 3 modelx 7343034 4 modelx 9485895 5 modelu 8589439 6 modelx 9869949 And I would like to have these values returned c 7343034 9485895 9869949 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Condition font color in a single cell based on its formula data. | Excel Worksheet Functions | |||
| How do I return the vale of multiple cells into a single cell | Excel Worksheet Functions | |||
| Return Single Row of Numeric Data to Single Column | Excel Worksheet Functions | |||
| Return a cell value based on specific combinations of cells in an array | Excel Worksheet Functions | |||
| Return a block of text based on a single "short name" entry | Excel Worksheet Functions | |||
Linear Mode
