How could I do this...thanks in advance

[mrcrappy]

Hi Everyone,

Wondering if anyone could help.

I have 2 sheets - Sheet A and B

Sheet A has a single column list of numbers 1,2,3.....

Sheet B has a single column of letters a,b,c,d,e


Is there a way i can merge the 2 sheets so i end up with a new sheet or the information in Sheet A to look like:

column 1 column 2
1 a
1 b
1 c
1 d
1 e
2 a
2 b
2 c
2 d
2 e


Hope you catch the drift.

Thanks again!!

 

[VoG]

In Sheet1 A1

=Sheet2!A1

and copy the formula down.

 

[mrcrappy]

Thanks VOG, but perhaps I wasn't too clear.

Basically I have a list in sheet 1 of unique values.
In sheet 2 I have a list of tasks eg. task 1, task 2, task 3 etc.

I wanted to merge the 2 so i ended up with each unique value on a seperate row with a corresponding task.

eg.

001 task1
001 task2
001 task3
002 task1
002 task2
002 task3
003 task1
003 task2
003 task3
etc

 

[VoG]

Try this macro

Sub MakeList()
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim LR1 As Long, LR2 As Long
Dim i As Long, j As Long, k As Long
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set ws3 = Sheets.Add
LR1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
LR2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR1
    For j = 1 To LR2
        k = k + 1
        ws3.Cells(k, 1).Value = ws1.Cells(i, 1).Value
        ws3.Cells(k, 2).Value = ws2.Cells(j, 1).Value
    Next j
Next i
End Sub

 

[mrcrappy]

VoG you legend!!!! thank you so much.

However, it seems to be creating 2 extraneous rows after each group of tasks.
Wish I understood the macro more but any chance of highlighting where the issue may be?


Thanks for your time.

 

[VoG]

I think you must have some extraneous spaces in apparently empty cells on the input sheets. Try selecting the first few blank rows at the end of your input sheets and pressing the Delete key.

 

[mrcrappy]

so stupid! ....Thank you again mate, spent so long trying to figure this out myself

 

[VoG]

In response to your PM, to include columns A and B from Sheet1 try

Sub MakeList()
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim LR1 As Long, LR2 As Long
Dim i As Long, j As Long, k As Long
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set ws3 = Sheets.Add
LR1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
LR2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR1
    For j = 1 To LR2
        k = k + 1
        ws3.Cells(k, 1).Value = ws1.Cells(i, 1).Value
        ws3.Cells(k, 2).Value = ws1.Cells(i, 2).Value
        ws3.Cells(k, 3).Value = ws2.Cells(j, 1).Value
    Next j
Next i
End Sub