Creating a Match Macro

[tjquinn]

Hello everyone,

I am currently trying to find a way to automate a rather simple procedure. The idea is this:

There are two excel sheets, one with a theoretical mass and another with an experimental mass. I want to see how closely the theoretical mass matches to the experimental mass (within +- 0.5). So, is there an easy way to automate this process? One that takes the theoretical mass and looks into another excel sheet to determine if there is a match within a window of 0.5? If so, I want to be able to tally the number of matches and perhaps copy over the details of the second sheet. However, the overall idea is to tally the number of matches between the theoretical and experimental data.

I am not sure how to go about doing this as I have a very weak understanding of macros.

Thanks again,

tjquinn

[SDruley]

Please attach a copy of your workbook containing the actual on sheet one and the theoretical on sheet 2.

[tjquinn]

Thank you for the reply, here is a sample worksheet.

tjquinn

[rylo]

Hi

Theoretical Data!C2: =SUMPRODUCT(--('Experimental Data'!$B$2:$B$9>='Theoretical Data'!B2-0.5),--('Experimental Data'!$B$2:$B$9<='Theoretical Data'!B2+0.5))

Copy down to C16

See how that goes.

rylo

[tjquinn]

That seems to work quite well. Is it possible to obtain more of the data? That is, not only tally up the matches but copy over the cmpd number? For example, cmpd 10 in the theoretical corresponds to cmpd 5 in the experimental. I appreciate your help on this!

tjquinn

[rylo]

Hi

Following on from the formula in C2 try
Theoretical Data!D2: =CHOOSE(C2+1,"No Result",INDEX('Experimental Data'!A:A,SUMPRODUCT(--('Experimental Data'!$B$2:$B$9>='Theoretical Data'!B2-0.5),--('Experimental Data'!$B$2:$B$9<='Theoretical Data'!B2+0.5),ROW('Experimental Data'!$B$2:$B$9))),"More Than 1")

Copy down.

This will give you:
1) No Result - when the count is 0
2) The CMD number when there is 1 match or
3) More Than 1 - when there is a count of 2
4) An error message if there is a count of >2

Another way would be to create your own function.

Please Login or Register  to view this content.

Put that function into a general module in the workbook, then
theoretical data!E2: =myfunc(B2,'Experimental Data'!$B$2:$B$9)


rylo

[SDruley]

The attached chart shows the output of your designs