Move data from right 2 column to left column.

[donlincolnmre2]

Hello

I'm looking for a macro that will move 9 digit numbers from column H and I to Column G.

Below is the dummy data.

The data can be seen here in the formatted format.

https://docs.google.com/document/d/1RRIfYb6UqQKAFtAV9EurWRITybjU5hX5h0nYce36sbA/edit?hl=en_US

any help would be greaty appreciated.

Thanks.



colum G H I
======== ======= ========
9519290759 N
woodland 5304061176 N
REDONDO BEACH 3103795756 N
6503590602 N
9164421516 N
9163929631 N
9162949820 N
FAIR OAKS 9169888790 N
5302691239 N
LOS ANGELES 3106492634 N
SACRAMENTO 9163490333 N
9164184251 N
7602897799 N
9163769197 N
9164554192 N
4154531610 N
9167228248 N
9164081171 N
9163666199 N
ca alameda 5107420733
ca alameda 5103861997

 

[Rick Rothstein]

Does this macro do what you want?

Sub MoveColumnsHIover()
  Dim A As Range
  For Each A In Columns("I").SpecialCells(xlCellTypeConstants).Areas
    A.Offset(, -1).Resize(, 2).Copy A.Offset(, -2)
    A.Clear
  Next
End Sub

 

[donlincolnmre2]

Hello Rick

It did moved column I, but left column H,

here is the data format.

https://docs.google.com/document/d/1.../edit?hl=en_US


can you double check and see what's going on. Really appreciate your help

Thanks.

 

[Rick Rothstein]

That links appears to be dead now, but I looked at the link you posted earlier and used that data in my tests for the macro... the macro performed correctly for me. Let me ask, just to be sure though... your cells are filled with constants, not formulas, correct?

 

[donlincolnmre2]

yeah they are just constant numbers

here is the link again

https://docs.google.com/document/d/1RRIfYb6UqQKAFtAV9EurWRITybjU5hX5h0nYce36sbA/edit?hl=en_US

 

[Rick Rothstein]

With you data in Columns G:I, this is what I get after I run the code I posted...

    G         H
----------   ---
9519290759    N
5304061176    N
3103795756    N
6503590602    N
9164421516    N
9163929631    N
9162949820    N
9169888790    N
5302691239    N
3106492634    N
9163490333    N
9164184251    N
7602897799    N
9163769197    N
9164554192    N
4154531610    N
9167228248    N
9164081171    N
9163666199    N
alameda       5107420733
alameda       5103861997

Isn't this the final format you were after?

 

[donlincolnmre2]

well

The macro should look for 9 digit phone numbers in column H and I

if it find 9 digits in column H then move that into Column G but in same row

if it find 9 digits in Column I then move that into Column G but in the same row

here is the link with those 3 columns

https://docs.google.com/document/d/1.../edit?hl=en_US

hope this one clarifies

Thanks.

 

[Rick Rothstein]

Your link is dead again. Hopefully, this link, when you revive it, will show the expected output for the sample data you posted earlier so I can see exactly what your words are describing.

By the way, you are saying 9-digit phone numbers, but the numbers in the sample data you posted all have 10 digits in them. Can you explain that and also tell us if you can have numbers that are not 9 (or is it 10) digits long mixed into those columns? In other words, if there is a number in Columns H or I, then that is a phone number that you want to move.

 

[donlincolnmre2]

Here is the link again

https://spreadsheets.google.com/spr...-1BKedHlIQ2w4ZE0zT3Z0a1g2RDR0cjNiSVE&hl=en_US

sorry about the digit, instead of 9 its a 10 digit phone number

its just a 10 digit phone number that i want to move from column H and I into G

hopefully its claraifed now, the above link shows actual columns.

thanks.