column number from one cell to the other

[donlincolnef]

Hello

I’m looking for a macro to copy records and phone number. The row without phone numbers (referring to COLUMN J) have lots more columns and lot more data I need a macro to transfer or copy the phone number from rows below into to the row above (master record) in case there are multiple child records, and only 1 master record, the macro should insert a row and copy the master row into it.

I suppose the macro should check the numeric and just first alphabetic portion of the address to find the match (e.g. 11213 FOSTER ) since some address have apts and that would throw the macro off.

Then the macro would count how many times the record appeared below and then create the master record and then copy the child records phone number into them.

This is a real challenging one, any help would be greatly appreciated.

If all else failed and it can’t be done then the macro should just copy the very first child record phone number into the master record.

COLUMN F= ADDRESS
G=CITY
H=STATE
I=ZIP
J=PHONE NUMBERS

Below is the DUMMY DATA.



F G H I j


1121 LOTEBUSH LN BARSTOW CA 91601 3679-H5
11213 FOSTER RD NORWALK, CA 91601 736-F2
11213 FOSTER RD NORWALK, CA 91601 5624091827
11213 FOSTER RD NORWALK, CA 91601 5628638970
11215 HATTERAS ST NORTH HOLLYWOOD CA 91601 562-J1
11215 HATTERAS ST N HOLLYWOOD, CA 91601 8185087796
11215 HATTERAS ST NORTH HOLLYWOOD, CA 91601 8186237910
11216 DEBRA AVE SAN FERNANDO CA 91344
11216 S FIGUEROA LOS ANGELES, CA 9134 704-B6
11216 S FIGUEROA ST LOS ANGELES, CA 9134 3232421161
11216 S FIGUEROA ST, APT 2 LOS ANGELES, CA 9134 3234189687
11216 S FIGUEROA ST, APT 3 LOS ANGELES, CA 9134 3237561280
11216 S FIGUEROA ST, APT 7 LOS ANGELES, CA 9134 3237579304
11216 S FIGUEROA ST, APT 8 LOS ANGELES, CA 9134 3237799190
11226 FORBES AVE GRANADA HILLS CA 91344 501-D1
11248 TERRA VISTA PKWY 83 RANCHO CUCAMONGA CA 91730 573-D7

[protonLeah]

Please post a sample workbook with several of each variation of the records.

[donlincolnef]

Hello

I’m looking for a macro to MOVE only PHONE NUMBERS from the row below (CHILD RECORD which appears several times in some cases) to the row above (MASTER RECORD)

The top row (or the first occurrence of the record) contains the master record and it has columns range of from A to BB

The child records rows have lots less columns.

The macro should count how many times the CHILD Record appear and copy the MASTER RECORD that many time and then MOVE the PHONE NUMBERS from the CHILD record into the MASTER RECORD (only the PHONE NUMBERS)

The address appears in COLUMN F and the PHONE NUMBERS in COLUMN J

The only way I can think of is to focus on COLUMN F and compare row by row and look for equal address match, once found then count how many time it appeared and then copy the master that many time (entire row) and then MOVE the numbers from child record in to master.

If there is no duplicate address for the record then it should just delete the entire row.

e.g. 762 morita only appeared once so that one should be deleted

7619 kengard the 1st record is the master record so the number should be MOVED from the row below

761 TERMINAL the CHILD RECORD appeard 6 times, so the master record (the entire row) should have a total of 6 copies and then MOVE the phone number from the CHILD RECORD.


Any help would be greatly appreciated


Thanks.


Here is the DUMMY DATA.
=====================

Data shows 5 columns
Column F=address
Column g=city state zip
Column h=state
Column I=zip
Column j=phone numbers



762 MORITA DR CORONA CA 92879 744-A2
7619 KENGARD AVE WHITTIER CA 90606 677-A7
7619 KENGARD AVE Whittier, Ca 90606-2440 CA 591000 5623258331
761 TERMINAL ST Los Angeles, Ca 90021-1121 CA 90605 707-G2
761 TERMINAL ST Los Angeles, Ca 90021-1121 CA 374381 2136238560
761 TERMINAL ST Los Angeles, Ca 90021-1121 CA 374381 2136247415
761 TERMINAL ST Los Angeles, Ca 90021-1100 CA 374381 2134880271
761 TERMINAL ST Los Angeles, Ca 90021-1100 CA 374381 2135720121
761 TERMINAL ST Los Angeles, Ca 90021-1100 CA 374381 2136231810
761 TERMINAL ST Los Angeles, Ca 90021-1100 CA 374381 2136275045
7607 S HALLDALE AVE LOS ANGELES CA 90047 703-J1
7607 S HALLDALE AVE Los Angeles, Ca 90047-2516 CA 450000 3237511636
7606 YANKEY ST DOWNEY CA 90242 705-H6
7605 TEA BERRY WAY SACRAMENTO CA 95828 338-F3
7605 NORTHEAST CIR CITRUS HEIGHTS CA 95610 259-H6
7604 WINDSONG PL RANCHO CUCAMONGA CA 91730 573-B7
7601 LEHIGH PL WESTMINSTER CA 92683 827-J4
7601 LEHIGH PL Westminster, Ca 92683-6200 CA 517000 7148943591