How can I combine data for multiple excel files if they are not ordered in the same way?

Hi all,

I am reading two different excel documents into matlab, both of which contain data from the same subjects in the study. The study was a within-subjects crossover design with repeated measurements. The first excel document, 'A.xlsx' lists measurements that occurred in both arms of the study; it is ordered by subject id and session number (1-6). So, the first two columns of the second cell (after the header) read '1' (for subject ID) and '1' (for first study session), the first two columns of the third cell read '1' (same subject) and '2' (for the second study session).

However, the second excel document, 'B.xlsx' lists measurements that only occurred in one arm of the study. The subject ID numbers are the same, but the next column lists the order that participants completed that particular arm of the study (1-3). So, column 1 and 2 for row 2 reads (1,1), column 1 and 2 for row 3 reads (1,2), and so on. Complicating matters, the order of sessions was randomized.

What is the best way to combine these two excel documents together? I cannot edit these excel documents, but I can create a third excel document that lists both session number and order of the study arm together.

[Caglar]

So A.xlsx is like:

SubjectId Session

1 1

1 2

1 3 (increases up to 6)

2 1

2 2

2 3 (increases up to 6)

3 1

3 2

...

And B.xlsx:

SubjectId Sessions

1 (1,2)

2 (1,3,2)

3 (2,3,1)

4 (1,2,3)

...

And data in B is consistent with data in A for sessions 1-3, but data in B also shows the order. You want to keep all these information in a useful way?