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]
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: