code to create list of values
 

a1:a50 is a list of item keys. b1:b23 is a list of counties. I want to
create a two column list (it would have 1150 rows: 50 x 23). It would have
every county for each item key.

code to create list of values
 

Steve,

Where do you want this list to be?

(be sure to make a copy of your file and work on the copy!!!)
(assuming you have the sheet that has these 2 lists as the active sheet in
the active workbook)
The following code will create the results you desire on a sheet named
"Sheet2" complete with column headings (I THINK!!! I have not tested it.)

Option Explicit

Sub CreateCombinedList()
Dim prngItemKeys As Range
Dim prngCounties As Range
Dim prngItemKeyCell As Range
Dim prngCountyCell As Range
Dim pintCounter As Integer

prngItemKeys = ActiveSheet.Range("A1:A50")
prngCounties = ActiveSheet.Range("B1:B23")
pintCounter = 2

Worksheets("Sheet2").Range("A1") = "Item Key"
Worksheets("Sheet2").Range("B1") = "Item Key"

For Each prngItemKeyCell In prngItemKeys.Cells
For Each prngCountyCell In prngCounties.Cells
Worksheets("Sheet2").Cells(pintCounter, 1) =
prngItemKeyCell.Value
Worksheets("Sheet2").Cells(pintCounter, 2) =
prngCountyCell.Value
pintcoutner = pintCounter + 1
Next prngCountyCell
Next prngItemKeyCell


End Sub

HTH,

Conan






"Steve" wrote in message
...
a1:a50 is a list of item keys. b1:b23 is a list of counties. I want to
create a two column list (it would have 1150 rows: 50 x 23). It would
have
every county for each item key.

code to create list of values
 

Public Sub ProcessData()
Dim i As Long, j As Long
Dim LastItem As Long
Dim LastCounty As Long

With ActiveSheet

LastItem = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCounty = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 1 To LastItem * LastCounty Step LastItem
j = j + 1
.Cells(1, "A").Resize(LastItem).Copy .Cells(i, "M")
.Cells(i, "N").Resize(LastItem).Value = .Cells(j, "B")
Next i
End With

End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Steve" wrote in message
...
a1:a50 is a list of item keys. b1:b23 is a list of counties. I want to
create a two column list (it would have 1150 rows: 50 x 23). It would
have
every county for each item key.

code to create list of values
 

Woops,

There were a couple of errors in that code I posted.

I corrected them and tested it and it looks like it works!!!

Option Explicit

Sub CreateCombinedList()
Dim prngItemKeys As Range
Dim prngCounties As Range
Dim prngItemKeyCell As Range
Dim prngCountyCell As Range
Dim pintCounter As Integer

Set prngItemKeys = ActiveSheet.Range("A1:A50")
Set prngCounties = ActiveSheet.Range("B1:B23")
pintCounter = 2

Worksheets("Sheet2").Range("A1") = "Item Key"
Worksheets("Sheet2").Range("B1") = "County"

For Each prngItemKeyCell In prngItemKeys.Cells
For Each prngCountyCell In prngCounties.Cells
Worksheets("Sheet2").Cells(pintCounter, 1) =
prngItemKeyCell.Value
Worksheets("Sheet2").Cells(pintCounter, 2) =
prngCountyCell.Value
pintCounter = pintCounter + 1
Next prngCountyCell
Next prngItemKeyCell


End Sub





"Conan Kelly" wrote in message
...
Steve,

Where do you want this list to be?

(be sure to make a copy of your file and work on the copy!!!)
(assuming you have the sheet that has these 2 lists as the active sheet in
the active workbook)
The following code will create the results you desire on a sheet named
"Sheet2" complete with column headings (I THINK!!! I have not tested it.)

Option Explicit

Sub CreateCombinedList()
Dim prngItemKeys As Range
Dim prngCounties As Range
Dim prngItemKeyCell As Range
Dim prngCountyCell As Range
Dim pintCounter As Integer

prngItemKeys = ActiveSheet.Range("A1:A50")
prngCounties = ActiveSheet.Range("B1:B23")
pintCounter = 2

Worksheets("Sheet2").Range("A1") = "Item Key"
Worksheets("Sheet2").Range("B1") = "Item Key"

For Each prngItemKeyCell In prngItemKeys.Cells
For Each prngCountyCell In prngCounties.Cells
Worksheets("Sheet2").Cells(pintCounter, 1) =
prngItemKeyCell.Value
Worksheets("Sheet2").Cells(pintCounter, 2) =
prngCountyCell.Value
pintcoutner = pintCounter + 1
Next prngCountyCell
Next prngItemKeyCell


End Sub

HTH,

Conan






"Steve" wrote in message
...
a1:a50 is a list of item keys. b1:b23 is a list of counties. I want to
create a two column list (it would have 1150 rows: 50 x 23). It would
have
every county for each item key.

code to create list of values
 

But still 9 times slower than mine <vbg

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Conan Kelly" wrote in message
...
Woops,

There were a couple of errors in that code I posted.

I corrected them and tested it and it looks like it works!!!

Option Explicit

Sub CreateCombinedList()
Dim prngItemKeys As Range
Dim prngCounties As Range
Dim prngItemKeyCell As Range
Dim prngCountyCell As Range
Dim pintCounter As Integer

Set prngItemKeys = ActiveSheet.Range("A1:A50")
Set prngCounties = ActiveSheet.Range("B1:B23")
pintCounter = 2

Worksheets("Sheet2").Range("A1") = "Item Key"
Worksheets("Sheet2").Range("B1") = "County"

For Each prngItemKeyCell In prngItemKeys.Cells
For Each prngCountyCell In prngCounties.Cells
Worksheets("Sheet2").Cells(pintCounter, 1) =
prngItemKeyCell.Value
Worksheets("Sheet2").Cells(pintCounter, 2) =
prngCountyCell.Value
pintCounter = pintCounter + 1
Next prngCountyCell
Next prngItemKeyCell


End Sub





"Conan Kelly" wrote in message
...
Steve,

Where do you want this list to be?

(be sure to make a copy of your file and work on the copy!!!)
(assuming you have the sheet that has these 2 lists as the active sheet
in the active workbook)
The following code will create the results you desire on a sheet named
"Sheet2" complete with column headings (I THINK!!! I have not tested it.)

Option Explicit

Sub CreateCombinedList()
Dim prngItemKeys As Range
Dim prngCounties As Range
Dim prngItemKeyCell As Range
Dim prngCountyCell As Range
Dim pintCounter As Integer

prngItemKeys = ActiveSheet.Range("A1:A50")
prngCounties = ActiveSheet.Range("B1:B23")
pintCounter = 2

Worksheets("Sheet2").Range("A1") = "Item Key"
Worksheets("Sheet2").Range("B1") = "Item Key"

For Each prngItemKeyCell In prngItemKeys.Cells
For Each prngCountyCell In prngCounties.Cells
Worksheets("Sheet2").Cells(pintCounter, 1) =
prngItemKeyCell.Value
Worksheets("Sheet2").Cells(pintCounter, 2) =
prngCountyCell.Value
pintcoutner = pintCounter + 1
Next prngCountyCell
Next prngItemKeyCell


End Sub

HTH,

Conan






"Steve" wrote in message
...
a1:a50 is a list of item keys. b1:b23 is a list of counties. I want to
create a two column list (it would have 1150 rows: 50 x 23). It would
have
every county for each item key.

code to create list of values
 

Bob,

What is "<vbg"?

Why is this way so much slower than your way? What bogs it down so much?

Considering the result is only 1150 rows, is anyone going to notice the
difference? ;-)

Thanks,

Conan




"Bob Phillips" wrote in message
...
But still 9 times slower than mine <vbg

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Conan Kelly" wrote in message
...
Woops,

There were a couple of errors in that code I posted.

I corrected them and tested it and it looks like it works!!!

Option Explicit

Sub CreateCombinedList()
Dim prngItemKeys As Range
Dim prngCounties As Range
Dim prngItemKeyCell As Range
Dim prngCountyCell As Range
Dim pintCounter As Integer

Set prngItemKeys = ActiveSheet.Range("A1:A50")
Set prngCounties = ActiveSheet.Range("B1:B23")
pintCounter = 2

Worksheets("Sheet2").Range("A1") = "Item Key"
Worksheets("Sheet2").Range("B1") = "County"

For Each prngItemKeyCell In prngItemKeys.Cells
For Each prngCountyCell In prngCounties.Cells
Worksheets("Sheet2").Cells(pintCounter, 1) =
prngItemKeyCell.Value
Worksheets("Sheet2").Cells(pintCounter, 2) =
prngCountyCell.Value
pintCounter = pintCounter + 1
Next prngCountyCell
Next prngItemKeyCell


End Sub





"Conan Kelly" wrote in message
...
Steve,

Where do you want this list to be?

(be sure to make a copy of your file and work on the copy!!!)
(assuming you have the sheet that has these 2 lists as the active sheet
in the active workbook)
The following code will create the results you desire on a sheet named
"Sheet2" complete with column headings (I THINK!!! I have not tested
it.)

Option Explicit

Sub CreateCombinedList()
Dim prngItemKeys As Range
Dim prngCounties As Range
Dim prngItemKeyCell As Range
Dim prngCountyCell As Range
Dim pintCounter As Integer

prngItemKeys = ActiveSheet.Range("A1:A50")
prngCounties = ActiveSheet.Range("B1:B23")
pintCounter = 2

Worksheets("Sheet2").Range("A1") = "Item Key"
Worksheets("Sheet2").Range("B1") = "Item Key"

For Each prngItemKeyCell In prngItemKeys.Cells
For Each prngCountyCell In prngCounties.Cells
Worksheets("Sheet2").Cells(pintCounter, 1) =
prngItemKeyCell.Value
Worksheets("Sheet2").Cells(pintCounter, 2) =
prngCountyCell.Value
pintcoutner = pintCounter + 1
Next prngCountyCell
Next prngItemKeyCell


End Sub

HTH,

Conan






"Steve" wrote in message
...
a1:a50 is a list of item keys. b1:b23 is a list of counties. I want
to
create a two column list (it would have 1150 rows: 50 x 23). It would
have
every county for each item key.

code to create list of values
 

Hi Conan,

<vbg is Very Big Grin, I was ribbing you.

It is so much slower because you have a loop within a loop, I have a single
loop that loads them in blocks rather than another inner loop.

In this case, it is not discernible, but performance should always be borne
in mind, as it all mounts up and suddenly, it hurts.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Conan Kelly" wrote in message
...
Bob,

What is "<vbg"?

Why is this way so much slower than your way? What bogs it down so much?

Considering the result is only 1150 rows, is anyone going to notice the
difference? ;-)

Thanks,

Conan




"Bob Phillips" wrote in message
...
But still 9 times slower than mine <vbg

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Conan Kelly" wrote in message
...
Woops,

There were a couple of errors in that code I posted.

I corrected them and tested it and it looks like it works!!!

Option Explicit

Sub CreateCombinedList()
Dim prngItemKeys As Range
Dim prngCounties As Range
Dim prngItemKeyCell As Range
Dim prngCountyCell As Range
Dim pintCounter As Integer

Set prngItemKeys = ActiveSheet.Range("A1:A50")
Set prngCounties = ActiveSheet.Range("B1:B23")
pintCounter = 2

Worksheets("Sheet2").Range("A1") = "Item Key"
Worksheets("Sheet2").Range("B1") = "County"

For Each prngItemKeyCell In prngItemKeys.Cells
For Each prngCountyCell In prngCounties.Cells
Worksheets("Sheet2").Cells(pintCounter, 1) =
prngItemKeyCell.Value
Worksheets("Sheet2").Cells(pintCounter, 2) =
prngCountyCell.Value
pintCounter = pintCounter + 1
Next prngCountyCell
Next prngItemKeyCell


End Sub





"Conan Kelly" wrote in message
...
Steve,

Where do you want this list to be?

(be sure to make a copy of your file and work on the copy!!!)
(assuming you have the sheet that has these 2 lists as the active sheet
in the active workbook)
The following code will create the results you desire on a sheet named
"Sheet2" complete with column headings (I THINK!!! I have not tested
it.)

Option Explicit

Sub CreateCombinedList()
Dim prngItemKeys As Range
Dim prngCounties As Range
Dim prngItemKeyCell As Range
Dim prngCountyCell As Range
Dim pintCounter As Integer

prngItemKeys = ActiveSheet.Range("A1:A50")
prngCounties = ActiveSheet.Range("B1:B23")
pintCounter = 2

Worksheets("Sheet2").Range("A1") = "Item Key"
Worksheets("Sheet2").Range("B1") = "Item Key"

For Each prngItemKeyCell In prngItemKeys.Cells
For Each prngCountyCell In prngCounties.Cells
Worksheets("Sheet2").Cells(pintCounter, 1) =
prngItemKeyCell.Value
Worksheets("Sheet2").Cells(pintCounter, 2) =
prngCountyCell.Value
pintcoutner = pintCounter + 1
Next prngCountyCell
Next prngItemKeyCell


End Sub

HTH,

Conan






"Steve" wrote in message
...
a1:a50 is a list of item keys. b1:b23 is a list of counties. I want
to
create a two column list (it would have 1150 rows: 50 x 23). It
would have
every county for each item key.

code to create list of values
 

Bob,

Thanks for the info, ej-ja-ma-ca-tion, and the ribbing.

Unfortunately, I don't think I will ever get the chance to rib you :'(
(<--don't know what the usenet equiv. of that is......maybe <c.......maybe
<cf.......maybe <vbf)

;-)

Conan




"Bob Phillips" wrote in message
...
Hi Conan,

<vbg is Very Big Grin, I was ribbing you.

It is so much slower because you have a loop within a loop, I have a
single loop that loads them in blocks rather than another inner loop.

In this case, it is not discernible, but performance should always be
borne in mind, as it all mounts up and suddenly, it hurts.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Conan Kelly" wrote in message
...
Bob,

What is "<vbg"?

Why is this way so much slower than your way? What bogs it down so much?

Considering the result is only 1150 rows, is anyone going to notice the
difference? ;-)

Thanks,

Conan




"Bob Phillips" wrote in message
...
But still 9 times slower than mine <vbg

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Conan Kelly" wrote in message
...
Woops,

There were a couple of errors in that code I posted.

I corrected them and tested it and it looks like it works!!!

Option Explicit

Sub CreateCombinedList()
Dim prngItemKeys As Range
Dim prngCounties As Range
Dim prngItemKeyCell As Range
Dim prngCountyCell As Range
Dim pintCounter As Integer

Set prngItemKeys = ActiveSheet.Range("A1:A50")
Set prngCounties = ActiveSheet.Range("B1:B23")
pintCounter = 2

Worksheets("Sheet2").Range("A1") = "Item Key"
Worksheets("Sheet2").Range("B1") = "County"

For Each prngItemKeyCell In prngItemKeys.Cells
For Each prngCountyCell In prngCounties.Cells
Worksheets("Sheet2").Cells(pintCounter, 1) =
prngItemKeyCell.Value
Worksheets("Sheet2").Cells(pintCounter, 2) =
prngCountyCell.Value
pintCounter = pintCounter + 1
Next prngCountyCell
Next prngItemKeyCell


End Sub





"Conan Kelly" wrote in message
...
Steve,

Where do you want this list to be?

(be sure to make a copy of your file and work on the copy!!!)
(assuming you have the sheet that has these 2 lists as the active
sheet in the active workbook)
The following code will create the results you desire on a sheet named
"Sheet2" complete with column headings (I THINK!!! I have not tested
it.)

Option Explicit

Sub CreateCombinedList()
Dim prngItemKeys As Range
Dim prngCounties As Range
Dim prngItemKeyCell As Range
Dim prngCountyCell As Range
Dim pintCounter As Integer

prngItemKeys = ActiveSheet.Range("A1:A50")
prngCounties = ActiveSheet.Range("B1:B23")
pintCounter = 2

Worksheets("Sheet2").Range("A1") = "Item Key"
Worksheets("Sheet2").Range("B1") = "Item Key"

For Each prngItemKeyCell In prngItemKeys.Cells
For Each prngCountyCell In prngCounties.Cells
Worksheets("Sheet2").Cells(pintCounter, 1) =
prngItemKeyCell.Value
Worksheets("Sheet2").Cells(pintCounter, 2) =
prngCountyCell.Value
pintcoutner = pintCounter + 1
Next prngCountyCell
Next prngItemKeyCell


End Sub

HTH,

Conan






"Steve" wrote in message
...
a1:a50 is a list of item keys. b1:b23 is a list of counties. I want
to
create a two column list (it would have 1150 rows: 50 x 23). It
would have
every county for each item key.