Compare each value in a range to each value in another range

I am looking for a macro that will return a comparison of each cell in
a range to each cell in another range.

example.     the first range would have vales of 1,2,3   and the second
range would have values of 5,6,7  Thus the macro should return  9
possible comparisons:  1 & 5,   1 & 6,   1 & 7,  2 & 5,  2 & 6,  2 & 7,
 3 & 5,  3 & 6,  3 & 7,         

Can anybody help me with this??

0
trigo1 (2)
9/22/2006 1:58:50 AM
excel 39879 articles. 2 followers. Follow

6 Replies
630 Views

Similar Articles

[PageSpeed] 18

Just hazarding some thoughts here ..
Perhaps using formulas would suffice ?
Example: Assume 1st range is A1:A3, 2nd range is B1:B3. Then

Compare 1st range against 2nd range
In C1:
=IF(ISNUMBER(MATCH(A1,$B$1:$B$3,0)),"Y","")
Copy C1 down to the last row of data in the 1st range, ie to C3. This 
returns the comparison results: Y, if item in 1st range is found in the 2nd 
range, blank: "" if not found

Compare 2nd range against 1st range
In D1:
=IF(ISNUMBER(MATCH(B1,$A$1:$A$3,0)),"Y","")
Copy D1 down to the last row of data in the 2nd range, ie to D3. This 
returns the comparison results: Y, if item in 2nd range is found in the 1st 
range, blank: "" if not found
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
--- 
<trigo1@cox.net> wrote in message 
news:1158890330.194339.209860@d34g2000cwd.googlegroups.com...
>I am looking for a macro that will return a comparison of each cell in
> a range to each cell in another range.
>
> example.     the first range would have vales of 1,2,3   and the second
> range would have values of 5,6,7  Thus the macro should return  9
> possible comparisons:  1 & 5,   1 & 6,   1 & 7,  2 & 5,  2 & 6,  2 & 7,
> 3 & 5,  3 & 6,  3 & 7,
>
> Can anybody help me with this??
> 


0
demechanik (4694)
9/22/2006 9:09:38 AM
If the ranges are vertical--A1:A3 and B1:B6, you could use an array formula
like:

=SUM(--(A1:A3=TRANSPOSE(B1:B6)))

This is an array formula.  Hit ctrl-shift-enter instead of enter.  If you do it
correctly, excel will wrap curly brackets {} around your formula.  (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

trigo1@cox.net wrote:
> 
> I am looking for a macro that will return a comparison of each cell in
> a range to each cell in another range.
> 
> example.     the first range would have vales of 1,2,3   and the second
> range would have values of 5,6,7  Thus the macro should return  9
> possible comparisons:  1 & 5,   1 & 6,   1 & 7,  2 & 5,  2 & 6,  2 & 7,
>  3 & 5,  3 & 6,  3 & 7,
> 
> Can anybody help me with this??

-- 

Dave Peterson
0
petersod (12005)
9/22/2006 12:09:58 PM
Hey guys thanks for the quick response....I tryied both recomondations
and both were able to compare the data i needed. However how can i get
excel to return each possible comparison   i.e
 1 & 5,   1 & 6,   1 & 7,  2 & 5,  2 & 6,  2 & 7,  3 & 5,  3 & 6,  3 &
7,  from the two rages of 1,2,3   and the second of 5,6,7  ??

Ultimatly the end result would be a list of all the possible
combonations.

thanks again





Dave Peterson wrote:
> If the ranges are vertical--A1:A3 and B1:B6, you could use an array formula
> like:
>
> =SUM(--(A1:A3=TRANSPOSE(B1:B6)))
>
> This is an array formula.  Hit ctrl-shift-enter instead of enter.  If you do it
> correctly, excel will wrap curly brackets {} around your formula.  (don't type
> them yourself.)
>
> Adjust the range to match--but you can't use the whole column.
>
> trigo1@cox.net wrote:
> >
> > I am looking for a macro that will return a comparison of each cell in
> > a range to each cell in another range.
> >
> > example.     the first range would have vales of 1,2,3   and the second
> > range would have values of 5,6,7  Thus the macro should return  9
> > possible comparisons:  1 & 5,   1 & 6,   1 & 7,  2 & 5,  2 & 6,  2 & 7,
> >  3 & 5,  3 & 6,  3 & 7,
> > 
> > Can anybody help me with this??
> 
> -- 
> 
> Dave Peterson

0
trigo1 (2)
9/23/2006 8:45:10 PM
Try this post from Myrna Larson:
http://groups.google.com/group/microsoft.public.excel.misc/msg/2150eee92452c83c?output=gplain

or
http://snipurl.com/wzbn

trigo1@cox.net wrote:
> 
> Hey guys thanks for the quick response....I tryied both recomondations
> and both were able to compare the data i needed. However how can i get
> excel to return each possible comparison   i.e
>  1 & 5,   1 & 6,   1 & 7,  2 & 5,  2 & 6,  2 & 7,  3 & 5,  3 & 6,  3 &
> 7,  from the two rages of 1,2,3   and the second of 5,6,7  ??
> 
> Ultimatly the end result would be a list of all the possible
> combonations.
> 
> thanks again
> 
> Dave Peterson wrote:
> > If the ranges are vertical--A1:A3 and B1:B6, you could use an array formula
> > like:
> >
> > =SUM(--(A1:A3=TRANSPOSE(B1:B6)))
> >
> > This is an array formula.  Hit ctrl-shift-enter instead of enter.  If you do it
> > correctly, excel will wrap curly brackets {} around your formula.  (don't type
> > them yourself.)
> >
> > Adjust the range to match--but you can't use the whole column.
> >
> > trigo1@cox.net wrote:
> > >
> > > I am looking for a macro that will return a comparison of each cell in
> > > a range to each cell in another range.
> > >
> > > example.     the first range would have vales of 1,2,3   and the second
> > > range would have values of 5,6,7  Thus the macro should return  9
> > > possible comparisons:  1 & 5,   1 & 6,   1 & 7,  2 & 5,  2 & 6,  2 & 7,
> > >  3 & 5,  3 & 6,  3 & 7,
> > >
> > > Can anybody help me with this??
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12005)
9/23/2006 8:55:22 PM
Here's a way to generate the combinations from column data ..
 
Assume a source of 3 variables per col within 6 cols in Sheet1's A1:F3, viz:
1 10 21 34 40 11
3 14 23 37 42 13
4 17 28 38 43 18
(all 18 numbers are assumed unique)

and we want to "generate" the above into a total of:
3^6 = 729 combinations in a final output Sheet4, ie:

1-10-21-34-40-11 (< in A1)
1-10-21-34-40-13
1-10-21-34-40-18
....
....
4-17-28-38-43-11
4-17-28-38-43-13
4-17-28-38-43-18 (< in A729)

Steps:

In Sheet2
-------------
Put in:
A1: =OFFSET(Sheet1!$A$1,INT((ROW(A1)-1)/3),)
B1: =OFFSET(Sheet1!$B$1,MOD(ROW(A1)-1,3),)
C1: =OFFSET(Sheet1!$C$1,INT((ROW(A1)-1)/3),)
D1: =OFFSET(Sheet1!$D$1,MOD(ROW(A1)-1,3),)
E1: =OFFSET(Sheet1!$E$1,INT((ROW(A1)-1)/3),)
F1: =OFFSET(Sheet1!$F$1,MOD(ROW(A1)-1,3),)

Select A1:F1, copy down to F9

In Sheet3
-------------
Put in A1
=OFFSET(Sheet2!$A$1,INT((ROW(A1)-1)/9),)&"-"&OFFSET(Sheet2!$B$1,INT((ROW(A1)-1)/9),)

Put in B1
=OFFSET(Sheet2!$C$1,MOD(ROW(A1)-1,9),)&"-"&OFFSET(Sheet2!$C$1,MOD(ROW(A1)-1,9),1)

Put in C1
=OFFSET(Sheet2!$E$1,MOD(ROW(A1)-1,9),)&"-"&OFFSET(Sheet2!$E$1,MOD(ROW(A1)-1,9),1)

Select A1:C1, copy down to C81

In Sheet4
------------
Put in A1
=OFFSET(Sheet3!$A$1,INT((ROW(A1)-1)/9),)&"-"&OFFSET(Sheet3!$B$1,INT((ROW(A1)-1)/9),)&"-"&OFFSET(Sheet3!$C$1,MOD(ROW(A1)-1,9),)

Copy A1 down to A729. This will list all 729 (3^6) combinations from the 
source data in Sheet1's A1:F3.

Adapt to suit .. The method limits a max extension for the source data 
to 6 var per col in 6 cols (in Sheet1's A1:F6) which'll generate 6^6 = 46656 
combinations in Sheet4 (as 7^6 = 117649, which exceeds Excel's max 65536 
rows). 
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"trigo1@cox.net" wrote:
> Hey guys thanks for the quick response....I tryied both recomondations
> and both were able to compare the data i needed. However how can i get
> excel to return each possible comparison   i.e
>  1 & 5,   1 & 6,   1 & 7,  2 & 5,  2 & 6,  2 & 7,  3 & 5,  3 & 6,  3 &
> 7,  from the two rages of 1,2,3   and the second of 5,6,7  ??
> 
> Ultimatly the end result would be a list of all the possible
> combonations.

Orig. post:
<trigo1@cox.net> wrote in message 
news:1158890330.194339.209860@d34g2000cwd.googlegroups.com...
>I am looking for a macro that will return a comparison of each cell in
> a range to each cell in another range.
>
> example.     the first range would have vales of 1,2,3   and the second
> range would have values of 5,6,7  Thus the macro should return  9
> possible comparisons:  1 & 5,   1 & 6,   1 & 7,  2 & 5,  2 & 6,  2 & 7,
> 3 & 5,  3 & 6,  3 & 7,
>
> Can anybody help me with this??
0
demechanik (4694)
9/24/2006 10:40:02 AM
Here's a sample construct: 
http://www.savefile.com/files/101884
Generating combinations from column data.xls

-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
0
demechanik (4694)
9/24/2006 12:14:01 PM
Reply:

Similar Artilces:

Chart with Named Range error
Using Excel 2007, creating a chart using named ranges as inputs when I try ti enter the named range as a source I get a formula error "the formula you typed containes an error..." So I setup a simple scenario to dumlicate the problem. named a range of cells "Test" and entered arbitrary numbers in the cells of the range. Insert chart, Right click on the chart and select "select data" then "add" In the box type "=Test" or used the "Use in formula/paste name" tool. Select ok and get the formula error. I dont think I had any problems w...

If "yes" then require data in another field
I have two fields on a form: one a "check box" and another a "combo list" If the check box = Yes, then I want the field for the combo list to require data. I can make it go the field that I want the date inserted, but I cannot seem to figure out how to make it mandatory for the user to insert data from the drop down list if the check box = yes. Use the Exit event of the ComboBox and Cancel = True If Len(Me.ComboBox & "") = 0 Sheryl wrote: >I have two fields on a form: one a "check box" and another a "combo list" > >If t...

Creating a goto/anchor in excel based on a cell value
Hello, I am creating a financial worksheet for a client in which the user can choose options from a drop down menu. What I want to happen is, based on the option the user chooses, the model sends them to a certain cell on the same sheet. Ex: If user chooses 1 from the drop down, it sends them to cell A4. If the user chooses 2 from the drop down, it sends them to cell A5. Also, I would like to know how to trigger a macro based on a cell value Here is the code I thought might work =if(A1=1, Run_OLM(),"") Any advice on these topics is much appreciated. Thanks in advance. Tushar...

Chart Axes Linked to cell values
Hello Everyone. I have some sort of a problem with linking chart axe (min and max values) to cell values in a worksheet. I am using th macro that I found in Mr Peltier' site: http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html It worked just fine for a simple test I was doing.. BUT, I came up wit the idea of assigning to the cells (the cells that contain the scal params) some worksheet functions, to determine the values from a set o data. When I did this, the macro didn't work anymore. It didn't pop u any errors or such things, it just didn't adjust the chart anymo...

Linking a Message Box to a cell value
Okay, this is probably the worst way to do things, but I want to link cell value to a message box. I am basically going to use the messag box as a method of data validation. If the data value of the cel exceeds a certain data range, then the message box should pop up an alert the user that his input data is "out-of-range" I can't really use the data validation approach. I have a drop dow menu next to the input data cell to allow for different input units. This drop down menu is used along with conversion factors to a specifi unit that I want to use in my calculations. The ...

Safe way to exchange Users folder to another HD
Hi all, windows 7 pro x64 in a tiny SSD (C: with OS)(30 Gb) another normal HD (E: with data)(500 Gb). is there any safe way to move all C:\users to e.g. E:\Users and not having a pain in the a... with problems of: "not found", "impossible to add new users", etc. could be fine a step by step guide of how to do it, and thing that not has to be done. Thanks, Corsair. Corsair wrote: > windows 7 pro x64 in a tiny SSD (C: with OS)(30 Gb) another normal > HD (E: with data)(500 Gb). > > is there any safe way to move all C:\users to e.g. E:\U...

Money 2007 file wont update from one computer to another
Ok, I have a desktop with Vista and laptop with XP home. I use the notebook when Im away at work and the Vista desktop when Im home. When I got home I wanted my money file from the notebook to be transfered to the desktop but... when I copy the file from the notebook to my external USB HD and then paste it to the desktop to replace the existing one the file does not reflect changes made on the file version on the notebook. When I copy the file from the notebook to the USB HD and then open it it shows one balance. but when I then paste that file to the desktop it shows a different balan...

Computed value does not appear...
I am on a Windows 2000 platform, using Office 2000. Recently, when I enter data in a cell, which is linked to other cells on the spreadsheet, the newly computed values in the other cells do not appear until I hit Save, or until I enter data elsewhere on the spreadsheet. The only software changes to my system have been two security updates from the Windows Update web site. Does anyone have any thoughts why the changed values are delayed in appearing on my screen? Thanks in advance. Gordon Biggar Houston, Texas Gordon Tools>Options>Calculation Change to "automatic" Go...

Programmatically Update a Form's Combo Box Default Value and SQL Record Source
I have one Sales Activity form that I use for data entry and for viewing all records by Sales Person (EmployeeID). Without having to have multiple instances of this form, I want to accomplish the below; based on which EmployeeID (sales person) opens the form in either mode: 1. For the data entry mode – I want the Combo Box’s Default Value to change, based on my user (EmployeeID); 2. Show All Records mode – I want the SQL record source to filter on that sales person’s records (EmployeeID) I’ve got some code for passwords, so I hope I can utilize that part of it; I just do not know how to do ...

how can i transfer data from one sheet to another without blank li
Dear All I have extracted data from a system to a sheet including blank lines between the data. Any idea of how i can transfer the data from one sheet to another without any blank lines, in order to do my job using excel functions? Thanks in advance If you mean blank rows why dont you just sort the data? In case you need to retain the order (you can use a temporary column); insert an additional column say A. Number 1,2,....till the end in cells a1,a2 ...... Sort col B. Delete the blank rows..and then sort by ColA...Remove ColA -- Jacob (MVP - Excel) "Manos&q...

Re: Maximum Value based on multiple criteria
Hi, I have the excel list with fields designation, experience, salary. I want to find out the maximum salary for a particular designation an particular experience range I specify. Is it possible to us sumproduct for this purpose? Can somebody help me out? Thanks in anticipation Regards V S Mohanakrishna -- Message posted from http://www.ExcelForum.com One way: Assume designation in column A, experience in column B, salary in column C. Also assume that the desired designation is in D1 and desired experience in D2: =MAX(IF((A1:A1000=D1)*(B1:B1000=D2),C1:C1000,0)) If you want to...

Control Button to Create a New Record in Another Form
I've developed a MS Access application for Trade Shows. One of my tables contains all basic client info. This table is used as the data source for both a complete client form and a partial data form. Both forms can show all clients (individually). Using the partial data form I want to create a button that copies the current record and creates a new record in a form titled Trade Show Attendees. Other data in the Trade Show Attendees must be entered to complete that attendee's record. Some of the data entered will populate the TradeShow Attendees table. Can anyone describe t...

How to look up a value?
I have two column like this: A B 1 23 2 21 4 20 1 19 4 18 ....etc (The numbers in column A are random and do repeat) What I want to do is to look up a specific value in column A, like 4, and return the corresponding value of column B with the largest row number, in this case it's 18 not 20 Any suggestions? =) Hi, Try this. The 4 in the middle of the formula is the lookup value =LOOKUP(2,1/(A1:A20=4),B1:B20) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assu...

Report loading pictures base on value...
Dear all, I have a report(labels) that have 5 textboxes with single value from either 0 to 9. so text box 1 could be 5, and text box 2 could be 8, and so on...So when a text box is with a certain value, i want it to load an image. so if text box 5 has a value of five, I want it to load image 5. I looked everywhere but couldn't find the answer. I think i have a concept but cannot code it...Please help. Thanks in advance. i'm looking to use: select case (expression - don't know how to start) case 1 load image 1 case 2 load image 2 case 3 load image 3 and so on... Thanks...

Rule to move from one folder to another
Just moved from Lotus to Outlook 2007 on exchange. We have an email address that we use for all of our employees to mail in requests and tasks. There are 6 support people who work out of the mail folder in order to complete the requests. As each support person completes the request, she moves the request to the completed folder. What we've been told is that for the support person to move the item, we have to give her delete permission. The problem is that we don't want anyone to delete out of this mail file. One thing I was thinking is to create a rule that anything that m...

Lastrow in Range
Hello, This is the VBA macro that I use: Set rng = Range("h2:h167") For Each cell In rng If IsNumeric(cell.Value) Then cell.Value = Application.WorksheetFunction. _ RoundDown(cell.Value, 2) End If Next I'd like to set the rng to automatically start from H2 to the last row in column H. Thanks, try Set rng = Range("h2:h" & cells(rows.count,"H").end(xlup).row) -- Don Guillett SalesAid Software donaldb@281.com "Jeff" <Jeff@discussions.microsoft.com> wrote in message news:8C10211C-8D30-49A0-80E8-ABCE7CE0F7CB@...

Cell value not recognize in an expression
=SUMIF(B21:B24,">=C18",C21:C24 Any reason why the cell value of C18 would not be recognized, if I replace the value of c18 in the expression, it works but putting the value of C18 as a reference in the expression give me zero as an answer. Hi jaclh2o! Use: =SUMIF(B21:B24,">="&C18,C21:C24) -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au "jaclh2o" <anonymous@discussions.microsoft.com> wrote in message news:10CA0900-E305-4144-A0B4-16B092EFFD73@microsoft.com... > =SUMIF(B21:B24,">=C18",C21:C24) &g...

named ranges #2
Hello gurus, This is a fun project, or it could be with a little help for you fine folks. I am establishing named ranges to create dynamic charting. Since I will be doing 100+ charts for multiple groups at my company, I’ve set up a standard format for each set of named ranges that generate the charts. However, I am not that far along and defining a new set of named ranges (approx 12 per chart = 1,200 names) 100 times over has proven quite daunting, even with the use of the name manager (http://www.jkp-ads.com/officemarketplacenm-en.asp). Each group will have one workbook, each with...

Area chart
Hi all, I have been creating graphs using the stacked area type to display data for data by year. However I have some years that have missing data. This then plots as zero. I have been to tools, options, charts and clicked interpolate for missing values but this doesn`t seem to work for the stacked area chart...has anyone else found this? Has anyone found an acceptable work around? I`d be interested if anybosy has found anything similar, thanks in advance for any help. what do you want it to plot? "Lasci" wrote: > Hi all, > > I have been creating graphs using the ...

QAT Reference to Macros in Another Workbook Don't Stick
Running Excel 2007 under Vista. I have one workbook which will contain all my data, and a second which contains a set of macros. By adding the second workbook as a reference, I am able to access all of the macros from the first. (I did have to add a dummy macro to the first workbook to get the reference to stick.) I can add these macros to the Quick Access Toolbar in the first workbook, and they work until I close the file; however, they are gone when the file is re-opened. I can add my dummy macro from the first file to the QAT, and that sticks. However, if I then add a macro fro...

configuring / comparing Outlook with OE
Hello, Hopefully I can find some help here. I’m currently in utter disbelief with this situation. I just installed Outlook 2007, and I can’t believe what a hassle it is. I have been running Outlook Express for the last 12 years. I now have five email accounts, three that are for my business, one for bill pay purposes, and another personal. I thought by now, since 1998 when I first started using Outlook Express, that Microsoft would have developed into Outlook the basics of a good functional email program, something that was a “step-up” from Outlook Express, and so I de...

adding values in a column
I want to add currency values in column A and in column B, each time I add a new value, column B updates with the total. However, I don't want the last sum to be displayed down the entire column B. How can I hide that? Does this illustrate what you want?... Suppose you have data in column A, in the range A3:A8. In the range B3:B100 you have the running total for column A. (Cell B8, for example, has the formula =B7+A8.) But no subtotals show above or below cell B8. Then, when you enter a value in cell A9, the subtotal appears only for cell B9. If that's what you want, here...

How to reference another cell's content directly in a formula
Hi all, For a cell such as that in A2 below: A B 1 4Q 2005 1Q 2006 2 ='4Q 2005'!$R$7 3 The '4Q 2005' refers to a sheet in the same excel work book with that name, so therefore cell A2 is set equal to cell R7 of sheet '4Q 2005'. Is there any function I can use so that cell A2 uses the data in cell A1 to figure out which sheet name to look for? I'm doing this so that I can copy and paste cell A2 into cell B2 and don't have to manually input '1Q 2006'. Something to the effect of the pseudocode = 'content_of(A1)'!$R$7. When copied and p...

Map XML data to another XML
Hi, I have 2 xml document the A and B, i want to to map some of the data from A to B using XSLT, but without creating a new XML Document. How can i do that? Thanks Hello! > Hi, I have 2 xml document the A and B, i want to to map some of the data from > A to B using XSLT, but without creating a new XML Document. How can i do that? In .NET it is possible to redirect the XSLT output to an XmlReader, but I can't find it now... But you can also use a MemoryStream: write the output to the memory, set the stream's position to 0 and read it again: <http://www.xml.com/pub/a/2...

Counting Soecific dates in a range
Hi, I have a series of dates in A3:A1000 and I only want to count the dates in September 2009 **/09/09 (UK date format) in that range. I looked at COUNT, COUNTIF etc. but they only seem to look for text strings. I seem to be going around in circles with this. -- Steve Try this: =COUNTIF(A1:A1000,">="&DATE(2009,9,1))-COUNTIF(A1:A1000,">"&DATE(2009,9,30)) Or, use cells to hold the date boundaries: C1 = start date = 1/9/2009 D1 = end date = 30/9/2009 =COUNTIF(A1:A1000,">="&C1)-COUNTIF(A1:A1000,">"&D1) Format as Gener...