Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Matching two columns of data
Hi All,
I have two columns of numbers that need to be kept as pairs for example; 122 505 122 909 345 6576 555 676 I need to randomly choose a pair x amount of times. I am using this formula in the first output column =INDEX($I$2:$I$5,RANDBETWEEN(1,COUNTA($I:$I)),1), which is fine to choose one of the numbers of column 1, however I am struggling to match the randomly chosen first number with its corresponding second number. This is as good as I have got =INDEX($J$2:$J$5,(MATCH(L2,$I$2:$I$5,0)),). But the problem with is that is chooses the first value it finds that corresponds to the correct number in column 1. So 122 is always paired with 505 never 909. I need a way to match the randomly chosen cell location rather than the value itself. Hope this makes sense. Thanks for any help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching two columns of data
Hi,
Am Wed, 21 Nov 2012 13:19:41 +0000 schrieb JFLaj: 122 505 122 909 345 6576 555 676 I need to randomly choose a pair x amount of times. I am using this formula in the first output column MATCH or VLOOKUP only find the first value. Randomly choose the first value in the column with unique values. Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching two columns of data
On Wednesday, November 21, 2012 5:38:48 AM UTC-8, JFLaj wrote:
Hi All, I have two columns of numbers that need to be kept as pairs for example; 122 505 122 909 345 6576 555 676 I need to randomly choose a pair x amount of times. I am using this formula in the first output column =INDEX($I$2:$I$5,RANDBETWEEN(1,COUNTA($I:$I)),1), which is fine to choose one of the numbers of column 1, however I am struggling to match the randomly chosen first number with its corresponding second number. This is as good as I have got =INDEX($J$2:$J$5,(MATCH(L2,$I$2:$I$5,0)),). But the problem with is that is chooses the first value it finds that corresponds to the correct number in column 1. So 122 is always paired with 505 never 909. I need a way to match the randomly chosen cell location rather than the value itself. Hope this makes sense. Thanks for any help. -- JFLaj Hi JFLaj, Maybe this will work for you... A1:A4 enter 1,2,3,4 B1:C4 is your example table D1 enter =RANDBETWEEN(1,4) Select BOTH cells E1 & F1 and enter =VLOOKUP(D1,A1:C4,{2,3},0) Commit with ctrl + shift + enter. Hit F9 to recalc results. HTH Regards, Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
matching 7 columns of data | Excel Programming | |||
matching 2 columns of data | Excel Discussion (Misc queries) | |||
Matching Data in Columns | Excel Worksheet Functions | |||
How to take matching data from 2 columns and put in the same row? | New Users to Excel | |||
Matching data in columns | Excel Discussion (Misc queries) |