CODEWITHSUNDEEP
vbaexcel
kcube17
kcube17

Reputation: 17

in excel how to repeat column for each row in another column

in excel there are city names in sheet 1, and in sheet 2 person names.

example

City

Achalpur
Achhnera
Adalaj
Adilabad

person name

Name
A
B
C
D

I want the result in sheet 3 like below

city 1 person 1
city 1 person 2
till last person
city 2 person 1
city 2 person 2

Achalpur    A
Achalpur    B
Achalpur    C
Achalpur    D
Achalpur    E
Achalpur    F
Achalpur    G

Upvotes: 0

Views: 533

Answers (1)

Ads
Ads

Reputation: 2184

Here is a function that will do it for you. You can modify it slightly, if you can be sure that there will be no empty rows between entries in your city list or the second list.

It also assumes that your data is in Sheet1 and Sheet2 with a header row.

One minor thing to modify, you might just need to set the s3RCtr back 5 for each time the loop finishes, otherwise you get a gap of 5 empty rows in your results.

I hope this helps. Sorry, I've got something urgent to look at atm.

Function DoStuff()

Dim s1ECtr, s1RCtr
Dim s2ECtr, s2RCtr
Dim s3RCtr

s1ECtr = 1
s2ECtr = 1

s1RCtr = 2
s3RCtr = 2

Do While s1ECtr < 5
    If (Sheet1.Cells(s1RCtr, 1) <> "") Then
        s2RCtr = 2
        Do While s2ECtr < 5
            If (Sheet2.Cells(s2RCtr, 1) <> "") Then
                Sheet3.Cells(s3RCtr, 1) = Sheet1.Cells(s1RCtr, 1) & " " & Sheet2.Cells(s2RCtr, 1)
            Else
                s2ECtr = s2ECtr + 1
            End If
            s3RCtr = s3RCtr + 1
            s2RCtr = s2RCtr + 1
        Loop
        s2ECtr = 0
    Else
        s1ECtr = s1ECtr + 1
    End If
    s1RCtr = s1RCtr + 1
Loop
End Function

Upvotes: 1

Related Questions