Move data from one cell to another

[bradjedis]

Greetings,

I have a file with 1 million + rows. I have data in 4 col's that needs moved if a certain condition is met.

Col B if blank, ignore and proceed
If Data is present in col B, and it equals "First", then data remains in col B
if data is present in col B, and it equals "second", move to col C, same row
if data is present in col B, and it equals "third", move to col D, same row
if data is present in col B, and it equals "fourth", move to col E, sale row

Cycle thru until end of data.

Thanks,
Brad

[HansV]

Try this. I don't know how long it'll take.

Code: Select all

Sub MoveData()
    Dim r As Long
    Dim v
    v = Range("B1:E1048576").Value
    For r = 1 To 1048576
        Select Case LCase(v(r, 1))
            Case "second"
                v(r, 2) = "second"
                v(r, 1) = ""
            Case "third"
                v(r, 3) = "third"
                v(r, 1) = ""
            Case "fourth"
                v(r, 4) = "fourth"
                v(r, 1) = ""
        End Select
    Next r
    Range("B1:E1048576").Value = v
End Sub

[bradjedis]

Hans,

When I substitute the v(r, 2) = "second" word second, with the real text, it is not processing the move. the rea data is text in this format: XXXX_XXX_XXXXXXX=XXX_XXXX_XXX The text will be uppercase.

Case "second"
v(r, 2) = "second"
v(r, 1) = ""

Thoughts?

[HansV]

Could you attach a small sample workbook?

[bradjedis]

Here is a sample

[HansV]

I'm really confused now. Can you explain how this relates to your question in the first post. I don't see "first", "second", "third" or "fourth" anywhere.

[bradjedis]

the terms First, Second, Third, Fourth are generic. I cannot post the real words...

[bradjedis]

in this sample, the XXXX_XXX_XXXXXXX=XXX_XXXX_XXX would move to col C as if it were the term Second

[HansV]

I'm sorry, I don't understand.

[bradjedis]

could it be as simple as upper vs. lower case?

[bradjedis]

I played around with upper vs. lower and that seems to be the problem so I will continue to mess around and ping back if I fail..

[snb]

Why sending 2 empty worksheets ?

In your sample file:
- empty column C (except its header)
- use

Code: Select all

Sub M_snb()
  sn = Sheet1.Cells(1).CurrentRegion
  
  For j = 1 To UBound(sn)
    If InStr("  CDE", Left(sn(j, 2), 1)) Then sn(j, InStr("  CDE", Left(sn(j, 2), 1))) = sn(j, 2)
  Next
  
  sheet1.Cells(20, 1).Resize(UBound(sn), UBound(sn, 2)) = sn
End Sub