Combine Data in 2 Sheets like below

[kcube17]

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.

 

[kcube17]

can any 1 help?

 

[kcube17]

any suggestions

 

[kcube17]

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")

 

[My Aswer Is This]

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?

 

[My Aswer Is This]

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

 

[My Aswer Is This]

Assuming your data is in Column "A" on Both sheets
Try this

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

 

[kcube17]

thanks for reps but that's not what i want

sheet1

City

Achalpur

Achhnera

Adalaj

Adilabad

Adityapur

<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>

 

[kcube17]

Assuming your data is in Column "A" on Both sheets
Try this

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