transposing data excell 2008

[drkimble]

I have a long list of husbands and wives where for example:

HUSBAND 1's last name is in cell H2
HUSBAND 1's first name is in cell I2
HUSBAND 1's age is in cell K2

On the next row is that husband's wife:
WIFE 1's last name is in cell H3
WIFE 1's first name is in cell I3
WIFE 1's age is in cell K3

Then a new couple begins in rows 4&5, 6&7, 8&9, etc.

I want a formula so that the data in:
H3 gets repeated in U2
I3 gets repeated in V2
K3 gets repeated in W2

Then similarly:
H2 gets repeated in U3
I2 gets repeated in V3
K2 gets repeated in W3

Then for the next couple with a new HUSBAND in the next even row and his WIFE in the next odd row the same thing happens:

H5 gets repeated in U4
I5 gets repeated in V4
K5 gets repeated in W4

H4 gets repeated in U5
I4 gets repeated in V5
K4 gets repeated in W5

Is there a formula I can put on top of the U, V, and W columns that I can drag down and watch magically the whole autofill with the data I have entered in the H, I, K columns?

As an additional challenge in the H column only the text will be entered in "CAPS" and I'd like it to be imported into the U column in "Title Case"

Thank you.

 

[sizzling]

Google Excel Index and Match. Think that will help