data manipulation question

  • Thread starter Thread starter Blaster Master
  • Start date Start date
B

Blaster Master

I've got an almost 1800 line spreadsheet in Excel. I've got to manipulate
data in it for a client. I'm needing to do the following in Excel

1. begin searching at the top of the file
2. check the check / manipulate the contents of 3 adjacent columns, ie:

____________
| A | B | C |
----------------
| | X | Y |
----------------
| | X | |
----------------
| | | |
----------------
| | X | |
----------------
| | X | Y |
----------------

3. column A is always blank
4. if column C has data in it, then
4a. move column B data to column A
4b. move column C data to column B

____________
| A | B | C |
----------------
| X | Y | |
----------------
| | X | |
----------------
| | | |
----------------
| | X | |
----------------
| X | Y | |
----------------

5. continue searching from the current location until the end of the file

Any takers on this one? Otherwise, I'm gonna have to hand move 2 cells of
data almost 1700 times.

Thanks,


--

Brad S. Russell
Network / Systems Engineer
Datamax Micro
501-603-3077 (office)
 
I'm not very advanced in Excel so perhaps there are better ways - but I would
have done the following:

Create a new temporary spreadsheet to copy the values by the help of
functions. Then paste only the values into a final destination.

In temporary spreadsheat:

A1=
if(isblank(originalsheet!C1);"";originalsheet!B1)

B1=
if (isblank(originalsheet!C1);originalsheetB1;originalsheetC1)

C1=
don't enter anything. C1 should remain blank after the manipulation in all
cases if I understand you right.

Copy/drag the formula below the expected row number and you have a converted
list. Select the values and paste special (values only) to final destination.
 
Try this
Sub UntestedButShouldWork()
For i=1 to range(C65536").end(xlup).Row
if cells(i,3).value<>"" then
cells(i,1).value=cells(i,2).Value
Cells(i,2).Value=cells(i,3).value
Cells(i,3).clearcontenst
End If
Next
End Sub

Bob Umlas
Excel MVP
 
...or try a macro:

Sub moveBC()
Dim r As Long, lastrow As Long
With Worksheets("Sheet1")
lastrow = 1800 '<== change to suit
For r = 2 To lastrow
If .Cells(r, "C") <> "" Then
.Cells(r, "A") = .Cells(r, "B")
.Cells(r, "B") = .Cells(r, "C")
.Cells(r, "c") = ""
End If
Next r
End With
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top