Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
two arrays
Hi,
In col A througn F there are numbers about 150 rows down. In G1 through L1 there are numbers and these will change from time to time. G2 through L2 and down I want a formula that will find the numbers that are in A through F and put YES in the corresponding cell under G to L. So any cell in A:F that has one of the numbers in G:L the formula will put yes in the corresponding cell under G:L if the numbers is in G1:L1. A B C D E F G H I J K L Numbers 3 5 12 16 34 27 38 40 48 52 56 46 34 32 33 43 53 9 Yes 8 15 16 24 38 19 13 15 16 32 41 22 23 36 27 41 42 21 Yes 11 27 32 48 52 28 5 7 12 16 45 19 Yes Yes 24 31 33 46 50 7 16 17 18 30 37 27 Yes Yes 2 13 16 37 44 32 Hope this makes sense. Thanks, Jerry |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
two arrays
Do you want the rows in A through F to match the first row in H through L?
If so, and I understand you correctly, try putting this formula in G2 and copying it down and across: =IF(H$1=A2,"YES","") "Jerry Kinder" wrote: Hi, In col A througn F there are numbers about 150 rows down. In G1 through L1 there are numbers and these will change from time to time. G2 through L2 and down I want a formula that will find the numbers that are in A through F and put YES in the corresponding cell under G to L. So any cell in A:F that has one of the numbers in G:L the formula will put yes in the corresponding cell under G:L if the numbers is in G1:L1. A B C D E F G H I J K L Numbers 3 5 12 16 34 27 38 40 48 52 56 46 34 32 33 43 53 9 Yes 8 15 16 24 38 19 13 15 16 32 41 22 23 36 27 41 42 21 Yes 11 27 32 48 52 28 5 7 12 16 45 19 Yes Yes 24 31 33 46 50 7 16 17 18 30 37 27 Yes Yes 2 13 16 37 44 32 Hope this makes sense. Thanks, Jerry |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
two arrays
In G2 copied across and down =IF(ISNUMBER(MATCH(G$1,$A2:$F2,0)),"yes","") -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=516554 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
two arrays
THANKS, it works great!
Jerry "daddylonglegs" wrote in message news:daddylonglegs.23t1gz_1140903905.1237@excelfor um-nospam.com... In G2 copied across and down =IF(ISNUMBER(MATCH(G$1,$A2:$F2,0)),"yes","") -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=516554 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
two arrays
Just to extend it a little further ..
If we want to count the number of numbers within cols A to F (in row2 down) which match the reference set of numbers in G1:L1 (eg: as in checking lotto tickets against results) we could put in say, M2: =SUMPRODUCT(--ISNUMBER(MATCH(A2:F2,G$1:L$1,0))) and copy M2 down to return the counts [The numbers in cols A to F, and those in the reference set can be in any order, need not be sorted] -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using CORREL with arrays containing null values | Excel Discussion (Misc queries) | |||
vba adding arrays | Excel Discussion (Misc queries) | |||
Problem with plotting a chart when using arrays as Values and Xvalues | Charts and Charting in Excel | |||
Confused about arrays and ranges in functions | Excel Worksheet Functions | |||
Comparing Arrays | Excel Discussion (Misc queries) |