moving data from one cell to another

[mgm]

I copied and pasted many lines into a spreadsheet that are in this sort of
order:
5/6/2004 Data
5/6/2004 Data
#1
5/6/2004 Data
#2
5/6/2004 Data
5/6/2004 Data
#3



Since you can't see the lines, i'll explain - First column contains a date,
second column contains data, then below the Data row, for some, there is a #
in the third column. I would like to move the data over to the third column
and insert the # into the second column (where the Data was). Or, Insert a
new column between the first and second, and insert the # on the row above
in that column, then to delete all empty rows (see below)... is this
possible? (sounds difficult)

5/6/2004 Data
5/6/2004 #1 Data
5/6/2004 #2 Data
5/6/2004 Data
5/6/2004 #3 Data

 

[Ian]

I'm assuming the dates in the first column are in order and that the data
starts on row 1

Create a new column between the date and the data as you suggested.
Enter =IF(LEFT(A2,1)="#",A2,"") in B1, then copy it down the column as far
as the data goes. This will copy the # cells in column A to the row above in
column B.
Select column B, copy it then paste special>values. This will replace the
formulae in column B with the results of the formulae.
Select all the data and sort according to date.

HTH

 

[Ian]

Sorry I forgot to add that this will also bunch the # cells in column A
together which will make it easy to select and delete the rows.

 

[krcowen]

1. Insert a column between the data and the data.
2. Put a formula in B1 like =if(a2>35000,"",a2)
3. Copy the formula to the end of your data
4. Convert the new column B to values
5. Sort by column A and delete all the rows that don't have dates in
column A

The tricky part will be the formula in step 2. You need a formula
that will distinguish between dates in column A in the row immediately
below and your other possible data in column A of the row immediately
below, which needs to be moved up a row and over to column B. If the
numbers are low, like 1, 2, 3 as in your example and the dates are all
after 1996, the formula above should work. You may have to play with,
and complicate, the formula to accomodate whatever it is you have in
column A that is not dates.

Good luck.


Ken
Norfolk, Va

 

[mgm]

It worked! Thanks!

Sorry I forgot to add that this will also bunch the # cells in column A
together which will make it easy to select and delete the rows.

 

[mgm]

One more question - how do you convert to values? I copied and pasted to
new sheet and pasted 'values' only, but would like to know how to convert.

Thanks!
mgm

 

[Ian]

By pasting as Value you are automatically removing the formula leaving just
the result (ie the value). That is all the conversion you need.