how to do this in excel

[banme]

i have sheet with data . i want to combine column A values with Column B values with this condition ..

IF sheet one have these values in Column A and Column B

|----------------------------|--------------------------|
|                Cell A                |        Cell B                     |
|----------------------------|--------------------------|
|             Name 1                |         Last Name 1           |
|----------------------------|--------------------------|
|             Name 2                |         Last Name 2           |
|----------------------------|--------------------------|
|             Name 3                |         Last Name 3           |
|----------------------------|--------------------------|
|             Name 4                |         Last Name 4           |
|----------------------------|--------------------------|
|             Name 5                |         Last Name 5           |
|----------------------------|--------------------------|

then need values in sheet 2 like this

|----------------------------------------|
|                Cell A                                 |   
|----------------------------------------|
|             Name 1 -  Last Name 1            |
|----------------------------------------|
|             Name 1 -  Last Name 2            |
|----------------------------------------|
|             Name 1 -  Last Name 3            |
|----------------------------------------|
|             Name 1 -  Last Name 4            |
|----------------------------------------|
|             Name 1 -  Last Name 5            |
|----------------------------------------|

|----------------------------------------|
|                Cell B                                 |   
|----------------------------------------|
|             Name 2 -  Last Name 1            |
|----------------------------------------|
|             Name 2 -  Last Name 2            |
|----------------------------------------|
|             Name 2 -  Last Name 3            |
|----------------------------------------|
|             Name 2 -  Last Name 4            |
|----------------------------------------|
|             Name 2 -  Last Name 5            |
|----------------------------------------|

|----------------------------------------|
|                Cell C                                 |   
|----------------------------------------|
|             Name 3 -  Last Name 1            |
|----------------------------------------|
|             Name 3 -  Last Name 2            |
|----------------------------------------|
|             Name 3 -  Last Name 3            |
|----------------------------------------|
|             Name 3 -  Last Name 4            |
|----------------------------------------|
|             Name 3 -  Last Name 5            |
|----------------------------------------| and continue TO SAME NAME 5

i tried to find some other ways to do THIS . but not able to find any online tool to do this so i think excel is only option for this to do this . so can you please help me in this .

 

[WarfritLive]

=CONCATENATE($A$1," - ", B1)

Where A1 is name 1. You should be able to do the rest

 

[banme]

=CONCATENATE($A$1," - ", B1)

Where A1 is name 1. You should be able to do the rest

thanks for help . but i have 400 500 first name and 400 500 last name so it very helpful for me if any automatic way to do this . thanks

 

[seekerarcane]

For Cell A, in sheet 2, use this formula and drag it down;

=Sheet1!$A$1&"-"&Sheet1!B1


For Cell B, in sheet 2, use this formula and drag it down;

=Sheet1!$A$2&"-"&Sheet1!B1


For Cell C, in sheet 2, use this formula and drag it down;

=Sheet1!$A$3&"-"&Sheet1!B1


and so on.....

Here, operator "&" is used to concatenate or combine values of Column A, Dash(-), and ColumnB

 

[raman1667]

Hi banme,


PFB for the required macro for your query.

Sub ColumnMerger()


'
'
'Code by Ramandeep Singh
'
'


'Declaring variables
Dim LastRowColumnA, LastRowColumnB, i, j As Long
Dim RangeA, RangeB As Range
Dim ColumnAValue, ColumnBValue As Variant


With Sheets("Sheet1")
    
'Getting the last row number for column A and B
LastRowColumnA = .Range("A1").End(xlDown).Row
LastRowColumnB = .Range("B1").End(xlDown).Row
    
'Checking whether no record or single record exists in column A
If LastRowColumnA = 1048576 Then
MsgBox "Macro cannot run as Column A contain no record or single record"
Exit Sub
End If
    
'Checking whether no record or single record exists in column B
If LastRowColumnB = 1048576 Then
MsgBox "Macro cannot run as Column B contain no record or single record"
Exit Sub
End If
    
'Defining the range of column A and B
Set RangeA = .Range(.Range("A1"), .Range("A1").End(xlDown))
Set RangeB = .Range(.Range("B1"), .Range("B1").End(xlDown))


End With


'Inserting new sheet as the last sheet
Sheets.Add after:=Sheets(Sheets.Count)


i = 1


'Looping through all the values in column A
For Each ColumnAValue In RangeA
j = 1
'Looping through all the values in column B
For Each ColumnBValue In RangeB
        
'Generating the required string
Cells(j, i) = ColumnAValue & " - " & ColumnBValue
j = j + 1
Next
i = i + 1
Next


'Auto adjusting the size of the columns
Columns.AutoFit


End Sub

Regards
Ramandeep Singh

 

[banme]

thank you for help ...