Estimated matching based on multiple column values

iotapsi321

New Member
Joined
Oct 6, 2017
Messages
22
Hello. I am wanting to match data in two spreadsheets with rows representing participants and columns representing variables. Because participant ID was not collected, the only way I could match the data is is based on the total scores of the variables. Is there a way I could create a formula to match the data based on several column values (i.e., variable scores). Another issue is that some of the data was removed in one spreadsheet and not the other. Ideally, the output would be a new spreadsheet with data matched to the best ability of the formula. Also, if exact matches aren't found I would like these identifiable. Below is what I think is a desirable output. If you have a better suggestion for the output I'd be glad to hear it. Thank you for your time.

Example
spreadsheet 1 (with deleted participants)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]variable A[/TD]
[TD]variable b[/TD]
[TD]variable c[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]20[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]25[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]23[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]

spreadsheet 2 (complete data, no deleted participants)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]variable A[/TD]
[TD]variable B[/TD]
[TD]variable C[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]23[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]26[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]20[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]22[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]25[/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]

Desired output
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]variable A spreadsheet 1[/TD]
[TD="align: center"]variable A spreadsheet 2[/TD]
[TD="align: center"]variable B spreadsheet 1[/TD]
[TD="align: center"]variable B spreadsheet 2[/TD]
[TD="align: center"]variable C
spreadsheet 1[/TD]
[TD="align: center"]variable C spreadsheet 2[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]30[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]35[/TD]
[TD]25[/TD]
[TD]25[/TD]
[TD]15[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]32[/TD]
[TD]23[/TD]
[TD]23[/TD]
[TD]12[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]34[/TD]
[TD][/TD]
[TD]16[/TD]
[TD][/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]33[/TD]
[TD][/TD]
[TD]22[/TD]
[TD][/TD]
[TD]11[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Forum statistics

Threads
1,223,706
Messages
6,173,998
Members
452,542
Latest member
Bricklin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top