Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 168
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
matching 7 columns of data [email protected] Excel Programming 0 January 11th 08 01:50 AM
matching 2 columns of data karen Excel Discussion (Misc queries) 3 May 9th 07 03:15 PM
Matching Data in Columns ebraun01 Excel Worksheet Functions 7 January 14th 06 03:35 AM
How to take matching data from 2 columns and put in the same row? JustinM New Users to Excel 1 May 27th 05 12:32 AM
Matching data in columns Daria1977 Excel Discussion (Misc queries) 4 February 7th 05 02:24 PM


All times are GMT +1. The time now is 12:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"