I have in column A a list of numbers in random order that can repeat themselves. I want to be able to type a number in say cell C1. A routine will go through column A and find that number from C1 and take the number in the cell directly beneath it and put that number below the stated number in cell C1. Continue checking column A for any more that can be placed in column C. Is this possible? I have been looking for hours. Thank you.
Hi,
a possible solution in C2 and beneath (numbers in A2:A1500)
It's a array formula to be confirmed with control+shift+enter.=IF(COUNTIF($A$1:$A$1500;$C$1)>=ROWS($A$1:A1);INDEX(A$2:A$1501;SMALL(IF($A$1:$A$1500=$C$1;ROW($A$1:$ A$1500);"");ROWS($A$1:A1)));"")
Hope it helps
Thanks for your help. It accomplished what I needed to get done. However I have 6 columns of numbers instead of just one. I was hoping ot move your array formula to a different column and copy it 5 more times over to the right. Instead I just made 6 different worksheets and copied my numbers in the first column of each sheet to get the results. Is it possible to have more than one array formula on the same worksheet?
Originally Posted by CANAPONE
Hi, I don't have a clear picture of the layout of your numbers.
If you'd need to copy the formulae on the right, the formula could be an example. The formulae should help you to display the number beneath the numbers you're indicating in C1-G1-K1 (...)
To be confirmed with control+shift+enter.=IF(COUNTIF(A$1:A$1500;C$1)>=ROWS($A$1:A1);INDEX(A$2:A$1501;SMALL(IF(A$1:A$1500=C$1;ROW($A$1:$ A$1500);"");ROWS($A$1:$A1)));"")
A very close solution if you'd need to refer always to C1 (please, see the file attached)
If you would need to display beneath C1 all the "values under" from the range 6 columns, this naif attempt to nidificate the above formula is not for sure the best strategy.
In the file attached you can find -just for fun or good will- a formula to read and obtain numbers from the first three columns: I'm using Excel 2000.
To be confirmed with control+shift+enter.=IF(COUNTIF(A$1:A$200,$H$1)>=ROWS($A$1:$A1),INDEX(A$2:A$200,SMALL(IF(A$1:A$200=$H$1,RIF.ROW($A$1:$A$ 200),""),ROWS($A$1:$A1))))+IF(AND(COUNTIF(B$1:B$200,$H$1)>=ROWS($A$1:$A1)-COUNTIF($A$1:$A$200,$H$1),ROWS($A$1:$A1)-COUNTIF($A$1:$A$200,$H$1)>0),INDEX(B$2:B$200,SMALL(IF(B$1:B$200=$H$1,RIF.ROW($A$1:$A$200),""),ROWS($ A$1:$A1)-COUNTIF($A$1:$A$200,$H$1))))+IF(AND(COUNTIF(C$1:C$200,$H$1)>=ROWS($A$1:$A1)-COUNTIF($A$1:$B$200,$H$1),ROWS($A$1:$A1)-COUNTIF($A$1:$B$200,$H$1)>0),INDEX(C$2:C$200,SMALL(IF(C$1:C$200=$H$1,RIF.ROW($A$1:$A$200),""),ROWS($ A$1:$A1)-COUNTIF($A$1:$B$200,$H$1))))
I'm sure this is not the shortcut to get from A to B.
Regards
Last edited by CANAPONE; 02-24-2011 at 06:01 AM. Reason: very poor grammar
This is great. This will work perfectly. Thank you for your time and knowledge. It is amazing what excel is capable of if you have the know-how to use it. Thank you again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks