Formatting a list of data into a row in Excel

[JohnnyCai]

I have data exported that requires to be reformatted like so:

x1 a
x1 b
x2 c
x2 d
to become
x1 a b
x2 c d
etc.
Can anyone steer me thru this.

[JLatham]

I set up some data like this starting in cell A1:
X1 a
X1 b
X2 c
X2 d
X1 e
X1 f
X1 g
X2 h

Then in E1 I put "X1" and in E2 I put "X2" as the start for the horizontal
format.
In cell F1, right next to E1, I entered this formula:

=IF(OFFSET($A$1,COLUMN(F1)-COLUMN($F1),0)=$E1,OFFSET($A$1,COLUMN(F1)-COLUMN($F1),1),"")

The big thing to notice in that is the cell reference that is the first
parameter to the OFFSET() functions. It must point at the first entry in
your current list that matches the value in E1. You'll have to manually
adjust that in each formula you enter to start a new transition. Here's the
formula I put into F2 (next to "X2" in the new list)

=IF(OFFSET($A$3,COLUMN(F2)-COLUMN($F2),0)=$E2,OFFSET($A$3,COLUMN(F2)-COLUMN($F2),1),"")

Notice that $A$1 had to be changed to $A$3 in both places in the formula for
it to work. I ended up with a table that looks like this (using _ to show
blank cells)

E F G H I J K L
1 X1 a b _ _ e f g
2 X2 c d _ _ _ h _

"JohnnyCai" wrote:

> I have data exported that requires to be reformatted like so:
>
> x1 a
> x1 b
> x2 c
> x2 d
> to become
> x1 a b
> x2 c d
> etc.
> Can anyone steer me thru this.