Insert New Rows based on COUNTA()

[TheDPQ]

This might not be the right area however i am a newbie to Excel so i figure i'd start here. I have very limited experience with Excel so excuse me if i don't make sense.
Any Help In The Right Direction Would Be Awesome


I have a Column full of keywords, and i have a column full of Ad IDs... is there a way in a new sheet to insert the keywords in as many times as there are ads?

EDIT:
This needs to be dynamic. If i add/remove a keyword (or Ad) a keyword from sheet 1 it needs to reflect on sheet 2.

Sheet 1
Keysword | Ad IDs
Keyword 1 | 123
Keyword 2 | 456
xxxxxxxxx | 789

Sheet 2
Keysword | Ad IDs
Keyword 1 | 123
Keyword 1 | 456
Keyword 1 | 789
Keyword 2 | 123
Keyword 2 | 456
Keyword 2 | 789


I found COUNTA() Which lets me count how many keywords and ads i have, i'm just not sure what to do with those numbers now.

[Anne Troy]

If I've got you right, perhaps this will help?
http://www.officearticles.com/excel/...soft_excel.htm
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"TheDPQ" <[email protected]> wrote in
message news:[email protected]...
>
> This might not be the right area however i am a newbie to Excel so i
> figure i'd start here. I have very limited experience with Excel so
> excuse me if i don't make sense.
> ANY HELP IN THE RIGHT DIRECTION WOULD BE AWESOME
>
>
> I have a Column full of keywords, and i have a column full of Ad IDs...
> is there a way in a *new* sheet to insert the keywords in as many times
> as there are ads?
>
> SHEET 1
> Keyword 1 | 123
> Keyword 2 | 456
> xxxxxxxxx | 789
>
> SHEET 2
> Keyword 1 | 123
> Keyword 1 | 456
> Keyword 1 | 789
> Keyword 2 | 123
> Keyword 2 | 456
> Keyword 2 | 789
>
>
> I found COUNTA() Which lets me count how many keywords and ads i have,
> i'm just not sure what to do with those numbers now.
>
>
> --
> TheDPQ
> ------------------------------------------------------------------------
> TheDPQ's Profile:
> http://www.excelforum.com/member.php...o&userid=30915
> View this thread: http://www.excelforum.com/showthread...hreadid=505922
>

[Dave Peterson]

You could use a macro:

Option Explicit
Sub testme()
Dim myCol1 As Range
Dim myCol2 As Range
Dim myCell1 As Range
Dim myCell2 As Range
Dim wks As Worksheet
Dim newWks As Worksheet
Dim oRow As Long

Set wks = Worksheets("Sheet1")

With wks
Set myCol1 = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
Set myCol2 = .Range("b1", .Cells(.Rows.Count, "B").End(xlUp))
If myCol1.Cells.Count * myCol2.Cells.Count > .Rows.Count Then
MsgBox "Too much data!"
Exit Sub
End If
End With

Set newWks = Worksheets.Add

oRow = 0
For Each myCell1 In myCol1.Cells
For Each myCell2 In myCol2.Cells
oRow = oRow + 1
With newWks.Cells(oRow, "A")
.Value = myCell1.Value
.Offset(0, 1).Value = myCell2.Value
End With
Next myCell2
Next myCell1

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

TheDPQ wrote:
>
> This might not be the right area however i am a newbie to Excel so i
> figure i'd start here. I have very limited experience with Excel so
> excuse me if i don't make sense.
> ANY HELP IN THE RIGHT DIRECTION WOULD BE AWESOME
>
> I have a Column full of keywords, and i have a column full of Ad IDs...
> is there a way in a *new* sheet to insert the keywords in as many times
> as there are ads?
>
> SHEET 1
> Keyword 1 | 123
> Keyword 2 | 456
> xxxxxxxxx | 789
>
> SHEET 2
> Keyword 1 | 123
> Keyword 1 | 456
> Keyword 1 | 789
> Keyword 2 | 123
> Keyword 2 | 456
> Keyword 2 | 789
>
> I found COUNTA() Which lets me count how many keywords and ads i have,
> i'm just not sure what to do with those numbers now.
>
> --
> TheDPQ
> ------------------------------------------------------------------------
> TheDPQ's Profile: http://www.excelforum.com/member.php...o&userid=30915
> View this thread: http://www.excelforum.com/showthread...hreadid=505922

--

Dave Peterson