• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 46
  • Last Modified:

Move contents of selected cells to same row into next column adjacent

1.  In column A there is a list of suburb names, state abbreviations, postcodes.
2.  There are also some rows that have only the state abbreviation and postcode.

I want to move the state abbreviation and postcode to the cell in the next column immediately adjacent, on the same row.
Theses are the state abbreviations VIC, TAS, NSW, SA, WA, QLD, ACT

See example of original and required in attached file.
EE-Postcodes-problem-1.xlsx
0
gregfthompson
Asked:
gregfthompson
  • 3
  • 2
1 Solution
 
als315Commented:
You can use macro like:
Sub move_PO()
Dim i As Long
Dim j As Integer
Dim ws As Object
Dim PO
Set ws = ActiveSheet
PO = Array("VIC", "TAS", "NSW", "SA", "WA", "QLD", "ACT")
i = 2
With ws
  Do Until .Cells(i, 1) = "" And .Cells(i, 3) = ""
    For j = 0 To UBound(PO)
        If InStr(1, .Cells(i, 1), PO(j) & "", vbBinaryCompare) = 1 Then
            .Cells(i, 3) = .Cells(i, 1)
            .Cells(i, 1) = ""
            Exit For
        End If
    Next j
    i = i + 1
  Loop
End With
End Sub

Open in new window

Assign it to some key (in attached sample it is assigned to CTRL+SHIFT+A) and run on your sheet
EE-Postcodes-problem-1.xlsm
0
 
gregfthompsonAuthor Commented:
Thanks.

It worked for some.

But there are still lots that did not move.

A larger list is attached.
EE-State-abbreviation-problem.xlsm
0
 
Gerald ConnollyCommented:
If this is a one off you could just use the text-2-columns function (on the Date Tab) in Excel and then manually edit the exceptions, then use the string join to give you a single cell (ie =c1 & " " & d1), then copy and paste special (Values)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
als315Commented:
I didn't expect empty rows. Try this function:
Sub move_PO()
Dim i As Long, LastRow As Long
Dim j As Integer
Dim ws As Object
Dim PO
PO = Array("VIC", "TAS", "NSW", "SA", "WA", "QLD", "ACT")
Set ws = ActiveSheet
With ws
.UsedRange 'Refresh UsedRange
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

i = 2
  Do While i <= LastRow
    For j = 0 To UBound(PO)
        If InStr(1, .Cells(i, 1), PO(j) & " ", vbBinaryCompare) = 1 Then
            .Cells(i, 3) = .Cells(i, 1)
            .Cells(i, 1) = ""
            Exit For
        End If
    Next j
    i = i + 1
  Loop
End With
End Sub

Open in new window

EE-State-abbreviation-problem-1.xlsm
0
 
gregfthompsonAuthor Commented:
Thanks.

That is brilliant.

Can you do a variation that moves the contents of any cell that does not contain a number, into the cell on the right on the same row?
0
 
gregfthompsonAuthor Commented:
Thanks heaps.

I've managed to work it out.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now