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

B

BobMi

New Member
Joined
Feb 10, 2014
Messages
1
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
ABIK
LocationDateSample LabelPIT Tag #
1FC5-13003-282-020
1MC5-13010-012-602
2MC5-13003-282-260 & 003-290-865
3MC5-13075-063-375
4MC5-13003-302-263
(3MC5-13)075-063-375
1m2-11 cp001-002-003
5m2-11 cp002-003-004
6m2-11 cp004-005-006
7m2-11007-008-009

<tbody>
[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5/12/2013[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5/12/2013[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5/12/2013[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5/12/2013[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5/12/2013[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5/13/2013[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2/15/2011[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2/15/2011[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2/15/2011[/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2/15/2011[/TD]

[TD="align: right"][/TD]

</tbody>

Fig 2. Experimental Results

OPX
Sample LabelCollection DateT Average
(ng / dl)
1m2-11 cp
3MC5-13
5m2-11 cp
6m2-11 cp
7m2-11

<tbody>
[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]2/15/2011[/TD]
[TD="align: right"]4.68[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]5/12/2013[/TD]
[TD="align: right"]14.115[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]2/15/2011[/TD]
[TD="align: right"]6.67[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]2/15/2011[/TD]
[TD="align: right"]23.42[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]2/15/2011[/TD]
[TD="align: right"]39.77[/TD]

</tbody>

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

Fig 3. Properly aligned and matched.
ABIKOPX
LocationDateSample LabelPIT Tag #Sample LabelCollection DateT Average
(ng / dl)
1FC5-13003-282-020
1MC5-13010-012-602
2MC5-13003-282-260 & 003-290-865
3MC5-13075-063-3753MC5-13
4MC5-13003-302-263
(3MC5-13)075-063-375
1m2-11 cp001-002-0031m2-11 cp
5m2-11 cp002-003-0045m2-11 cp
6m2-11 cp004-005-0066m2-11 cp
7m2-11007-008-0097m2-11

<tbody>
[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5/12/2013[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5/12/2013[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5/12/2013[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5/12/2013[/TD]

[TD="align: right"]5/12/2013[/TD]
[TD="align: right"]14.115[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5/12/2013[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5/13/2013[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2/15/2011[/TD]

[TD="align: right"]2/15/2011[/TD]
[TD="align: right"]4.68[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2/15/2011[/TD]

[TD="align: right"]2/15/2011[/TD]
[TD="align: right"]6.67[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2/15/2011[/TD]

[TD="align: right"]2/15/2011[/TD]
[TD="align: right"]23.42[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2/15/2011[/TD]

[TD="align: right"]2/15/2011[/TD]
[TD="align: right"]39.77[/TD]

</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.
PIT Tag #Unique ID
010-012-602010-012-602
003-282-260 & 003-290-865001-002-003

<tbody>
[TD="align: center"]16[/TD]
[TD="align: right"]Sample[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

</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

ALM
PIT Tag #Unique ID
010-012-602010-012-602
003-282-260 & 003-290-865001-002-003
003-282-260001-002-003
003-290-865001-002-003
next year010-012-602010-012-602

<tbody>
[TD="align: center"]16[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]21[/TD]

</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.
ABCDEFGHIJKLMNOPQRS
FemaleMale
IDHormone AppmavgHormone BppmavgIDHormone AppmavgHormone Bppmavg
11F6-11 a6M6-11 a
11F6-11 b6M6-11 b

<tbody>
[TD="align: center"]36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FFFF00, align: right"]0.666[/TD]
[TD="bgcolor: #FFC000, align: right"]13.28[/TD]
[TD="align: right"]10.65[/TD]
[TD="align: right"][/TD]
[TD="align: right"]17.763[/TD]
[TD="align: right"]492.43[/TD]
[TD="align: right"]487.02[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.299[/TD]
[TD="align: right"]2.98[/TD]
[TD="align: right"]3.54[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6.719[/TD]
[TD="align: right"]186.26[/TD]
[TD="align: right"]203.06[/TD]

[TD="align: center"]39[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FFFF00, align: right"]0.402[/TD]
[TD="bgcolor: #FFC000, align: right"]8.01[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]17.373[/TD]
[TD="align: right"]481.61[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.412[/TD]
[TD="align: right"]4.11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7.931[/TD]
[TD="align: right"]219.86[/TD]
[TD="align: right"][/TD]

</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).


BCDEFGHIJKL
IDHormone AppmavgHormone Bppmavg
11F6-11
6M6-11

<tbody>
[TD="align: center"]42[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]43[/TD]

[TD="bgcolor: #FFFF00, align: right"]0.666[/TD]
[TD="bgcolor: #FFFF00, align: right"]0.402[/TD]
[TD="bgcolor: #FFC000, align: right"]13.28[/TD]
[TD="bgcolor: #FFC000, align: right"]8.01[/TD]
[TD="align: right"]10.65[/TD]
[TD="align: right"]17.763[/TD]
[TD="align: right"]17.373[/TD]
[TD="align: right"]492.43[/TD]
[TD="align: right"]481.61[/TD]
[TD="align: right"]487.02[/TD]

[TD="align: center"]44[/TD]

[TD="align: right"]0.299[/TD]
[TD="align: right"]0.412[/TD]
[TD="align: right"]4.11[/TD]
[TD="align: right"]7.931[/TD]
[TD="align: right"]3.54[/TD]
[TD="align: right"]6.719[/TD]
[TD="align: right"]7.93[/TD]
[TD="align: right"]186.26[/TD]
[TD="align: right"]219.86[/TD]
[TD="align: right"]203.06[/TD]

</tbody>

Thanks in advance for any help,

Bob
 
You must log in or register to reply here.

Similar threads

H
  • Solved
Creation of arrows for 2 family trees.
Replies
8
Views
414
harzer
H
H
  • Solved
Modify content of cells in column "F" according to the content of cells in column "A"
Replies
5
Views
551
igold
igold
tezza
  • Solved
INDEX MATCH TWO COLUMNS
Replies
7
Views
633
Peter_SSs
Peter_SSs
H
  • Solved
Count nonblank lines (between blank lines)
2
Replies
15
Views
1K
Alex Blakenburg
A
H
  • Solved
Count breeding birds by year and sex.
2
Replies
16
Views
1K
harzer
H

Share this page

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