Hi,

I have a spreadsheet were I have list of accounts and they are catergorized
by location in the same column. for example in Column B

1001 (Account)
1002 (Account)
1003 (Account)
1004 (Account)
1005 (Account)
J101(Location)
1001 (Account)
1002 (Account)
1003 (Account)
1005 (Account)
J102 (Location)

so it will list the accounts and then the last item will be the location.
What I want to do is have the location in column a right next to the account.
Doea anyone have a macro that can go down the list put the location in front
of the account?

Thanks
Mascot

Assuming all your data has a space why don't you just use a formula like
the one below ??


=RIGHT(E1,LEN(E1)-FIND(" ",E1,1))&" "&LEFT(E1,FIND(" ",E1,1)-1)


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=564408

Assuming your locations all begin w/a letter (so it is not possible they
could be interpreted as numbers). After putting the location in the column
to the left of your data, this macro also deletes the rows in your data that
had the locations, so be sure to backup your data before trying. Also, you
have to select the data you want the macro to run on beforehand.

Sub test()
Dim rngCell As Range
Dim rngDelete As Range
Dim rngTemp As Range

If Selection.Columns.Count 1 Then End

For Each rngCell In Selection.Cells
If Not IsNumeric(rngCell.Value) Then
If rngTemp Is Nothing Then
rngCell.Copy Range(Selection.Cells(1)(1, 0), rngCell(0, 0))
Else: rngCell.Copy Range(rngTemp(2, 0), rngCell(0, 0))
End If
Set rngTemp = rngCell
If rngDelete Is Nothing Then
Set rngDelete = rngCell
Else: Set rngDelete = Union(rngCell, rngDelete)
End If
End If
Next rngCell

If Not rngDelete Is Nothing Then _
rngDelete.EntireRow.Delete

End Sub

"Mascot" wrote:

Hi,

I have a spreadsheet were I have list of accounts and they are catergorized
by location in the same column. for example in Column B

1001 (Account)
1002 (Account)
1003 (Account)
1004 (Account)
1005 (Account)
J101(Location)
1001 (Account)
1002 (Account)
1003 (Account)
1005 (Account)
J102 (Location)

so it will list the accounts and then the last item will be the location.
What I want to do is have the location in column a right next to the account.
Doea anyone have a macro that can go down the list put the location in front
of the account?

Thanks
Mascot

Another way you could do it is to use formulae

Enter in cell A1:

=IF(ISTEXT(B1),"",IF(ISTEXT(B2),B2,A2))
and copy down. If you want the data hardcoded, select column A and copy,
then Edit/Paste Special-values. After this, if you want to get rid of the
locations in your data in column B, use Autofilter to filter for blanks in
column A, delete the filtered rows, then turn off Autofilter.



"JMB" wrote:

Assuming your locations all begin w/a letter (so it is not possible they
could be interpreted as numbers). After putting the location in the column
to the left of your data, this macro also deletes the rows in your data that
had the locations, so be sure to backup your data before trying. Also, you
have to select the data you want the macro to run on beforehand.

Sub test()
Dim rngCell As Range
Dim rngDelete As Range
Dim rngTemp As Range

If Selection.Columns.Count 1 Then End

For Each rngCell In Selection.Cells
If Not IsNumeric(rngCell.Value) Then
If rngTemp Is Nothing Then
rngCell.Copy Range(Selection.Cells(1)(1, 0), rngCell(0, 0))
Else: rngCell.Copy Range(rngTemp(2, 0), rngCell(0, 0))
End If
Set rngTemp = rngCell
If rngDelete Is Nothing Then
Set rngDelete = rngCell
Else: Set rngDelete = Union(rngCell, rngDelete)
End If
End If
Next rngCell

If Not rngDelete Is Nothing Then _
rngDelete.EntireRow.Delete

End Sub

"Mascot" wrote:

Hi,

I have a spreadsheet were I have list of accounts and they are catergorized
by location in the same column. for example in Column B

1001 (Account)
1002 (Account)
1003 (Account)
1004 (Account)
1005 (Account)
J101(Location)
1001 (Account)
1002 (Account)
1003 (Account)
1005 (Account)
J102 (Location)

so it will list the accounts and then the last item will be the location.
What I want to do is have the location in column a right next to the account.
Doea anyone have a macro that can go down the list put the location in front
of the account?

Thanks
Mascot