Merging Colums

[Spatzz]

Hi i have a sheet with data in the following format

Column1 Column2 Column3 etc
Y
Y
Y
I need to changed the Y's to different values. In column1 Y needs to equal =
1 and in Y in column2 needs to equal 2 etc. Is it possible to change the
values other than having to change each Y indvidually? I have 700 rows!!!
I then need to merge the three rows so i have one column with the converted
data.

I'm new to excel and and can not see away through.

Help

 

[Ragdyer]

Do you actually want to change the cell contents to numbers, or do you just
wish to total the values that each column represents?

 

[Dave Peterson]

Select each column (one at a time)
edit|replace
what: y
with: 1 (or 2 or 3 ...)
replace all

=======
Or you could try this.
Select your range to change
edit|replace
what: y
with: =column()
replace all

If column1 is not A, you may have to use a formula like:
=column()-3
(if I was starting in column D)

 

[Spatzz]

Ragdyer,
thanks for getting back. I do need to change the cell contents. I'm trying
to recode so i can them move the data in to SPSS. I've changed the Y's as
suggested by Dave using edit replace. Any ideas how i can merge the resulting
three columns in to one?

 

[Spatzz]

Dave,
thanks.That has helped in me recoding the Y's. Any ideas how i can merge the
data in the resulting three columns in to 1?

 

[Dave Peterson]

If you have
1 2 3
or
1 3
or
2 3
or
3

And can end up with
123
13
23
3

you could use a formula like:
=a1&b1&c1

or
=if(a1<>"",a1," ")&if(b1<>"",b1," ")&if(c1<>"",c1," ")
to end up with
123
1 3
23
3

I'm not sure what you want.



=if(a1<>"

 

[Don Guillett]

You should always give before/after examples so we understand what you want.
IF? I understand what you want, this will copy the last column in any row to
column 1. Then just delete or clear all other columns.

Sub Makeonecolumn()
For i = 1 To 7 'change 7 to suit rows needed
Cells(i, 1) = Cells(i, "iv").End(xlToLeft)
Next i
End Sub

 

[Gord Dibben]

F5>Special>Blanks.

Edit>Delete>Shift cells left.


Gord Dibben MS Excel MVP

 

[RagDyeR]

I think Gord's idea is the easiest.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Ragdyer,
thanks for getting back. I do need to change the cell contents. I'm trying
to recode so i can them move the data in to SPSS. I've changed the Y's as
suggested by Dave using edit replace. Any ideas how i can merge the
resulting
three columns in to one?

 

[Dave Peterson]

Ahhhh.

To the OP: This will work nicely if the options are mutually exclusive. But if
you have any rows that have multiple "responses", then it may not do what you
want.

Another way if they're mutually exclusive:
=sum(a1:C1)
convert to values and delete the original columns.

 

[Gord Dibben]

I was looking at the original post which had only one Y per row.

Locked in visually<g>

Gord

 

[Dave Peterson]

I figured row 693 of that set of 700 rows was different <vbg>.

 

[Spatzz]

Thanks for all the posts. What i'm trying to achieve is to go from my orginal
position of :
Column1 Column2 Column3 etc
Y
Y
Y

to
Column1 Column2 Column3 Column
1
2
3

and finally to

Column1 Column2 Column3 etc
1
2
3

Does that make sense. I've managed to get to stage 2.

 

[Gord Dibben]

Try my earlier suggestion to move everything to the left.

Select column A:C

F5>Special>Blanks>OK

Edit>Delete>Shift cells left.


Gord

 

[Spatzz]

Gord,
i've tried your suggestion and the only problem i'm having is that for some
reason it is then copying the text i have in the adjoining colum in to the
resulting column!!

I'm ending up with the following

Column1 Column2 Column3 Column4
1 xxxxx
2 xxxxx
3 xxxxx

after selecting and delecting blanks it is producing

Column1
xxxxx1
xxxxx2
xxxxx3

I'm definetly only choosing the first three columns to identify and then
delete the blanks.

Spatzz

 

[Don Guillett]

Perhaps you should tell us ALL of your desires at the start for effective
help....................................

 

[Gord Dibben]

With your example I end up with

ColA ColB ColC ColD

1 xxxxxx
2 xxxxxx
3 xxxxxx


Gord

 

[Spatzz]

Gord,
thanks for your help.

With your example I end up with

ColA ColB ColC ColD

1 xxxxxx
2 xxxxxx
3 xxxxxx


Gord