How to match and align unsorted data and apply a unique identifier for sorting

[BobMi]

I'm trying to help organize a colleagues data in Excel 2010 on Windows 7 and have encountered 3 issues that could be very time-consuming to resolve manually. The data is from a multi-year study, only a few samples and months are shown.


The main issue: Sort & match in Excel, data from tables in Word with results in excel.

Step 1: Have imported the Word Table (using table to text, import, etc. - Thanks to the Forum) into Excel. I'm stuck at the next step on finding out how to automate the sort & matching.

The goal is being able to non-manually match the row with experimental results (Fig 2; column O) with the row with corresponding sample label on the data sheet (Fig 1, Column I). Goal is shown in Figure 3.

Fig1. Data sheet

	A	B	I	K
3	Location	Date	Sample Label	PIT Tag #
4
5
6	1	5/12/2013	1FC5-13	003-282-020
7	1	5/12/2013	1MC5-13	010-012-602
8	1	5/12/2013	2MC5-13	003-282-260 & 003-290-865
9	1	5/12/2013	3MC5-13	075-063-375
10	1	5/12/2013	4MC5-13	003-302-263
11	1	5/13/2013	(3MC5-13)	075-063-375
12	1	2/15/2011	1m2-11 cp	001-002-003
13	1	2/15/2011	5m2-11 cp	002-003-004
14	1	2/15/2011	6m2-11 cp	004-005-006
15	1	2/15/2011	7m2-11	007-008-009

<tbody>
</tbody>

Fig 2. Experimental Results

	O	P	X
3	Sample Label	Collection Date	T Average
4			(ng / dl)
5
6	1m2-11 cp	2/15/2011	4.68
7	3MC5-13	5/12/2013	14.115
8	5m2-11 cp	2/15/2011	6.67
9	6m2-11 cp	2/15/2011	23.42
10	7m2-11	2/15/2011	39.77

<tbody>
</tbody>

Goal - align, sort and match rows by sample label (match column I with column O).

Fig 3. Properly aligned and matched.

	A	B	I	K	O	P	X
3	Location	Date	Sample Label	PIT Tag #	Sample Label	Collection Date	T Average
4							(ng / dl)
5
6	1	5/12/2013	1FC5-13	003-282-020
7	1	5/12/2013	1MC5-13	010-012-602
8	1	5/12/2013	2MC5-13	003-282-260 & 003-290-865
9	1	5/12/2013	3MC5-13	075-063-375	3MC5-13	5/12/2013	14.115
10	1	5/12/2013	4MC5-13	003-302-263
11	1	5/13/2013	(3MC5-13)	075-063-375
12	1	2/15/2011	1m2-11 cp	001-002-003	1m2-11 cp	2/15/2011	4.68
13	1	2/15/2011	5m2-11 cp	002-003-004	5m2-11 cp	2/15/2011	6.67
14	1	2/15/2011	6m2-11 cp	004-005-006	6m2-11 cp	2/15/2011	23.42
15	1	2/15/2011	7m2-11	007-008-009	7m2-11	2/15/2011	39.77

<tbody>
</tbody>

Second issue:

In Fig 3 above for Column K, there should be only one 9 digit number. However some PIT Tags # were mistakenly applied two or three times. What I would like to for the cells with multiple #'s is assign a unique 9-digit ID (single # ) like below.

Part 2. Fig 1.

16	Sample	PIT Tag #	Unique ID
17	1	010-012-602	010-012-602
18	2	003-282-260 & 003-290-865	001-002-003

<tbody>
</tbody>

The second part of this issue is that should the PIT Tag# (correct or mistaken) be encountered later; then correctly apply the Unique ID.


Example:
If the PIT Tag# encountered on row 200 is 003-282-260 ---> use Unique ID 001-002-003
... PIT Tag# on row 3000 is 003-290-865 ---> use Unique ID 001-002-003
However, should PIT Tag# 010-012-602 be encountered later ----> use Unique ID 010-012-602

	A	L	M
16		PIT Tag #	Unique ID
17	1	010-012-602	010-012-602
18	2	003-282-260 & 003-290-865	001-002-003
19	3	003-282-260	001-002-003
20	4	003-290-865	001-002-003
21	next year	010-012-602	010-012-602

<tbody>
</tbody>

Third Issue:
Another colleague has their data in excel in a row/column format on around 100 or more worksheets that need to be integrated with the above data : The a and b below refer to repetitions.

Issue 3. Fig 1.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
36					Female											Male
37		ID	Hormone A	ppm	avg		Hormone B	ppm	avg			ID	Hormone A	ppm	avg		Hormone B	ppm	avg
38		11F6-11 a	0.666	13.28	10.65		17.763	492.43	487.02			6M6-11 a	0.299	2.98	3.54		6.719	186.26	203.06
39		11F6-11 b	0.402	8.01			17.373	481.61				6M6-11 b	0.412	4.11			7.931	219.86

<tbody>
</tbody>

In order to apply the solutions to the first two issues, I would like to find a non-manual way to transform the reps from a row/column format in Issue 3 Fig1. to a row-only format below (column C is rep A and column D is rep B for Hormone A).

	B	C	D	E	F	G	H	I	J	K	L
42	ID	Hormone A		ppm		avg	Hormone B		ppm		avg
43	11F6-11	0.666	0.402	13.28	8.01	10.65	17.763	17.373	492.43	481.61	487.02
44	6M6-11	0.299	0.412	4.11	7.931	3.54	6.719	7.93	186.26	219.86	203.06

<tbody>
</tbody>

Thanks in advance for any help,

Bob