# Combine Data in 2 Sheets like below

#### kcube17

##### New Member
2 years ago I used to use a macro to join data in 2 sheets like

in sheet 1 city names
in sheet 2 person names

and if i use macro it used to combine city name and person name like below

sheet 3
city 1 person 1
city 1 person 2
till last person
city 2 person 1
city 2 person 2

Now I lost that macro. Can any one help me.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
can any 1 help?

any suggestions

i got the code but unable to do it
------------
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Sub loops()

Dim n_height, n_width, c As Integer

With ThisWorkbook.Sheets("Sheet1")

n_height
= .Cells(Rows.Count, 1).End(xlUp).Row 'Assuming height is in column A
n_width
= .Cells(Rows.Count, 2).End(xlUp).Row 'Assuming width is in column B

c
= 2

For i = 2 To n_height
For j = 2 To n_width
.Range("D" & c).Value = .Range("A" & i).Value 'Prints heights in column D
.Range("E" & c).Value = .Range("B" & j).Value 'Prints widths in column E
c
= c + 1
Next j
Next i

End With

End Sub
----------------
it shows error at
</code>With ThisWorkbook.Sheets("Sheet1")

Where in sheet 1 is the city name is it column "A"
Where in sheet 2 is the person name is it column "A"
And where do you want the combined result to be put?

If you want a formula try this and then fill down
=Sheet2!A1&" "&Sheet1!A1

Assuming your data is in Column "A" on Both sheets
Try this
Code:
``````Sub Combine_Me()
Application.ScreenUpdating = False
Dim i As Integer
Dim Lastrow As Long
Sheets("Sheet1").Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
Sheets("Sheet3").Cells(i, 1).Value = Cells(i, 1).Value & "  " & Sheets("Sheet2").Cells(i, 1).Value
Next
Application.ScreenUpdating = True
End Sub``````

thanks for reps but that's not what i want

sheet1

<colgroup><col></colgroup><tbody>
</tbody>

<colgroup><col></colgroup><tbody></tbody>

Sheet2
 Name A B C D

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

and output like

 Achalpur A Achalpur B Achalpur C Achalpur D Achhnera A Achhnera B Achhnera C Achhnera D

<colgroup><col><col></colgroup><tbody>
</tbody>

Assuming your data is in Column "A" on Both sheets
Try this
Code:
``````Sub Combine_Me()
Application.ScreenUpdating = False
Dim i As Integer
Dim Lastrow As Long
Sheets("Sheet1").Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
Sheets("Sheet3").Cells(i, 1).Value = Cells(i, 1).Value & "  " & Sheets("Sheet2").Cells(i, 1).Value
Next
Application.ScreenUpdating = True
End Sub``````
thanks once again

i got the solution
excel - How to repeat all column values for each row in another column - Stack Overflow

Replies
4
Views
205
Replies
13
Views
393
Replies
19
Views
648
Replies
1
Views
123
Replies
16
Views
568

1,203,329
Messages
6,054,757
Members
444,748
Latest member
knowak87

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back