How do I combine these columns into one?

[laredotornado]

Hi,

I'm using Excel 2003 for Win XP. In my worksheet, I have a header row
with the columns,

Brancolo Celina Colonial Dolo Erie

and in each row beneath, there is an "x" under the column to which the
row belongs. Note that each row has exactly one "x" (these are
headings for a product's company, and a product belongs to exactly one
company).

What I want instead is to have a single column, "Company", and for
each cell, have the word "Brancolo", "Celina", etc., depending on
where the "x" is located. Does anyone know how to combine my data to
match this?

Thanks, - Dave

 

[Dave Peterson]

Assuming that your data is in A1:Exxx...

Select your range but exclude row 1
Then hit F5 (or edit|goto|Special|constants)
click ok

The cells with X values should be the only cells selected.

If the activecell is in column A, then type:
=A$1
(use the column letter of the activecell if it's not in column A)

hit ctrl-enter to change all the selected cells (with X's) to this formula.

Each of the X's should be replaced with a formula that points at row 1 of its
column.

After that, you can select the entire range and convert it to values:
edit|copy
edit|paste values

 

[Don Guillett Excel MVP]

Hi,

I'm using Excel 2003 for Win XP.  In my worksheet, I have a header row
with the columns,

Brancolo   Celina   Colonial   Dolo   Erie

and in each row beneath, there is an "x" under the column to which the
row belongs.  Note that each row has exactly one "x" (these are
headings for a product's company, and a product belongs to exactly one
company).

What I want instead is to have a single column, "Company", and for
each cell, have the word "Brancolo", "Celina", etc., depending on
where the "x" is located.  Does anyone know how to combine my data to
match this?

Thanks, - Dave

If you have a in col a and b in col b etc, IF? I understand what you
want.
=IF(ISNA(MATCH("x",2:2,0)),"",INDEX($1:$1,,MATCH("x",2:2,0)))