Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a drop down list with different values | New Users to Excel | |||
create a list of single values from multiple values | Excel Worksheet Functions | |||
Count unique values and create list based on these values | Excel Worksheet Functions | |||
create list of unique values from a column with repeated values? | Excel Worksheet Functions | |||
How can I create a list that skips zero values? | Excel Worksheet Functions |