Aligning rows of data from multiple data sets in columns

Have table of data

2001 2002 2003
Name # Name # Name #
A 23 A 33 A 29
B 20 B 22 B 29
C 15 D 36 C 12
D 33 E 20 E 48
G 19 F 12 F 19


Each year's data includes a name (A, B, C, etc.) and a number.
However, not all years include all names. How can I match up names in
each row so all "A's" appear in one row, "B's" in one row
etc. along with their corresponding number.

Desired Outcome is:

2001 2002 2003
Name # Name # Name #
A 23 A 33 A 29
B 20 B 22 B 29
C 15 C 12
D 33 D 36
E 20 E 48
F 12 F 19
G 19

or the ideal outcome would be:

2001 2002 2003
Name # Name # Name #
A 23 A 33 A 29
B 20 B 22 B 29
C 15 C 12
D 33 D 36
E E 20 E 48
F F 12 F 19
G 19

I have tried to align these numbers in columns but it is not WYSIWYG.
If this does not make sense, I can email an actual excel table with the
data to better explain or detail the problem.

Thank you.

Here's one crack at this, using formulas ..

A sample construct is available at:
http://www.savefile.com/files/5667691
Aligning rows of data from multiple data sets in columns.xls

Assume source data in sheet: X, cols A to F, data from row3 to 7
(headers in rows 1 & 2)

> 2001 2002 2003
> Name # Name # Name #
> A 23 A 33 A 29
> B 20 B 22 B 29
> C 15 D 36 C 12
> D 33 E 20 E 48
> G 19 F 12 F 19

In a new sheet: Y,

In A3:
=OFFSET(X!$A$3,MOD(ROW(A1)-1,5),INT((ROW(A1)-1)/5)*2)

In B3:
=IF(A3=0,"",IF(COUNTIF($A$3:A3,A3)>1,"",ROW()))

In C3:
=INDEX(A:A,MATCH(SMALL(B:B,ROW(A1)),B:B,0))

In D3:
=IF(ISERROR(C3),"",CODE(LEFT(C3,1))+ROW()/10^10)

In E3:
=IF(ROW(A1)>COUNT(D:D),"",INDEX(C:C,MATCH(SMALL(D:D,ROW(A1)),D:D,0)))

In F3, array-entered (press CTRL+SHIFT+ENTER):
=IF($E3="","",IF(ISNA(INDEX(X!A$3:A$7,MATCH($E3,OFFSET(X!$A$3:$A$7,,INT((COLUMN(A1)-1)/2)*2),0))),"",INDEX(X!A$3:A$7,MATCH($E3,OFFSET(X!$A$3:$A$7,,INT((COLUMN(A1)-1)/2)*2),0))))
Copy F3 to K3

Then select A3:K3, fill down until zeros appear in col A, signalling
exhaustion of data
The desired results will be returned in cols F to K

Some explanations:
Col A extracts all names from each data set in X into a single col
(adapt the number "5" to suit the # of lines of source data in the
formula in A3)
Col B is a criteria col to "flag" uniques for col C to extract and
bunch uniques at the top (unsorted)
Col D is a criteria col (with tiebreaker) to "flag" the unsorted
uniques in col C for col E to autosort (only by the 1st alpha of names)
and bunch uniques at the top.
Cols F to K then matches the names in X (in each data set) against the
sorted list in col E to return the desired results (just adapt the
range: X!A$3:A$7 to suit, in the formula in the start cell F3)

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
e abor wrote:
> Have table of data
>
> 2001 2002 2003
> Name # Name # Name #
> A 23 A 33 A 29
> B 20 B 22 B 29
> C 15 D 36 C 12
> D 33 E 20 E 48
> G 19 F 12 F 19
>
>
> Each year's data includes a name (A, B, C, etc.) and a number.
> However, not all years include all names. How can I match up names in
> each row so all "A's" appear in one row, "B's" in one row
> etc. along with their corresponding number.
>
> Desired Outcome is:
>
> 2001 2002 2003
> Name # Name # Name #
> A 23 A 33 A 29
> B 20 B 22 B 29
> C 15 C 12
> D 33 D 36
> E 20 E 48
> F 12 F 19
> G 19
>
> or the ideal outcome would be:
>
> 2001 2002 2003
> Name # Name # Name #
> A 23 A 33 A 29
> B 20 B 22 B 29
> C 15 C 12
> D 33 D 36
> E E 20 E 48
> F F 12 F 19
> G 19
>
> I have tried to align these numbers in columns but it is not WYSIWYG.
> If this does not make sense, I can email an actual excel table with the
> data to better explain or detail the problem.
>
> Thank you.