Copy column and rearrange rows

[Benjamin]

Hello,

I have a column in one spreadsheet that I would like to copy into
another spreadsheet, rearranging the rows to match up with a common
column.

Is this possible?

Thank you very much,

Ben

 

[JLatham]

No doubt it is possible, but the question becomes how to get it done.

At a very simple level you could copy the column and then use
Edit --> Paste Special with the "Transpose" option selected to 'rotate' the
column into a single row. But somehow I think there's more to it than this.

What defines a "common column"? Do you have row headings in column A on the
first sheet that could be matched to column headings on the other sheet? Like

First Sheet Second Sheet
A B A B
C
1 Name joe 1 Name Addr City
2 Addr 101 main st. 2 joe 101 M... NYC
3 City NYC
4 Name ....

or is your data in the first sheet simply grouped into separate constant
number of rows (as above perhaps groups of 3 rows [name, addr, city]) or more?

 

[Benjamin]

No doubt it is possible, but the question becomes how to get it done.

At a very simple level you could copy the column and then use
Edit --> Paste Special with the "Transpose" option selected to 'rotate' the
column into a single row.  But somehow I think there's more to it than this.

What defines a "common column"?  Do you have row headings in column A on the
first sheet that could be matched to column headings on the other sheet?  Like

First Sheet                                             Second Sheet
     A          B                                             A       B    
        C
1   Name     joe                                     1  Name  Addr      City
2   Addr      101 main st.                         2  joe     101 M...  NYC
3   City        NYC
4   Name    ....

or is your data in the first sheet simply grouped into separate constant
number of rows (as above perhaps groups of 3 rows [name, addr, city]) or more?

Hello,

I have a column in one spreadsheet that I would like to copy into
another spreadsheet, rearranging the rows to match up with a common
column.

Is this possible?

Thank you very much,

Ben
.- Hide quoted text -

- Show quoted text -

Hopefully this clarifies:

Currently,

First Sheet Second Sheet


1 Name DOB ID# Job 1 Name Addr City
Phone
2 Abe 2
Ben
3 Ben 3 Carla
4 Carla 4
Abe

I'd like to copy the Phone column from the second sheet to the first
sheet. Problem is that the name lists only contain some of the same
entries and some phone #'s are blank.

Thanks!

 

[Benjamin]

No doubt it is possible, but the question becomes how to get it done.

At a very simple level you could copy the column and then use
Edit --> Paste Special with the "Transpose" option selected to 'rotate'the
column into a single row.  But somehow I think there's more to it than this.

What defines a "common column"?  Do you have row headings in column Aon the
first sheet that could be matched to column headings on the other sheet?  Like

First Sheet                                            Second Sheet
     A          B                                              A       B    
        C
1   Name     joe                                     1  Name  Addr      City
2   Addr      101 main st.                         2  joe     101 M...  NYC
3   City        NYC
4   Name    ....

or is your data in the first sheet simply grouped into separate constant
number of rows (as above perhaps groups of 3 rows [name, addr, city]) or more?

- Show quoted text -

Hopefully this clarifies:

Currently,

First Sheet                                             Second Sheet

1   Name  DOB ID#  Job                 1  Name  Addr City Phone
2   Abe                                               2 Ben
3   Ben                                               3 Carla
4   Carla                                             4 Abe

I'd like to copy the Phone column from the second sheet to the first
sheet.  Problem is that the name lists only contain some of the same
entries and some phone #'s are blank.

Thanks!- Hide quoted text -

- Show quoted text -

Formatting got messed up for some reason, hopefully this works:

 

[JLatham]

Sorry, lost track of time! The code below should do the trick for you, copy
it into your workbook and change any of the Const values as required for the
setup in your workbook. You can then run the macro to get the job done.

To put the code into your workbook: start by making a copy of your
workbook, just in case. Then open the copy, Press [Alt]+[F11] to enter the
VB Editor. Use Insert --> Module to create a new code module and then copy
the code below and paste it into that code module. Make any changes to the
constants that you need to. Close the VB Editor, save the workbook and run
the macro to get the job done, either from Tools --> Macro --> Macros in
Excel 2003 & earlier, or from the Developer's tab in Excel 2007.

Sub CopyPhoneNumbers()
'copies data (phone numbers) from a
'specific column of Sheet2 to a specific
'column of Sheet1 when the name (or other data)
'in two more specified columns matches on both
'sheets
'
'Information about the sheet where we copy TO
'i.e., the destination sheet
Const destSheetName = "Sheet1"
Const destNameColumn = "A"
Const destPhoneColumn = "E"
'
'information about the sheet that we copy FROM
'i.e., the source sheet
Const sourceSheetName = "Sheet2"
Const sourceNameColumn = "A"
Const sourcePhoneColumn = "D"
'
'general variables to get the work done
'
Dim destWS As Worksheet
Dim destNamesList As Range
Dim anyDestName As Range
Dim srcWS As Worksheet
Dim srcNamesList As Range
Dim anySrcName As Range
'begin the work
Set destWS = ThisWorkbook.Worksheets(destSheetName)
'assumes data starts on row 2
Set destNamesList = destWS.Range(destNameColumn & _
"2:" & destWS.Range(destNameColumn & Rows.Count). _
End(xlUp).Address)

Set srcWS = ThisWorkbook.Worksheets(sourceSheetName)
'assumes data starts on row 2
Set srcNamesList = srcWS.Range(sourceNameColumn & _
"2:" & srcWS.Range(sourceNameColumn & Rows.Count). _
End(xlUp).Address)

' improve performance
Application.ScreenUpdating = False
'compare and copy as appropriate
'this does EXACT matches of names
For Each anySrcName In srcNamesList
For Each anyDestName In destNamesList
If anySrcName = anyDestName Then
destWS.Range(destPhoneColumn & _
anyDestName.Row) = _
srcWS.Range(sourcePhoneColumn & _
anySrcName.Row)
End If
Next
Next
'cleanup and housekeeping
Set srcNamesList = Nothing
Set srcWS = Nothing
Set destNamesList = Nothing
Set destWS = Nothing
MsgBox "Task has completed."
End Sub

No doubt it is possible, but the question becomes how to get it done.

At a very simple level you could copy the column and then use
Edit --> Paste Special with the "Transpose" option selected to 'rotate' the
column into a single row. But somehow I think there's more to it than this.

What defines a "common column"? Do you have row headings in column A on the
first sheet that could be matched to column headings on the other sheet? Like

First Sheet Second Sheet
A B A B
C
1 Name joe 1 Name Addr City
2 Addr 101 main st. 2 joe 101 M... NYC
3 City NYC
4 Name ....

or is your data in the first sheet simply grouped into separate constant
number of rows (as above perhaps groups of 3 rows [name, addr, city]) or more?

:
Hello,

I have a column in one spreadsheet that I would like to copy into
another spreadsheet, rearranging the rows to match up with a common
column.

Is this possible?

Thank you very much,

Ben
.- Hide quoted text -

- Show quoted text -

Hopefully this clarifies:

Currently,

First Sheet Second Sheet

1 Name DOB ID# Job 1 Name Addr City Phone
2 Abe 2 Ben
3 Ben 3 Carla
4 Carla 4 Abe

I'd like to copy the Phone column from the second sheet to the first
sheet. Problem is that the name lists only contain some of the same
entries and some phone #'s are blank.

Thanks!- Hide quoted text -

- Show quoted text -

Formatting got messed up for some reason, hopefully this works:

Currently,

First Sheet Second Sheet

1 Name DOB ID# Job 1 Name Addr City Phone
2 Abe 2 Ben
3 Ben 3 Carla
4 Carla 4 Abe

I'd like to copy the Phone column from the second sheet to the first
sheet. Problem is that the name lists only contain some of the same
entries and some phone #'s are blank.

.

 

[JLatham]

Benjamin, this can also be handled using a worksheet formula instead of VBA
code:

Assume this layout on Sheet1:

A B C D E
1 Name DOB ID# Job PHONE
2 Abe
3 Ben
4 Carla

and this layout on Sheet2:
A B C D
1 Name Addr City PHONE
2 Ben
3 Carla 123-4567
4 Abe 555-1212

Then back on sheet 1, in cell E2, put this formula (it should all be 1 line)

=INDEX(Sheet2!$A$1:$D$4,MATCH(Sheet1!$A2,Sheet2!$A$1:$A$4,0),MATCH(Sheet1!$E$1,Sheet2!$A$1:$D$1,0))

To pull it apart and help you modify it:
INDEX(Sheet2!$A$1:$D$4
The Sheet2!$A$1:$D$4 refers to the entire table on Sheet 2 from upper left
corner to lower right corner, and you need the $ signs to keep the reference
from changing as you fill the formula down the sheet later, then
MATCH(Sheet1!$A2,Sheet2!$A$1:$A$4,0)
Says to match the name in A2 on Sheet1 with a name in column A on Sheet2 and
return the row number for that match to use with the INDEX function. The ,0
says look for an exact match.

finally,
MATCH(Sheet1!$E$1,Sheet2!$A$1:$D$1,0)
Says match the contents of $E$1 on Sheet1 (the word PHONE) with an entry in
row 1 on Sheet2, to give us a column number to use for the INDEX formula.
Again, the ",0" says look for an exact match.

That formula will return either the phone number or a zero when the name is
matched. But if there is no match for the name, you get #N/A.

We can prevent the 0 and #N/A display by doing a little modification to the
original, basic formula (and again remember this is all one long line), and
we get this ugly brute that works and keeps your worksheet 'clean' looking:

=IF(ISNA(INDEX(Sheet2!$A$1:$D$4,MATCH(Sheet1!$A2,Sheet2!$A$1:$A$4,0),MATCH(Sheet1!$E$1,Sheet2!$A$1:$D$1,0))),"",IF(INDEX(Sheet2!$A$1:$D$4,MATCH(Sheet1!$A2,Sheet2!$A$1:$A$4,0),MATCH(Sheet1!$E$1,Sheet2!$A$1:$D$1,0))=0,"",INDEX(Sheet2!$A$1:$D$4,MATCH(Sheet1!$A2,Sheet2!$A$1:$A$4,0),MATCH(Sheet1!$E$1,Sheet2!$A$1:$D$1,0))))

No doubt it is possible, but the question becomes how to get it done.

At a very simple level you could copy the column and then use
Edit --> Paste Special with the "Transpose" option selected to 'rotate' the
column into a single row. But somehow I think there's more to it than this.

What defines a "common column"? Do you have row headings in column A on the
first sheet that could be matched to column headings on the other sheet? Like

First Sheet Second Sheet
A B A B
C
1 Name joe 1 Name Addr City
2 Addr 101 main st. 2 joe 101 M... NYC
3 City NYC
4 Name ....

or is your data in the first sheet simply grouped into separate constant
number of rows (as above perhaps groups of 3 rows [name, addr, city]) or more?

:
Hello,

I have a column in one spreadsheet that I would like to copy into
another spreadsheet, rearranging the rows to match up with a common
column.

Is this possible?

Thank you very much,

Ben
.- Hide quoted text -

- Show quoted text -

Hopefully this clarifies:

Currently,

First Sheet Second Sheet

1 Name DOB ID# Job 1 Name Addr City Phone
2 Abe 2 Ben
3 Ben 3 Carla
4 Carla 4 Abe

I'd like to copy the Phone column from the second sheet to the first
sheet. Problem is that the name lists only contain some of the same
entries and some phone #'s are blank.

Thanks!- Hide quoted text -

- Show quoted text -

Formatting got messed up for some reason, hopefully this works:

Currently,

First Sheet Second Sheet

1 Name DOB ID# Job 1 Name Addr City Phone
2 Abe 2 Ben
3 Ben 3 Carla
4 Carla 4 Abe

I'd like to copy the Phone column from the second sheet to the first
sheet. Problem is that the name lists only contain some of the same
entries and some phone #'s are blank.

.