Hello All,

My question is, how can I do this automatically with a macro or with a high
speed low drag formula. I took this over from someone who was doing it
manually on paper and I converted it into a spreadsheet. It's only been
three months into the new year and I find this very tedious and I now know
why that person no longer wanted to do this.

Given the following names listed below with the number of entries, I need to
figure out how many table columns needs to be created and put each name in
each table column only once by their corresponding entries. In the example
below, we have a total of 36 entries (adding up all the entries). To get
the total numbers of tables to create, I divided 36 by 8 (8 names per table)
and that gives me a total of 5 table columns to create.


Oscar Delahoya 5
Quincy Jones 5
Xiasha Jones 5
Adam Doe 4
chucky cheese 4
David Crocket 4
Frank Valins 4
Robert Ronins 4
Kent Clark 1
------------------
36/8 = 4.5 or 5 tables

Table1 Table2 Table3 Table4 Table5
------------------------------------------------
Oscar Oscar Oscar Oscar Oscar
Quincy Quincy Quincy Quincy Quincy
Xiasha Xiasha Xiasha Xiasha Xiasha
Adam Adam Adam Adam Kent
Chucky Chucky Chucky Chucky
David David David David
Frank Frank Frank Frank
Robert Robert Robert Robert

Any and all help in this matter is GREATLY APPRECIATED :)

Argus

I don't think you explained how you got that second layout--well, not enough for
me to understand.

I don't see why Chucky Cheese couldn't be in the 5th table--right next to the
4th Adam Doe entry. Then Chucky Cheese could be continued on the next line in
tables 1-3.

Then David Crocket would pick up in the next column (table 4, table 5), then the
next row tables 1 and 2.

And so forth.

If this kind of layout is ok:

Oscar Delahoya Oscar Delahoya Oscar Delahoya Oscar Delahoya Oscar Delahoya
Quincy Jones Quincy Jones Quincy Jones Quincy Jones Quincy Jones
Xiasha Jones Xiasha Jones Xiasha Jones Xiasha Jones Xiasha Jones
Adam Doe Adam Doe Adam Doe Adam Doe chucky cheese
chucky cheese chucky cheese chucky cheese David Crocket David Crocket
David Crocket David Crocket Frank Valins Frank Valins Frank Valins
Frank Valins Robert Ronins Robert Ronins Robert Ronins Robert Ronins
Kent Clark

Just filling them up across the tables, then going to the next row, then you
could use a macro like:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow, "B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \ HowManyPerTable

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value
If HowManyForThisPerson HowManyTables Then
MsgBox "not enough tables for: " & ThisPerson _
& "Stopping!"
Exit Sub
End If

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub

And I don't understand what would happen if Oscar had 6 entries????

Maybe the maximum number of tables should be the based on the max number at the
table and the number of entries--along with the maximum number of entries any
one person has???

So if Oscar had 7 entries, but the calculation showed 5 tables were necessary,
then you'd end up going with 7 tables.




OdAwG wrote:

Hello All,

My question is, how can I do this automatically with a macro or with a high
speed low drag formula. I took this over from someone who was doing it
manually on paper and I converted it into a spreadsheet. It's only been
three months into the new year and I find this very tedious and I now know
why that person no longer wanted to do this.

Given the following names listed below with the number of entries, I need to
figure out how many table columns needs to be created and put each name in
each table column only once by their corresponding entries. In the example
below, we have a total of 36 entries (adding up all the entries). To get
the total numbers of tables to create, I divided 36 by 8 (8 names per table)
and that gives me a total of 5 table columns to create.

Oscar Delahoya 5
Quincy Jones 5
Xiasha Jones 5
Adam Doe 4
chucky cheese 4
David Crocket 4
Frank Valins 4
Robert Ronins 4
Kent Clark 1
------------------
36/8 = 4.5 or 5 tables

Table1 Table2 Table3 Table4 Table5
------------------------------------------------
Oscar Oscar Oscar Oscar Oscar
Quincy Quincy Quincy Quincy Quincy
Xiasha Xiasha Xiasha Xiasha Xiasha
Adam Adam Adam Adam Kent
Chucky Chucky Chucky Chucky
David David David David
Frank Frank Frank Frank
Robert Robert Robert Robert

Any and all help in this matter is GREATLY APPRECIATED :)

Argus

--

Dave Peterson

Hey Dave,

Sorry for not being clear.

Let me try again:
001. The total number of table columns created depends on the total number
of entries.
002. To calculate the total number of tables columns, add up all entries
and divide by 8
003. Each table can only have a max of 8 names per table.
004. No duplications of names in the table columns. So, if Oscar has 5,
then his name should
should appear in five (5) separate tables.

I like the way you've done it in the your code below. I guess what needs to
happen first then is to sort the entry column first decended to get the
highest numeric value up top first to create the total number of table
columns to create.

But, i think the tables should be filled first with eight (8) names before
moving to the next table. What do you think?

Argus


"Dave Peterson" wrote in message
...
I don't think you explained how you got that second layout--well, not
enough for
me to understand.

I don't see why Chucky Cheese couldn't be in the 5th table--right next to
the
4th Adam Doe entry. Then Chucky Cheese could be continued on the next
line in
tables 1-3.

Then David Crocket would pick up in the next column (table 4, table 5),
then the
next row tables 1 and 2.

And so forth.

If this kind of layout is ok:

Oscar Delahoya Oscar Delahoya Oscar Delahoya Oscar Delahoya Oscar Delahoya
Quincy Jones Quincy Jones Quincy Jones Quincy Jones Quincy Jones
Xiasha Jones Xiasha Jones Xiasha Jones Xiasha Jones Xiasha Jones
Adam Doe Adam Doe Adam Doe Adam Doe chucky cheese
chucky cheese chucky cheese chucky cheese David Crocket David Crocket
David Crocket David Crocket Frank Valins Frank Valins Frank Valins
Frank Valins Robert Ronins Robert Ronins Robert Ronins Robert Ronins
Kent Clark

Just filling them up across the tables, then going to the next row, then
you
could use a macro like:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow, "B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \
HowManyPerTable

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value
If HowManyForThisPerson HowManyTables Then
MsgBox "not enough tables for: " & ThisPerson _
& "Stopping!"
Exit Sub
End If

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub

And I don't understand what would happen if Oscar had 6 entries????

Maybe the maximum number of tables should be the based on the max number
at the
table and the number of entries--along with the maximum number of entries
any
one person has???

So if Oscar had 7 entries, but the calculation showed 5 tables were
necessary,
then you'd end up going with 7 tables.




OdAwG wrote:

Hello All,

My question is, how can I do this automatically with a macro or with a
high
speed low drag formula. I took this over from someone who was doing it
manually on paper and I converted it into a spreadsheet. It's only been
three months into the new year and I find this very tedious and I now
know
why that person no longer wanted to do this.

Given the following names listed below with the number of entries, I need
to
figure out how many table columns needs to be created and put each name
in
each table column only once by their corresponding entries. In the
example
below, we have a total of 36 entries (adding up all the entries). To get
the total numbers of tables to create, I divided 36 by 8 (8 names per
table)
and that gives me a total of 5 table columns to create.

Oscar Delahoya 5
Quincy Jones 5
Xiasha Jones 5
Adam Doe 4
chucky cheese 4
David Crocket 4
Frank Valins 4
Robert Ronins 4
Kent Clark 1
------------------
36/8 = 4.5 or 5 tables

Table1 Table2 Table3 Table4 Table5
------------------------------------------------
Oscar Oscar Oscar Oscar Oscar
Quincy Quincy Quincy Quincy Quincy
Xiasha Xiasha Xiasha Xiasha Xiasha
Adam Adam Adam Adam Kent
Chucky Chucky Chucky Chucky
David David David David
Frank Frank Frank Frank
Robert Robert Robert Robert

Any and all help in this matter is GREATLY APPRECIATED :)

Argus

--

Dave Peterson

You can determine the maximum number of tables without sorting. Just like
using: =max(b1:b99) in a worksheet.

And I see no compelling reason to fill each table with 8 names first. So I
wouldn't bother. In fact, I would think that populating each table so that
they're approximately the same size (give or take one) would be better than
having 4 tables with 8 and one table with 1.

But I really don't understand what you're doing.

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim MaxEntries As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow, "B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \ HowManyPerTable

MaxEntries = Application.Max(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow, "B")))

If HowManyTables < MaxEntries Then
HowManyTables = MaxEntries
End If

If TotalEntries HowManyPerTable * HowManyTables Then
MsgBox "Something has to give--not enough room in the table"
Exit Sub
End If

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub



OdAwG wrote:

Hey Dave,

Sorry for not being clear.

Let me try again:
001. The total number of table columns created depends on the total number
of entries.
002. To calculate the total number of tables columns, add up all entries
and divide by 8
003. Each table can only have a max of 8 names per table.
004. No duplications of names in the table columns. So, if Oscar has 5,
then his name should
should appear in five (5) separate tables.

I like the way you've done it in the your code below. I guess what needs to
happen first then is to sort the entry column first decended to get the
highest numeric value up top first to create the total number of table
columns to create.

But, i think the tables should be filled first with eight (8) names before
moving to the next table. What do you think?

Argus

"Dave Peterson" wrote in message
...
I don't think you explained how you got that second layout--well, not
enough for
me to understand.

I don't see why Chucky Cheese couldn't be in the 5th table--right next to
the
4th Adam Doe entry. Then Chucky Cheese could be continued on the next
line in
tables 1-3.

Then David Crocket would pick up in the next column (table 4, table 5),
then the
next row tables 1 and 2.

And so forth.

If this kind of layout is ok:

Oscar Delahoya Oscar Delahoya Oscar Delahoya Oscar Delahoya Oscar Delahoya
Quincy Jones Quincy Jones Quincy Jones Quincy Jones Quincy Jones
Xiasha Jones Xiasha Jones Xiasha Jones Xiasha Jones Xiasha Jones
Adam Doe Adam Doe Adam Doe Adam Doe chucky cheese
chucky cheese chucky cheese chucky cheese David Crocket David Crocket
David Crocket David Crocket Frank Valins Frank Valins Frank Valins
Frank Valins Robert Ronins Robert Ronins Robert Ronins Robert Ronins
Kent Clark

Just filling them up across the tables, then going to the next row, then
you
could use a macro like:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow, "B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \
HowManyPerTable

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value
If HowManyForThisPerson HowManyTables Then
MsgBox "not enough tables for: " & ThisPerson _
& "Stopping!"
Exit Sub
End If

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub

And I don't understand what would happen if Oscar had 6 entries????

Maybe the maximum number of tables should be the based on the max number
at the
table and the number of entries--along with the maximum number of entries
any
one person has???

So if Oscar had 7 entries, but the calculation showed 5 tables were
necessary,
then you'd end up going with 7 tables.




OdAwG wrote:

Hello All,

My question is, how can I do this automatically with a macro or with a
high
speed low drag formula. I took this over from someone who was doing it
manually on paper and I converted it into a spreadsheet. It's only been
three months into the new year and I find this very tedious and I now
know
why that person no longer wanted to do this.

Given the following names listed below with the number of entries, I need
to
figure out how many table columns needs to be created and put each name
in
each table column only once by their corresponding entries. In the
example
below, we have a total of 36 entries (adding up all the entries). To get
the total numbers of tables to create, I divided 36 by 8 (8 names per
table)
and that gives me a total of 5 table columns to create.

Oscar Delahoya 5
Quincy Jones 5
Xiasha Jones 5
Adam Doe 4
chucky cheese 4
David Crocket 4
Frank Valins 4
Robert Ronins 4
Kent Clark 1
------------------
36/8 = 4.5 or 5 tables

Table1 Table2 Table3 Table4 Table5
------------------------------------------------
Oscar Oscar Oscar Oscar Oscar
Quincy Quincy Quincy Quincy Quincy
Xiasha Xiasha Xiasha Xiasha Xiasha
Adam Adam Adam Adam Kent
Chucky Chucky Chucky Chucky
David David David David
Frank Frank Frank Frank
Robert Robert Robert Robert

Any and all help in this matter is GREATLY APPRECIATED :)

Argus

--

Dave Peterson

--

Dave Peterson

Hey David,

Oh cool, thanks for the help.

I guess to make things all clear, what I am trying to do is automate a
bracket sheet that we can use for bowling. People would sign up to enter
into brackets. All they would have to do is give their names and the number
of brackets they want. The 8 bracket sheet is used (because we only bowl 3
games).

Game1 Game2 Game3

----------|
|--------|
----------| |
|------|
----------| | |
|--------| |
----------| |
|-----------
----------| |
|--------| |
----------| | |
|------|
----------| |
|--------|
----------|

In order to do a bracket sheet like above, we have to have the names filled
in. I thought the best way was to create a master sheet with only the
bowlers name and number of brackets entered. Based on the number of
brackets entry, will determing the number of brackets to use. That is what
we were trying to accomplish. I wanted to do this automatically with a
macro so that we wouldn't do it manually week to week.

Once the tables or brackets were completed, I already have a macro that
would copy those names over to the bracket sheets and then randomize them
into the bracket itself.

001. take table1 and copy to a sheet called bracket1 (do the same with each
table)
002. Once on the bracket sheet, I would randomize the name and place them
in the brackets

Does that make it more clearer? I know what I want to say but typing it out
is has been a challenge. hehe ahhah LOL :)

Argus



"Dave Peterson" wrote in message
...
You can determine the maximum number of tables without sorting. Just like
using: =max(b1:b99) in a worksheet.

And I see no compelling reason to fill each table with 8 names first. So
I
wouldn't bother. In fact, I would think that populating each table so
that
they're approximately the same size (give or take one) would be better
than
having 4 tables with 8 and one table with 1.

But I really don't understand what you're doing.

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim MaxEntries As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow, "B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \
HowManyPerTable

MaxEntries = Application.Max(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow, "B")))

If HowManyTables < MaxEntries Then
HowManyTables = MaxEntries
End If

If TotalEntries HowManyPerTable * HowManyTables Then
MsgBox "Something has to give--not enough room in the table"
Exit Sub
End If

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub



OdAwG wrote:

Hey Dave,

Sorry for not being clear.

Let me try again:
001. The total number of table columns created depends on the total
number
of entries.
002. To calculate the total number of tables columns, add up all entries
and divide by 8
003. Each table can only have a max of 8 names per table.
004. No duplications of names in the table columns. So, if Oscar has 5,
then his name should
should appear in five (5) separate tables.

I like the way you've done it in the your code below. I guess what needs
to
happen first then is to sort the entry column first decended to get the
highest numeric value up top first to create the total number of table
columns to create.

But, i think the tables should be filled first with eight (8) names
before
moving to the next table. What do you think?

Argus

"Dave Peterson" wrote in message
...
I don't think you explained how you got that second layout--well, not
enough for
me to understand.

I don't see why Chucky Cheese couldn't be in the 5th table--right next
to
the
4th Adam Doe entry. Then Chucky Cheese could be continued on the next
line in
tables 1-3.

Then David Crocket would pick up in the next column (table 4, table 5),
then the
next row tables 1 and 2.

And so forth.

If this kind of layout is ok:

Oscar Delahoya Oscar Delahoya Oscar Delahoya Oscar Delahoya Oscar
Delahoya
Quincy Jones Quincy Jones Quincy Jones Quincy Jones Quincy
Jones
Xiasha Jones Xiasha Jones Xiasha Jones Xiasha Jones Xiasha
Jones
Adam Doe Adam Doe Adam Doe Adam Doe chucky
cheese
chucky cheese chucky cheese chucky cheese David Crocket David
Crocket
David Crocket David Crocket Frank Valins Frank Valins Frank
Valins
Frank Valins Robert Ronins Robert Ronins Robert Ronins Robert
Ronins
Kent Clark

Just filling them up across the tables, then going to the next row,
then
you
could use a macro like:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow,
"B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \
HowManyPerTable

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value
If HowManyForThisPerson HowManyTables Then
MsgBox "not enough tables for: " & ThisPerson _
& "Stopping!"
Exit Sub
End If

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub

And I don't understand what would happen if Oscar had 6 entries????

Maybe the maximum number of tables should be the based on the max
number
at the
table and the number of entries--along with the maximum number of
entries
any
one person has???

So if Oscar had 7 entries, but the calculation showed 5 tables were
necessary,
then you'd end up going with 7 tables.




OdAwG wrote:

Hello All,

My question is, how can I do this automatically with a macro or with a
high
speed low drag formula. I took this over from someone who was doing
it
manually on paper and I converted it into a spreadsheet. It's only
been
three months into the new year and I find this very tedious and I now
know
why that person no longer wanted to do this.

Given the following names listed below with the number of entries, I
need
to
figure out how many table columns needs to be created and put each
name
in
each table column only once by their corresponding entries. In the
example
below, we have a total of 36 entries (adding up all the entries). To
get
the total numbers of tables to create, I divided 36 by 8 (8 names per
table)
and that gives me a total of 5 table columns to create.

Oscar Delahoya 5
Quincy Jones 5
Xiasha Jones 5
Adam Doe 4
chucky cheese 4
David Crocket 4
Frank Valins 4
Robert Ronins 4
Kent Clark 1
------------------
36/8 = 4.5 or 5 tables

Table1 Table2 Table3 Table4 Table5
------------------------------------------------
Oscar Oscar Oscar Oscar Oscar
Quincy Quincy Quincy Quincy Quincy
Xiasha Xiasha Xiasha Xiasha Xiasha
Adam Adam Adam Adam Kent
Chucky Chucky Chucky Chucky
David David David David
Frank Frank Frank Frank
Robert Robert Robert Robert

Any and all help in this matter is GREATLY APPRECIATED :)

Argus

--

Dave Peterson

--

Dave Peterson

I think you could do that with the table that's created from that second macro.

You could fill in the empty cells with "bye" so that any bracket with less than
8 entries would have some players that advance to the second round. (I'd try to
make it so that no bye played a bye, though.)

And, to me, it makes more sense to fill them the way the macro did--you'd never
end up with one bracket with just 1 name.



OdAwG wrote:

Hey David,

Oh cool, thanks for the help.

I guess to make things all clear, what I am trying to do is automate a
bracket sheet that we can use for bowling. People would sign up to enter
into brackets. All they would have to do is give their names and the number
of brackets they want. The 8 bracket sheet is used (because we only bowl 3
games).

Game1 Game2 Game3

----------|
|--------|
----------| |
|------|
----------| | |
|--------| |
----------| |
|-----------
----------| |
|--------| |
----------| | |
|------|
----------| |
|--------|
----------|

In order to do a bracket sheet like above, we have to have the names filled
in. I thought the best way was to create a master sheet with only the
bowlers name and number of brackets entered. Based on the number of
brackets entry, will determing the number of brackets to use. That is what
we were trying to accomplish. I wanted to do this automatically with a
macro so that we wouldn't do it manually week to week.

Once the tables or brackets were completed, I already have a macro that
would copy those names over to the bracket sheets and then randomize them
into the bracket itself.

001. take table1 and copy to a sheet called bracket1 (do the same with each
table)
002. Once on the bracket sheet, I would randomize the name and place them
in the brackets

Does that make it more clearer? I know what I want to say but typing it out
is has been a challenge. hehe ahhah LOL :)

Argus

"Dave Peterson" wrote in message
...
You can determine the maximum number of tables without sorting. Just like
using: =max(b1:b99) in a worksheet.

And I see no compelling reason to fill each table with 8 names first. So
I
wouldn't bother. In fact, I would think that populating each table so
that
they're approximately the same size (give or take one) would be better
than
having 4 tables with 8 and one table with 1.

But I really don't understand what you're doing.

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim MaxEntries As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow, "B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \
HowManyPerTable

MaxEntries = Application.Max(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow, "B")))

If HowManyTables < MaxEntries Then
HowManyTables = MaxEntries
End If

If TotalEntries HowManyPerTable * HowManyTables Then
MsgBox "Something has to give--not enough room in the table"
Exit Sub
End If

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub



OdAwG wrote:

Hey Dave,

Sorry for not being clear.

Let me try again:
001. The total number of table columns created depends on the total
number
of entries.
002. To calculate the total number of tables columns, add up all entries
and divide by 8
003. Each table can only have a max of 8 names per table.
004. No duplications of names in the table columns. So, if Oscar has 5,
then his name should
should appear in five (5) separate tables.

I like the way you've done it in the your code below. I guess what needs
to
happen first then is to sort the entry column first decended to get the
highest numeric value up top first to create the total number of table
columns to create.

But, i think the tables should be filled first with eight (8) names
before
moving to the next table. What do you think?

Argus

"Dave Peterson" wrote in message
...
I don't think you explained how you got that second layout--well, not
enough for
me to understand.

I don't see why Chucky Cheese couldn't be in the 5th table--right next
to
the
4th Adam Doe entry. Then Chucky Cheese could be continued on the next
line in
tables 1-3.

Then David Crocket would pick up in the next column (table 4, table 5),
then the
next row tables 1 and 2.

And so forth.

If this kind of layout is ok:

Oscar Delahoya Oscar Delahoya Oscar Delahoya Oscar Delahoya Oscar
Delahoya
Quincy Jones Quincy Jones Quincy Jones Quincy Jones Quincy
Jones
Xiasha Jones Xiasha Jones Xiasha Jones Xiasha Jones Xiasha
Jones
Adam Doe Adam Doe Adam Doe Adam Doe chucky
cheese
chucky cheese chucky cheese chucky cheese David Crocket David
Crocket
David Crocket David Crocket Frank Valins Frank Valins Frank
Valins
Frank Valins Robert Ronins Robert Ronins Robert Ronins Robert
Ronins
Kent Clark

Just filling them up across the tables, then going to the next row,
then
you
could use a macro like:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow,
"B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \
HowManyPerTable

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value
If HowManyForThisPerson HowManyTables Then
MsgBox "not enough tables for: " & ThisPerson _
& "Stopping!"
Exit Sub
End If

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub

And I don't understand what would happen if Oscar had 6 entries????

Maybe the maximum number of tables should be the based on the max
number
at the
table and the number of entries--along with the maximum number of
entries
any
one person has???

So if Oscar had 7 entries, but the calculation showed 5 tables were
necessary,
then you'd end up going with 7 tables.




OdAwG wrote:

Hello All,

My question is, how can I do this automatically with a macro or with a
high
speed low drag formula. I took this over from someone who was doing
it
manually on paper and I converted it into a spreadsheet. It's only
been
three months into the new year and I find this very tedious and I now
know
why that person no longer wanted to do this.

Given the following names listed below with the number of entries, I
need
to
figure out how many table columns needs to be created and put each
name
in
each table column only once by their corresponding entries. In the
example
below, we have a total of 36 entries (adding up all the entries). To
get
the total numbers of tables to create, I divided 36 by 8 (8 names per
table)
and that gives me a total of 5 table columns to create.

Oscar Delahoya 5
Quincy Jones 5
Xiasha Jones 5
Adam Doe 4
chucky cheese 4
David Crocket 4
Frank Valins 4
Robert Ronins 4
Kent Clark 1
------------------
36/8 = 4.5 or 5 tables

Table1 Table2 Table3 Table4 Table5
------------------------------------------------
Oscar Oscar Oscar Oscar Oscar
Quincy Quincy Quincy Quincy Quincy
Xiasha Xiasha Xiasha Xiasha Xiasha
Adam Adam Adam Adam Kent
Chucky Chucky Chucky Chucky
David David David David
Frank Frank Frank Frank
Robert Robert Robert Robert

Any and all help in this matter is GREATLY APPRECIATED :)

Argus

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Hey D

I agree with your suggestion and I will do it the way you have suggested.
However, one other quick question, ok acutally two questions

001. in your code Set NewWks = Worksheets.Add - can we name this to a
unique name like GetNames
002. can we add column heading Table1, Table2 or Bracket1, Bracket2,
etc...in that new sheet

Argus

"Dave Peterson" wrote in message
...
I think you could do that with the table that's created from that second
macro.

You could fill in the empty cells with "bye" so that any bracket with less
than
8 entries would have some players that advance to the second round. (I'd
try to
make it so that no bye played a bye, though.)

And, to me, it makes more sense to fill them the way the macro did--you'd
never
end up with one bracket with just 1 name.



OdAwG wrote:

Hey David,

Oh cool, thanks for the help.

I guess to make things all clear, what I am trying to do is automate a
bracket sheet that we can use for bowling. People would sign up to enter
into brackets. All they would have to do is give their names and the
number
of brackets they want. The 8 bracket sheet is used (because we only bowl
3
games).

Game1 Game2 Game3

----------|
|--------|
----------| |
|------|
----------| | |
|--------| |
----------| |
|-----------
----------| |
|--------| |
----------| | |
|------|
----------| |
|--------|
----------|

In order to do a bracket sheet like above, we have to have the names
filled
in. I thought the best way was to create a master sheet with only the
bowlers name and number of brackets entered. Based on the number of
brackets entry, will determing the number of brackets to use. That is
what
we were trying to accomplish. I wanted to do this automatically with a
macro so that we wouldn't do it manually week to week.

Once the tables or brackets were completed, I already have a macro that
would copy those names over to the bracket sheets and then randomize them
into the bracket itself.

001. take table1 and copy to a sheet called bracket1 (do the same with
each
table)
002. Once on the bracket sheet, I would randomize the name and place
them
in the brackets

Does that make it more clearer? I know what I want to say but typing it
out
is has been a challenge. hehe ahhah LOL :)

Argus

"Dave Peterson" wrote in message
...
You can determine the maximum number of tables without sorting. Just
like
using: =max(b1:b99) in a worksheet.

And I see no compelling reason to fill each table with 8 names first.
So
I
wouldn't bother. In fact, I would think that populating each table so
that
they're approximately the same size (give or take one) would be better
than
having 4 tables with 8 and one table with 1.

But I really don't understand what you're doing.

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim MaxEntries As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow,
"B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \
HowManyPerTable

MaxEntries = Application.Max(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow,
"B")))

If HowManyTables < MaxEntries Then
HowManyTables = MaxEntries
End If

If TotalEntries HowManyPerTable * HowManyTables Then
MsgBox "Something has to give--not enough room in the table"
Exit Sub
End If

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub



OdAwG wrote:

Hey Dave,

Sorry for not being clear.

Let me try again:
001. The total number of table columns created depends on the total
number
of entries.
002. To calculate the total number of tables columns, add up all
entries
and divide by 8
003. Each table can only have a max of 8 names per table.
004. No duplications of names in the table columns. So, if Oscar has
5,
then his name should
should appear in five (5) separate tables.

I like the way you've done it in the your code below. I guess what
needs
to
happen first then is to sort the entry column first decended to get
the
highest numeric value up top first to create the total number of table
columns to create.

But, i think the tables should be filled first with eight (8) names
before
moving to the next table. What do you think?

Argus

"Dave Peterson" wrote in message
...
I don't think you explained how you got that second layout--well, not
enough for
me to understand.

I don't see why Chucky Cheese couldn't be in the 5th table--right
next
to
the
4th Adam Doe entry. Then Chucky Cheese could be continued on the
next
line in
tables 1-3.

Then David Crocket would pick up in the next column (table 4, table
5),
then the
next row tables 1 and 2.

And so forth.

If this kind of layout is ok:

Oscar Delahoya Oscar Delahoya Oscar Delahoya Oscar Delahoya Oscar
Delahoya
Quincy Jones Quincy Jones Quincy Jones Quincy Jones Quincy
Jones
Xiasha Jones Xiasha Jones Xiasha Jones Xiasha Jones Xiasha
Jones
Adam Doe Adam Doe Adam Doe Adam Doe chucky
cheese
chucky cheese chucky cheese chucky cheese David Crocket David
Crocket
David Crocket David Crocket Frank Valins Frank Valins Frank
Valins
Frank Valins Robert Ronins Robert Ronins Robert Ronins Robert
Ronins
Kent Clark

Just filling them up across the tables, then going to the next row,
then
you
could use a macro like:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow, "B"),
_
.Cells(LastRow,
"B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \
HowManyPerTable

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value
If HowManyForThisPerson HowManyTables Then
MsgBox "not enough tables for: " & ThisPerson _
& "Stopping!"
Exit Sub
End If

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub

And I don't understand what would happen if Oscar had 6 entries????

Maybe the maximum number of tables should be the based on the max
number
at the
table and the number of entries--along with the maximum number of
entries
any
one person has???

So if Oscar had 7 entries, but the calculation showed 5 tables were
necessary,
then you'd end up going with 7 tables.




OdAwG wrote:

Hello All,

My question is, how can I do this automatically with a macro or
with a
high
speed low drag formula. I took this over from someone who was
doing
it
manually on paper and I converted it into a spreadsheet. It's only
been
three months into the new year and I find this very tedious and I
now
know
why that person no longer wanted to do this.

Given the following names listed below with the number of entries,
I
need
to
figure out how many table columns needs to be created and put each
name
in
each table column only once by their corresponding entries. In the
example
below, we have a total of 36 entries (adding up all the entries).
To
get
the total numbers of tables to create, I divided 36 by 8 (8 names
per
table)
and that gives me a total of 5 table columns to create.

Oscar Delahoya 5
Quincy Jones 5
Xiasha Jones 5
Adam Doe 4
chucky cheese 4
David Crocket 4
Frank Valins 4
Robert Ronins 4
Kent Clark 1
------------------
36/8 = 4.5 or 5 tables

Table1 Table2 Table3 Table4 Table5
------------------------------------------------
Oscar Oscar Oscar Oscar Oscar
Quincy Quincy Quincy Quincy Quincy
Xiasha Xiasha Xiasha Xiasha Xiasha
Adam Adam Adam Adam Kent
Chucky Chucky Chucky Chucky
David David David David
Frank Frank Frank Frank
Robert Robert Robert Robert

Any and all help in this matter is GREATLY APPRECIATED :)

Argus

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

#1. You could add this code:

Set NewWks = Worksheets.Add
on error resume next
worksheets("getnames").delete 'in case there's an existing sheet
on error goto 0
newwks.name = "GetNames"

#2. change this:
oRow = 0
to
oRow = 1 'cause you're gonna have headers in row 1
with newwks.range("a1").resize(1,howmanytables)
.formula = "=""Bracket""& column()"
.value = .value
end with

OdAwG wrote:

Hey D

I agree with your suggestion and I will do it the way you have suggested.
However, one other quick question, ok acutally two questions

001. in your code Set NewWks = Worksheets.Add - can we name this to a
unique name like GetNames
002. can we add column heading Table1, Table2 or Bracket1, Bracket2,
etc...in that new sheet

Argus

"Dave Peterson" wrote in message
...
I think you could do that with the table that's created from that second
macro.

You could fill in the empty cells with "bye" so that any bracket with less
than
8 entries would have some players that advance to the second round. (I'd
try to
make it so that no bye played a bye, though.)

And, to me, it makes more sense to fill them the way the macro did--you'd
never
end up with one bracket with just 1 name.



OdAwG wrote:

Hey David,

Oh cool, thanks for the help.

I guess to make things all clear, what I am trying to do is automate a
bracket sheet that we can use for bowling. People would sign up to enter
into brackets. All they would have to do is give their names and the
number
of brackets they want. The 8 bracket sheet is used (because we only bowl
3
games).

Game1 Game2 Game3

----------|
|--------|
----------| |
|------|
----------| | |
|--------| |
----------| |
|-----------
----------| |
|--------| |
----------| | |
|------|
----------| |
|--------|
----------|

In order to do a bracket sheet like above, we have to have the names
filled
in. I thought the best way was to create a master sheet with only the
bowlers name and number of brackets entered. Based on the number of
brackets entry, will determing the number of brackets to use. That is
what
we were trying to accomplish. I wanted to do this automatically with a
macro so that we wouldn't do it manually week to week.

Once the tables or brackets were completed, I already have a macro that
would copy those names over to the bracket sheets and then randomize them
into the bracket itself.

001. take table1 and copy to a sheet called bracket1 (do the same with
each
table)
002. Once on the bracket sheet, I would randomize the name and place
them
in the brackets

Does that make it more clearer? I know what I want to say but typing it
out
is has been a challenge. hehe ahhah LOL :)

Argus

"Dave Peterson" wrote in message
...
You can determine the maximum number of tables without sorting. Just
like
using: =max(b1:b99) in a worksheet.

And I see no compelling reason to fill each table with 8 names first.
So
I
wouldn't bother. In fact, I would think that populating each table so
that
they're approximately the same size (give or take one) would be better
than
having 4 tables with 8 and one table with 1.

But I really don't understand what you're doing.

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim MaxEntries As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow,
"B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \
HowManyPerTable

MaxEntries = Application.Max(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow,
"B")))

If HowManyTables < MaxEntries Then
HowManyTables = MaxEntries
End If

If TotalEntries HowManyPerTable * HowManyTables Then
MsgBox "Something has to give--not enough room in the table"
Exit Sub
End If

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub



OdAwG wrote:

Hey Dave,

Sorry for not being clear.

Let me try again:
001. The total number of table columns created depends on the total
number
of entries.
002. To calculate the total number of tables columns, add up all
entries
and divide by 8
003. Each table can only have a max of 8 names per table.
004. No duplications of names in the table columns. So, if Oscar has
5,
then his name should
should appear in five (5) separate tables.

I like the way you've done it in the your code below. I guess what
needs
to
happen first then is to sort the entry column first decended to get
the
highest numeric value up top first to create the total number of table
columns to create.

But, i think the tables should be filled first with eight (8) names
before
moving to the next table. What do you think?

Argus

"Dave Peterson" wrote in message
...
I don't think you explained how you got that second layout--well, not
enough for
me to understand.

I don't see why Chucky Cheese couldn't be in the 5th table--right
next
to
the
4th Adam Doe entry. Then Chucky Cheese could be continued on the
next
line in
tables 1-3.

Then David Crocket would pick up in the next column (table 4, table
5),
then the
next row tables 1 and 2.

And so forth.

If this kind of layout is ok:

Oscar Delahoya Oscar Delahoya Oscar Delahoya Oscar Delahoya Oscar
Delahoya
Quincy Jones Quincy Jones Quincy Jones Quincy Jones Quincy
Jones
Xiasha Jones Xiasha Jones Xiasha Jones Xiasha Jones Xiasha
Jones
Adam Doe Adam Doe Adam Doe Adam Doe chucky
cheese
chucky cheese chucky cheese chucky cheese David Crocket David
Crocket
David Crocket David Crocket Frank Valins Frank Valins Frank
Valins
Frank Valins Robert Ronins Robert Ronins Robert Ronins Robert
Ronins
Kent Clark

Just filling them up across the tables, then going to the next row,
then
you
could use a macro like:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow, "B"),
_
.Cells(LastRow,
"B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \
HowManyPerTable

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value
If HowManyForThisPerson HowManyTables Then
MsgBox "not enough tables for: " & ThisPerson _
& "Stopping!"
Exit Sub
End If

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub

And I don't understand what would happen if Oscar had 6 entries????

Maybe the maximum number of tables should be the based on the max
number
at the
table and the number of entries--along with the maximum number of
entries
any
one person has???

So if Oscar had 7 entries, but the calculation showed 5 tables were
necessary,
then you'd end up going with 7 tables.




OdAwG wrote:

Hello All,

My question is, how can I do this automatically with a macro or
with a
high
speed low drag formula. I took this over from someone who was
doing
it
manually on paper and I converted it into a spreadsheet. It's only
been
three months into the new year and I find this very tedious and I
now
know
why that person no longer wanted to do this.

Given the following names listed below with the number of entries,
I
need
to
figure out how many table columns needs to be created and put each
name
in
each table column only once by their corresponding entries. In the
example
below, we have a total of 36 entries (adding up all the entries).
To
get
the total numbers of tables to create, I divided 36 by 8 (8 names
per
table)
and that gives me a total of 5 table columns to create.

Oscar Delahoya 5
Quincy Jones 5
Xiasha Jones 5
Adam Doe 4
chucky cheese 4
David Crocket 4
Frank Valins 4
Robert Ronins 4
Kent Clark 1
------------------
36/8 = 4.5 or 5 tables

Table1 Table2 Table3 Table4 Table5
------------------------------------------------
Oscar Oscar Oscar Oscar Oscar
Quincy Quincy Quincy Quincy Quincy
Xiasha Xiasha Xiasha Xiasha Xiasha
Adam Adam Adam Adam Kent
Chucky Chucky Chucky Chucky
David David David David
Frank Frank Frank Frank
Robert Robert Robert Robert

Any and all help in this matter is GREATLY APPRECIATED :)

Argus

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Hey Mr. "DP"

U-Da-Man,

Thanks for the help, yet again. I guess you can tell, I'm a newbie huh ...
:)

Argus


"Dave Peterson" wrote in message
...
#1. You could add this code:

Set NewWks = Worksheets.Add
on error resume next
worksheets("getnames").delete 'in case there's an existing sheet
on error goto 0
newwks.name = "GetNames"

#2. change this:
oRow = 0
to
oRow = 1 'cause you're gonna have headers in row 1
with newwks.range("a1").resize(1,howmanytables)
.formula = "=""Bracket""& column()"
.value = .value
end with

OdAwG wrote:

Hey D

I agree with your suggestion and I will do it the way you have suggested.
However, one other quick question, ok acutally two questions

001. in your code Set NewWks = Worksheets.Add - can we name this to a
unique name like GetNames
002. can we add column heading Table1, Table2 or Bracket1, Bracket2,
etc...in that new sheet

Argus

"Dave Peterson" wrote in message
...
I think you could do that with the table that's created from that second
macro.

You could fill in the empty cells with "bye" so that any bracket with
less
than
8 entries would have some players that advance to the second round.
(I'd
try to
make it so that no bye played a bye, though.)

And, to me, it makes more sense to fill them the way the macro
did--you'd
never
end up with one bracket with just 1 name.



OdAwG wrote:

Hey David,

Oh cool, thanks for the help.

I guess to make things all clear, what I am trying to do is automate a
bracket sheet that we can use for bowling. People would sign up to
enter
into brackets. All they would have to do is give their names and the
number
of brackets they want. The 8 bracket sheet is used (because we only
bowl
3
games).

Game1 Game2 Game3

----------|
|--------|
----------| |
|------|
----------| | |
|--------| |
----------| |
|-----------
----------| |
|--------| |
----------| | |
|------|
----------| |
|--------|
----------|

In order to do a bracket sheet like above, we have to have the names
filled
in. I thought the best way was to create a master sheet with only the
bowlers name and number of brackets entered. Based on the number of
brackets entry, will determing the number of brackets to use. That is
what
we were trying to accomplish. I wanted to do this automatically with
a
macro so that we wouldn't do it manually week to week.

Once the tables or brackets were completed, I already have a macro
that
would copy those names over to the bracket sheets and then randomize
them
into the bracket itself.

001. take table1 and copy to a sheet called bracket1 (do the same
with
each
table)
002. Once on the bracket sheet, I would randomize the name and place
them
in the brackets

Does that make it more clearer? I know what I want to say but typing
it
out
is has been a challenge. hehe ahhah LOL :)

Argus

"Dave Peterson" wrote in message
...
You can determine the maximum number of tables without sorting.
Just
like
using: =max(b1:b99) in a worksheet.

And I see no compelling reason to fill each table with 8 names
first.
So
I
wouldn't bother. In fact, I would think that populating each table
so
that
they're approximately the same size (give or take one) would be
better
than
having 4 tables with 8 and one table with 1.

But I really don't understand what you're doing.

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim MaxEntries As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow, "B"),
_
.Cells(LastRow,
"B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \
HowManyPerTable

MaxEntries = Application.Max(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow,
"B")))

If HowManyTables < MaxEntries Then
HowManyTables = MaxEntries
End If

If TotalEntries HowManyPerTable * HowManyTables Then
MsgBox "Something has to give--not enough room in the
table"
Exit Sub
End If

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub



OdAwG wrote:

Hey Dave,

Sorry for not being clear.

Let me try again:
001. The total number of table columns created depends on the
total
number
of entries.
002. To calculate the total number of tables columns, add up all
entries
and divide by 8
003. Each table can only have a max of 8 names per table.
004. No duplications of names in the table columns. So, if Oscar
has
5,
then his name should
should appear in five (5) separate tables.

I like the way you've done it in the your code below. I guess what
needs
to
happen first then is to sort the entry column first decended to get
the
highest numeric value up top first to create the total number of
table
columns to create.

But, i think the tables should be filled first with eight (8) names
before
moving to the next table. What do you think?

Argus

"Dave Peterson" wrote in message
...
I don't think you explained how you got that second layout--well,
not
enough for
me to understand.

I don't see why Chucky Cheese couldn't be in the 5th table--right
next
to
the
4th Adam Doe entry. Then Chucky Cheese could be continued on the
next
line in
tables 1-3.

Then David Crocket would pick up in the next column (table 4,
table
5),
then the
next row tables 1 and 2.

And so forth.

If this kind of layout is ok:

Oscar Delahoya Oscar Delahoya Oscar Delahoya Oscar Delahoya Oscar
Delahoya
Quincy Jones Quincy Jones Quincy Jones Quincy Jones
Quincy
Jones
Xiasha Jones Xiasha Jones Xiasha Jones Xiasha Jones
Xiasha
Jones
Adam Doe Adam Doe Adam Doe Adam Doe
chucky
cheese
chucky cheese chucky cheese chucky cheese David Crocket David
Crocket
David Crocket David Crocket Frank Valins Frank Valins Frank
Valins
Frank Valins Robert Ronins Robert Ronins Robert Ronins
Robert
Ronins
Kent Clark

Just filling them up across the tables, then going to the next
row,
then
you
could use a macro like:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow,
"B"),
_
.Cells(LastRow,
"B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \
HowManyPerTable

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value
If HowManyForThisPerson HowManyTables Then
MsgBox "not enough tables for: " & ThisPerson _
& "Stopping!"
Exit Sub
End If

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub

And I don't understand what would happen if Oscar had 6
entries????

Maybe the maximum number of tables should be the based on the max
number
at the
table and the number of entries--along with the maximum number of
entries
any
one person has???

So if Oscar had 7 entries, but the calculation showed 5 tables
were
necessary,
then you'd end up going with 7 tables.




OdAwG wrote:

Hello All,

My question is, how can I do this automatically with a macro or
with a
high
speed low drag formula. I took this over from someone who was
doing
it
manually on paper and I converted it into a spreadsheet. It's
only
been
three months into the new year and I find this very tedious and
I
now
know
why that person no longer wanted to do this.

Given the following names listed below with the number of
entries,
I
need
to
figure out how many table columns needs to be created and put
each
name
in
each table column only once by their corresponding entries. In
the
example
below, we have a total of 36 entries (adding up all the
entries).
To
get
the total numbers of tables to create, I divided 36 by 8 (8
names
per
table)
and that gives me a total of 5 table columns to create.

Oscar Delahoya 5
Quincy Jones 5
Xiasha Jones 5
Adam Doe 4
chucky cheese 4
David Crocket 4
Frank Valins 4
Robert Ronins 4
Kent Clark 1
------------------
36/8 = 4.5 or 5 tables

Table1 Table2 Table3 Table4 Table5
------------------------------------------------
Oscar Oscar Oscar Oscar Oscar
Quincy Quincy Quincy Quincy Quincy
Xiasha Xiasha Xiasha Xiasha Xiasha
Adam Adam Adam Adam Kent
Chucky Chucky Chucky Chucky
David David David David
Frank Frank Frank Frank
Robert Robert Robert Robert

Any and all help in this matter is GREATLY APPRECIATED :)

Argus

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Glad you got something you can work with.

I don't have any specific code suggestion, but when you're laying out the
brackets with less 8 people, I would think you'd want to give byes to as many
people as possible. You wouldn't want a bye playing a bye if and have people
playing each other.

I think I'd seed them in this kind of order (with 1-x real people and x-8 the
byes):

1
----------+
5 |----
----------+

3
----------+
7 |----
----------+

2
----------+
6 |----
----------+

4
----------+
8 |----
----------+







OdAwG wrote:

Hey Mr. "DP"

U-Da-Man,

Thanks for the help, yet again. I guess you can tell, I'm a newbie huh ...
:)

Argus

"Dave Peterson" wrote in message
...
#1. You could add this code:

Set NewWks = Worksheets.Add
on error resume next
worksheets("getnames").delete 'in case there's an existing sheet
on error goto 0
newwks.name = "GetNames"

#2. change this:
oRow = 0
to
oRow = 1 'cause you're gonna have headers in row 1
with newwks.range("a1").resize(1,howmanytables)
.formula = "=""Bracket""& column()"
.value = .value
end with

OdAwG wrote:

Hey D

I agree with your suggestion and I will do it the way you have suggested.
However, one other quick question, ok acutally two questions

001. in your code Set NewWks = Worksheets.Add - can we name this to a
unique name like GetNames
002. can we add column heading Table1, Table2 or Bracket1, Bracket2,
etc...in that new sheet

Argus

"Dave Peterson" wrote in message
...
I think you could do that with the table that's created from that second
macro.

You could fill in the empty cells with "bye" so that any bracket with
less
than
8 entries would have some players that advance to the second round.
(I'd
try to
make it so that no bye played a bye, though.)

And, to me, it makes more sense to fill them the way the macro
did--you'd
never
end up with one bracket with just 1 name.



OdAwG wrote:

Hey David,

Oh cool, thanks for the help.

I guess to make things all clear, what I am trying to do is automate a
bracket sheet that we can use for bowling. People would sign up to
enter
into brackets. All they would have to do is give their names and the
number
of brackets they want. The 8 bracket sheet is used (because we only
bowl
3
games).

Game1 Game2 Game3

----------|
|--------|
----------| |
|------|
----------| | |
|--------| |
----------| |
|-----------
----------| |
|--------| |
----------| | |
|------|
----------| |
|--------|
----------|

In order to do a bracket sheet like above, we have to have the names
filled
in. I thought the best way was to create a master sheet with only the
bowlers name and number of brackets entered. Based on the number of
brackets entry, will determing the number of brackets to use. That is
what
we were trying to accomplish. I wanted to do this automatically with
a
macro so that we wouldn't do it manually week to week.

Once the tables or brackets were completed, I already have a macro
that
would copy those names over to the bracket sheets and then randomize
them
into the bracket itself.

001. take table1 and copy to a sheet called bracket1 (do the same
with
each
table)
002. Once on the bracket sheet, I would randomize the name and place
them
in the brackets

Does that make it more clearer? I know what I want to say but typing
it
out
is has been a challenge. hehe ahhah LOL :)

Argus

"Dave Peterson" wrote in message
...
You can determine the maximum number of tables without sorting.
Just
like
using: =max(b1:b99) in a worksheet.

And I see no compelling reason to fill each table with 8 names
first.
So
I
wouldn't bother. In fact, I would think that populating each table
so
that
they're approximately the same size (give or take one) would be
better
than
having 4 tables with 8 and one table with 1.

But I really don't understand what you're doing.

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim MaxEntries As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow, "B"),
_
.Cells(LastRow,
"B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \
HowManyPerTable

MaxEntries = Application.Max(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow,
"B")))

If HowManyTables < MaxEntries Then
HowManyTables = MaxEntries
End If

If TotalEntries HowManyPerTable * HowManyTables Then
MsgBox "Something has to give--not enough room in the
table"
Exit Sub
End If

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub



OdAwG wrote:

Hey Dave,

Sorry for not being clear.

Let me try again:
001. The total number of table columns created depends on the
total
number
of entries.
002. To calculate the total number of tables columns, add up all
entries
and divide by 8
003. Each table can only have a max of 8 names per table.
004. No duplications of names in the table columns. So, if Oscar
has
5,
then his name should
should appear in five (5) separate tables.

I like the way you've done it in the your code below. I guess what
needs
to
happen first then is to sort the entry column first decended to get
the
highest numeric value up top first to create the total number of
table
columns to create.

But, i think the tables should be filled first with eight (8) names
before
moving to the next table. What do you think?

Argus

"Dave Peterson" wrote in message
...
I don't think you explained how you got that second layout--well,
not
enough for
me to understand.

I don't see why Chucky Cheese couldn't be in the 5th table--right
next
to
the
4th Adam Doe entry. Then Chucky Cheese could be continued on the
next
line in
tables 1-3.

Then David Crocket would pick up in the next column (table 4,
table
5),
then the
next row tables 1 and 2.

And so forth.

If this kind of layout is ok:

Oscar Delahoya Oscar Delahoya Oscar Delahoya Oscar Delahoya Oscar
Delahoya
Quincy Jones Quincy Jones Quincy Jones Quincy Jones
Quincy
Jones
Xiasha Jones Xiasha Jones Xiasha Jones Xiasha Jones
Xiasha
Jones
Adam Doe Adam Doe Adam Doe Adam Doe
chucky
cheese
chucky cheese chucky cheese chucky cheese David Crocket David
Crocket
David Crocket David Crocket Frank Valins Frank Valins Frank
Valins
Frank Valins Robert Ronins Robert Ronins Robert Ronins
Robert
Ronins
Kent Clark

Just filling them up across the tables, then going to the next
row,
then
you
could use a macro like:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow,
"B"),
_
.Cells(LastRow,
"B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \
HowManyPerTable

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value
If HowManyForThisPerson HowManyTables Then
MsgBox "not enough tables for: " & ThisPerson _
& "Stopping!"
Exit Sub
End If

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub

And I don't understand what would happen if Oscar had 6
entries????

Maybe the maximum number of tables should be the based on the max
number
at the
table and the number of entries--along with the maximum number of
entries
any
one person has???

So if Oscar had 7 entries, but the calculation showed 5 tables
were
necessary,
then you'd end up going with 7 tables.




OdAwG wrote:

Hello All,

My question is, how can I do this automatically with a macro or
with a
high
speed low drag formula. I took this over from someone who was
doing
it
manually on paper and I converted it into a spreadsheet. It's
only
been
three months into the new year and I find this very tedious and
I
now
know
why that person no longer wanted to do this.

Given the following names listed below with the number of
entries,
I
need
to
figure out how many table columns needs to be created and put
each
name
in
each table column only once by their corresponding entries. In
the
example
below, we have a total of 36 entries (adding up all the
entries).
To
get
the total numbers of tables to create, I divided 36 by 8 (8
names
per
table)
and that gives me a total of 5 table columns to create.

Oscar Delahoya 5
Quincy Jones 5
Xiasha Jones 5
Adam Doe 4
chucky cheese 4
David Crocket 4
Frank Valins 4
Robert Ronins 4
Kent Clark 1
------------------
36/8 = 4.5 or 5 tables

Table1 Table2 Table3 Table4 Table5
------------------------------------------------
Oscar Oscar Oscar Oscar Oscar
Quincy Quincy Quincy Quincy Quincy
Xiasha Xiasha Xiasha Xiasha Xiasha
Adam Adam Adam Adam Kent
Chucky Chucky Chucky Chucky
David David David David
Frank Frank Frank Frank
Robert Robert Robert Robert

Any and all help in this matter is GREATLY APPRECIATED :)

Argus

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Hey Sir,

I think I got that one solved but need your help on two (2) things then.

1. within your code that you have provided, is their a way to fill in the
word bye into those brackets that have less than 8 names.

2. Also within your code, is there a way to copy those names from each
(bracket) table to each sheet. The table names used is the same as the
sheet name. Bracket1, Bracket2, and etc... The names would need to be
pasted in Column O in each sheet.

With the above said, I think we can do the following using rand() to assign
a numeric value. Using Columns O, P, Q, and R is static and the same on
every sheet.

1. I would copy the names from Bracket1 from sheet "GetNames" to sheet
"Bracket1" to column O
2. I would then make a copy of Column O and paste it into Column P.
3. Then I would place =rand() in column Q next to each name to get a
numeric value.
4. I would then highlight both column P and Q and sort by column Q.
5. finally in column R would place the following formula
=IF(O1=P1,"ReRun"," ")
6. I would hit F9 to generate new random numbers
7. If "ReRun" appears in any row in column R the hit F9 again until

What do y ou think,

Argus


"Dave Peterson" wrote in message
...
Glad you got something you can work with.

I don't have any specific code suggestion, but when you're laying out the
brackets with less 8 people, I would think you'd want to give byes to as
many
people as possible. You wouldn't want a bye playing a bye if and have
people
playing each other.

I think I'd seed them in this kind of order (with 1-x real people and x-8
the
byes):

1
----------+
5 |----
----------+

3
----------+
7 |----
----------+

2
----------+
6 |----
----------+

4
----------+
8 |----
----------+







OdAwG wrote:

Hey Mr. "DP"

U-Da-Man,

Thanks for the help, yet again. I guess you can tell, I'm a newbie huh
...
:)

Argus

"Dave Peterson" wrote in message
...
#1. You could add this code:

Set NewWks = Worksheets.Add
on error resume next
worksheets("getnames").delete 'in case there's an existing sheet
on error goto 0
newwks.name = "GetNames"

#2. change this:
oRow = 0
to
oRow = 1 'cause you're gonna have headers in row 1
with newwks.range("a1").resize(1,howmanytables)
.formula = "=""Bracket""& column()"
.value = .value
end with

OdAwG wrote:

Hey D

I agree with your suggestion and I will do it the way you have
suggested.
However, one other quick question, ok acutally two questions

001. in your code Set NewWks = Worksheets.Add - can we name this to
a
unique name like GetNames
002. can we add column heading Table1, Table2 or Bracket1, Bracket2,
etc...in that new sheet

Argus

"Dave Peterson" wrote in message
...
I think you could do that with the table that's created from that
second
macro.

You could fill in the empty cells with "bye" so that any bracket
with
less
than
8 entries would have some players that advance to the second round.
(I'd
try to
make it so that no bye played a bye, though.)

And, to me, it makes more sense to fill them the way the macro
did--you'd
never
end up with one bracket with just 1 name.



OdAwG wrote:

Hey David,

Oh cool, thanks for the help.

I guess to make things all clear, what I am trying to do is
automate a
bracket sheet that we can use for bowling. People would sign up to
enter
into brackets. All they would have to do is give their names and
the
number
of brackets they want. The 8 bracket sheet is used (because we
only
bowl
3
games).

Game1 Game2 Game3

----------|
|--------|
----------| |
|------|
----------| | |
|--------| |
----------| |
|-----------
----------| |
|--------| |
----------| | |
|------|
----------| |
|--------|
----------|

In order to do a bracket sheet like above, we have to have the
names
filled
in. I thought the best way was to create a master sheet with only
the
bowlers name and number of brackets entered. Based on the number
of
brackets entry, will determing the number of brackets to use. That
is
what
we were trying to accomplish. I wanted to do this automatically
with
a
macro so that we wouldn't do it manually week to week.

Once the tables or brackets were completed, I already have a macro
that
would copy those names over to the bracket sheets and then
randomize
them
into the bracket itself.

001. take table1 and copy to a sheet called bracket1 (do the same
with
each
table)
002. Once on the bracket sheet, I would randomize the name and
place
them
in the brackets

Does that make it more clearer? I know what I want to say but
typing
it
out
is has been a challenge. hehe ahhah LOL :)

Argus

"Dave Peterson" wrote in message
...
You can determine the maximum number of tables without sorting.
Just
like
using: =max(b1:b99) in a worksheet.

And I see no compelling reason to fill each table with 8 names
first.
So
I
wouldn't bother. In fact, I would think that populating each
table
so
that
they're approximately the same size (give or take one) would be
better
than
having 4 tables with 8 and one table with 1.

But I really don't understand what you're doing.

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim MaxEntries As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow,
"B"),
_
.Cells(LastRow,
"B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \
HowManyPerTable

MaxEntries = Application.Max(.Range(.Cells(FirstRow, "B"),
_
.Cells(LastRow,
"B")))

If HowManyTables < MaxEntries Then
HowManyTables = MaxEntries
End If

If TotalEntries HowManyPerTable * HowManyTables Then
MsgBox "Something has to give--not enough room in the
table"
Exit Sub
End If

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub



OdAwG wrote:

Hey Dave,

Sorry for not being clear.

Let me try again:
001. The total number of table columns created depends on the
total
number
of entries.
002. To calculate the total number of tables columns, add up
all
entries
and divide by 8
003. Each table can only have a max of 8 names per table.
004. No duplications of names in the table columns. So, if
Oscar
has
5,
then his name should
should appear in five (5) separate tables.

I like the way you've done it in the your code below. I guess
what
needs
to
happen first then is to sort the entry column first decended to
get
the
highest numeric value up top first to create the total number of
table
columns to create.

But, i think the tables should be filled first with eight (8)
names
before
moving to the next table. What do you think?

Argus

"Dave Peterson" wrote in message
...
I don't think you explained how you got that second
layout--well,
not
enough for
me to understand.

I don't see why Chucky Cheese couldn't be in the 5th
table--right
next
to
the
4th Adam Doe entry. Then Chucky Cheese could be continued on
the
next
line in
tables 1-3.

Then David Crocket would pick up in the next column (table 4,
table
5),
then the
next row tables 1 and 2.

And so forth.

If this kind of layout is ok:

Oscar Delahoya Oscar Delahoya Oscar Delahoya Oscar Delahoya
Oscar
Delahoya
Quincy Jones Quincy Jones Quincy Jones Quincy Jones
Quincy
Jones
Xiasha Jones Xiasha Jones Xiasha Jones Xiasha Jones
Xiasha
Jones
Adam Doe Adam Doe Adam Doe Adam Doe
chucky
cheese
chucky cheese chucky cheese chucky cheese David Crocket
David
Crocket
David Crocket David Crocket Frank Valins Frank Valins
Frank
Valins
Frank Valins Robert Ronins Robert Ronins Robert Ronins
Robert
Ronins
Kent Clark

Just filling them up across the tables, then going to the next
row,
then
you
could use a macro like:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow,
"B"),
_

.Cells(LastRow,
"B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \
HowManyPerTable

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value
If HowManyForThisPerson HowManyTables Then
MsgBox "not enough tables for: " & ThisPerson _
& "Stopping!"
Exit Sub
End If

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub

And I don't understand what would happen if Oscar had 6
entries????

Maybe the maximum number of tables should be the based on the
max
number
at the
table and the number of entries--along with the maximum number
of
entries
any
one person has???

So if Oscar had 7 entries, but the calculation showed 5 tables
were
necessary,
then you'd end up going with 7 tables.




OdAwG wrote:

Hello All,

My question is, how can I do this automatically with a macro
or
with a
high
speed low drag formula. I took this over from someone who
was
doing
it
manually on paper and I converted it into a spreadsheet.
It's
only
been
three months into the new year and I find this very tedious
and
I
now
know
why that person no longer wanted to do this.

Given the following names listed below with the number of
entries,
I
need
to
figure out how many table columns needs to be created and put
each
name
in
each table column only once by their corresponding entries.
In
the
example
below, we have a total of 36 entries (adding up all the
entries).
To
get
the total numbers of tables to create, I divided 36 by 8 (8
names
per
table)
and that gives me a total of 5 table columns to create.

Oscar Delahoya 5
Quincy Jones 5
Xiasha Jones 5
Adam Doe 4
chucky cheese 4
David Crocket 4
Frank Valins 4
Robert Ronins 4
Kent Clark 1
------------------
36/8 = 4.5 or 5 tables

Table1 Table2 Table3 Table4 Table5
------------------------------------------------
Oscar Oscar Oscar Oscar Oscar
Quincy Quincy Quincy Quincy Quincy
Xiasha Xiasha Xiasha Xiasha Xiasha
Adam Adam Adam Adam Kent
Chucky Chucky Chucky Chucky
David David David David
Frank Frank Frank Frank
Robert Robert Robert Robert

Any and all help in this matter is GREATLY APPRECIATED :)

Argus

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

#1. I wouldn't bother with the word Bye until later.
#2. You could sort each column in the same procedure that creates GetNames.
#3. You could populate each worksheet bracket## in the code.

This seemed to work ok for me. (The shufflearray procedure was stolen from a
Tom Ogilvy post):

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim MaxEntries As Long
Dim hCtr As Long

Dim iCol As Long
Dim myRng As Range
Dim myArr As Variant
Dim TestWks As Worksheet
Dim AddressesInBrktWks As Variant
Dim myName As String

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("getnames").Delete 'in case there's an existing sheet
Application.DisplayAlerts = True
On Error GoTo 0
NewWks.Name = "GetNames"

HowManyPerTable = 8

'list the addresses in the order that gets populated
AddressesInBrktWks = Array("A1", "A11", "a6", "a16", _
"A3", "A13", "a8", "a18")

If UBound(AddressesInBrktWks) - LBound(AddressesInBrktWks) + 1 _
< HowManyPerTable Then
MsgBox "Design error!" & vblf _
& "number of addresses doesn't match Number in table!"
Exit Sub
End If

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow, "B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \ HowManyPerTable

MaxEntries = Application.Max(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow, "B")))

If HowManyTables < MaxEntries Then
HowManyTables = MaxEntries
End If

If TotalEntries HowManyPerTable * HowManyTables Then
MsgBox "Something has to give--not enough room in the table"
Exit Sub
End If

With NewWks.Range("a1").Resize(1, HowManyTables)
.Formula = "=""Bracket""& column()"
.Value = .Value
End With

oRow = 1 'cause you're gonna have headers in row 1
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With

With NewWks
.UsedRange.Columns.AutoFit
For iCol = 1 To HowManyTables
Set myRng = .Range(.Cells(2, iCol), _
.Cells(.Rows.Count, iCol).End(xlUp))
If myRng.Cells.Count 1 Then
myRng.Value = Application.Transpose(ShuffleArray(myRng.Value))
End If

Set TestWks = Nothing
On Error Resume Next
Set TestWks = Worksheets(.Cells(1, iCol).Value)
On Error GoTo 0

If TestWks Is Nothing Then
MsgBox "Design error!" & vbLf & "No sheet named: " _
& .Cells(1, iCol).Value & vbLf & "Stopping!"
Exit Sub
Else
'avoid the headers
For iRow = 2 To HowManyPerTable + 1
myName = .Cells(iRow, iCol).Value
If myName = "" Then
myName = "Bye"
End If
'just for testing
myName = iRow - 1 & "." & " " & myName
TestWks.Range(AddressesInBrktWks(iRow - 2)).Value _
= myName
Next iRow
End If
Next iCol
End With
End Sub
Public Function ShuffleArray(varr)

'
' Algorithm from:
' The Art of Computer Programming: _
' SemiNumerical Algorithms Vol 2, 2nd Ed.
' Donald Knuth
' p. 139
'
'
Dim List() As Variant
Dim t As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim VarTemp As Variant

t = UBound(varr, 1) - LBound(varr, 1) + 1
ReDim List(1 To t)
For i = 1 To t
List(i) = varr(i, 1)
Next i

j = t
Randomize
For i = 1 To t
k = Int(Rnd * j) + 1
VarTemp = List(j)
List(j) = List(k)
List(k) = VarTemp
j = j - 1
Next i
ShuffleArray = List
End Function






OdAwG wrote:

Hey Sir,

I think I got that one solved but need your help on two (2) things then.

1. within your code that you have provided, is their a way to fill in the
word bye into those brackets that have less than 8 names.

2. Also within your code, is there a way to copy those names from each
(bracket) table to each sheet. The table names used is the same as the
sheet name. Bracket1, Bracket2, and etc... The names would need to be
pasted in Column O in each sheet.

With the above said, I think we can do the following using rand() to assign
a numeric value. Using Columns O, P, Q, and R is static and the same on
every sheet.

1. I would copy the names from Bracket1 from sheet "GetNames" to sheet
"Bracket1" to column O
2. I would then make a copy of Column O and paste it into Column P.
3. Then I would place =rand() in column Q next to each name to get a
numeric value.
4. I would then highlight both column P and Q and sort by column Q.
5. finally in column R would place the following formula
=IF(O1=P1,"ReRun"," ")
6. I would hit F9 to generate new random numbers
7. If "ReRun" appears in any row in column R the hit F9 again until

What do y ou think,

Argus

"Dave Peterson" wrote in message
...
Glad you got something you can work with.

I don't have any specific code suggestion, but when you're laying out the
brackets with less 8 people, I would think you'd want to give byes to as
many
people as possible. You wouldn't want a bye playing a bye if and have
people
playing each other.

I think I'd seed them in this kind of order (with 1-x real people and x-8
the
byes):

1
----------+
5 |----
----------+

3
----------+
7 |----
----------+

2
----------+
6 |----
----------+

4
----------+
8 |----
----------+







OdAwG wrote:

Hey Mr. "DP"

U-Da-Man,

Thanks for the help, yet again. I guess you can tell, I'm a newbie huh
...
:)

Argus

"Dave Peterson" wrote in message
...
#1. You could add this code:

Set NewWks = Worksheets.Add
on error resume next
worksheets("getnames").delete 'in case there's an existing sheet
on error goto 0
newwks.name = "GetNames"

#2. change this:
oRow = 0
to
oRow = 1 'cause you're gonna have headers in row 1
with newwks.range("a1").resize(1,howmanytables)
.formula = "=""Bracket""& column()"
.value = .value
end with

OdAwG wrote:

Hey D

I agree with your suggestion and I will do it the way you have
suggested.
However, one other quick question, ok acutally two questions

001. in your code Set NewWks = Worksheets.Add - can we name this to
a
unique name like GetNames
002. can we add column heading Table1, Table2 or Bracket1, Bracket2,
etc...in that new sheet

Argus

"Dave Peterson" wrote in message
...
I think you could do that with the table that's created from that
second
macro.

You could fill in the empty cells with "bye" so that any bracket
with
less
than
8 entries would have some players that advance to the second round.
(I'd
try to
make it so that no bye played a bye, though.)

And, to me, it makes more sense to fill them the way the macro
did--you'd
never
end up with one bracket with just 1 name.



OdAwG wrote:

Hey David,

Oh cool, thanks for the help.

I guess to make things all clear, what I am trying to do is
automate a
bracket sheet that we can use for bowling. People would sign up to
enter
into brackets. All they would have to do is give their names and
the
number
of brackets they want. The 8 bracket sheet is used (because we
only
bowl
3
games).

Game1 Game2 Game3

----------|
|--------|
----------| |
|------|
----------| | |
|--------| |
----------| |
|-----------
----------| |
|--------| |
----------| | |
|------|
----------| |
|--------|
----------|

In order to do a bracket sheet like above, we have to have the
names
filled
in. I thought the best way was to create a master sheet with only
the
bowlers name and number of brackets entered. Based on the number
of
brackets entry, will determing the number of brackets to use. That
is
what
we were trying to accomplish. I wanted to do this automatically
with
a
macro so that we wouldn't do it manually week to week.

Once the tables or brackets were completed, I already have a macro
that
would copy those names over to the bracket sheets and then
randomize
them
into the bracket itself.

001. take table1 and copy to a sheet called bracket1 (do the same
with
each
table)
002. Once on the bracket sheet, I would randomize the name and
place
them
in the brackets

Does that make it more clearer? I know what I want to say but
typing
it
out
is has been a challenge. hehe ahhah LOL :)

Argus

"Dave Peterson" wrote in message
...
You can determine the maximum number of tables without sorting.
Just
like
using: =max(b1:b99) in a worksheet.

And I see no compelling reason to fill each table with 8 names
first.
So
I
wouldn't bother. In fact, I would think that populating each
table
so
that
they're approximately the same size (give or take one) would be
better
than
having 4 tables with 8 and one table with 1.

But I really don't understand what you're doing.

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim MaxEntries As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow,
"B"),
_
.Cells(LastRow,
"B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \
HowManyPerTable

MaxEntries = Application.Max(.Range(.Cells(FirstRow, "B"),
_
.Cells(LastRow,
"B")))

If HowManyTables < MaxEntries Then
HowManyTables = MaxEntries
End If

If TotalEntries HowManyPerTable * HowManyTables Then
MsgBox "Something has to give--not enough room in the
table"
Exit Sub
End If

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub



OdAwG wrote:

Hey Dave,

Sorry for not being clear.

Let me try again:
001. The total number of table columns created depends on the
total
number
of entries.
002. To calculate the total number of tables columns, add up
all
entries
and divide by 8
003. Each table can only have a max of 8 names per table.
004. No duplications of names in the table columns. So, if
Oscar
has
5,
then his name should
should appear in five (5) separate tables.

I like the way you've done it in the your code below. I guess
what
needs
to
happen first then is to sort the entry column first decended to
get
the
highest numeric value up top first to create the total number of
table
columns to create.

But, i think the tables should be filled first with eight (8)
names
before
moving to the next table. What do you think?

Argus

"Dave Peterson" wrote in message
...
I don't think you explained how you got that second
layout--well,
not
enough for
me to understand.

I don't see why Chucky Cheese couldn't be in the 5th
table--right
next
to
the
4th Adam Doe entry. Then Chucky Cheese could be continued on
the
next
line in
tables 1-3.

Then David Crocket would pick up in the next column (table 4,
table
5),
then the
next row tables 1 and 2.

And so forth.

If this kind of layout is ok:

Oscar Delahoya Oscar Delahoya Oscar Delahoya Oscar Delahoya
Oscar
Delahoya
Quincy Jones Quincy Jones Quincy Jones Quincy Jones
Quincy
Jones
Xiasha Jones Xiasha Jones Xiasha Jones Xiasha Jones
Xiasha
Jones
Adam Doe Adam Doe Adam Doe Adam Doe
chucky
cheese
chucky cheese chucky cheese chucky cheese David Crocket
David
Crocket
David Crocket David Crocket Frank Valins Frank Valins
Frank
Valins
Frank Valins Robert Ronins Robert Ronins Robert Ronins
Robert
Ronins
Kent Clark

Just filling them up across the tables, then going to the next
row,
then
you
could use a macro like:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow,
"B"),
_

.Cells(LastRow,
"B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \
HowManyPerTable

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value
If HowManyForThisPerson HowManyTables Then
MsgBox "not enough tables for: " & ThisPerson _
& "Stopping!"
Exit Sub
End If

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub

And I don't understand what would happen if Oscar had 6
entries????

Maybe the maximum number of tables should be the based on the
max
number
at the
table and the number of entries--along with the maximum number
of
entries
any
one person has???

So if Oscar had 7 entries, but the calculation showed 5 tables
were
necessary,
then you'd end up going with 7 tables.




OdAwG wrote:

Hello All,

My question is, how can I do this automatically with a macro
or
with a
high
speed low drag formula. I took this over from someone who
was
doing
it
manually on paper and I converted it into a spreadsheet.
It's
only
been
three months into the new year and I find this very tedious
and
I
now
know
why that person no longer wanted to do this.

Given the following names listed below with the number of
entries,
I
need
to
figure out how many table columns needs to be created and put
each
name
in
each table column only once by their corresponding entries.
In
the
example
below, we have a total of 36 entries (adding up all the
entries).
To
get
the total numbers of tables to create, I divided 36 by 8 (8
names
per
table)
and that gives me a total of 5 table columns to create.

Oscar Delahoya 5
Quincy Jones 5
Xiasha Jones 5
Adam Doe 4
chucky cheese 4
David Crocket 4
Frank Valins 4
Robert Ronins 4
Kent Clark 1
------------------
36/8 = 4.5 or 5 tables

Table1 Table2 Table3 Table4 Table5
------------------------------------------------
Oscar Oscar Oscar Oscar Oscar
Quincy Quincy Quincy Quincy Quincy
Xiasha Xiasha Xiasha Xiasha Xiasha
Adam Adam Adam Adam Kent
Chucky Chucky Chucky Chucky
David David David David
Frank Frank Frank Frank
Robert Robert Robert Robert

Any and all help in this matter is GREATLY APPRECIATED :)

Argus

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Mr. "D",

I have a need for your services one last time. With your help, I have
chuncked all of my work and went a 100% yours in regards to this adventure.

in the master sheet, where I have entered all the names and the number of
brackets for each person, listed below:

Name BRKTS
John Doe 4

I have now include the following:

Name Brkts HDCP GM1 GM2 GM3
John Doe 4 45 180 195 210

What I need help with now is the following:

How can I include the HDCP for each bowler and then include their games
after they have bowled it to the corresponding brackets.

1. so after the brackets are made with the code that you have already
provided how can we include the persons handicap.

2. After the bowlers bowl their first game, I would like to enter that into
the master sheet next to their names and have it populate to the
corresponding Brackets where there name is.

I was planning on doing this manually with four or five brackets, (which is
managable manually) but what happens if I have 10 or more brackets. If I
could enter the bowlers score on one sheet and then have it go thru each
brackets and populate that score to each name, then it's only one place
where i have to update instead of all bracket sheets.

Is this doable sir? As always, your assistance and help in this matter is
greatly appreciated.

Argus



"Dave Peterson" wrote in message
...
#1. I wouldn't bother with the word Bye until later.
#2. You could sort each column in the same procedure that creates
GetNames.
#3. You could populate each worksheet bracket## in the code.

This seemed to work ok for me. (The shufflearray procedure was stolen
from a
Tom Ogilvy post):

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim MaxEntries As Long
Dim hCtr As Long

Dim iCol As Long
Dim myRng As Range
Dim myArr As Variant
Dim TestWks As Worksheet
Dim AddressesInBrktWks As Variant
Dim myName As String

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("getnames").Delete 'in case there's an existing sheet
Application.DisplayAlerts = True
On Error GoTo 0
NewWks.Name = "GetNames"

HowManyPerTable = 8

'list the addresses in the order that gets populated
AddressesInBrktWks = Array("A1", "A11", "a6", "a16", _
"A3", "A13", "a8", "a18")

If UBound(AddressesInBrktWks) - LBound(AddressesInBrktWks) + 1 _
< HowManyPerTable Then
MsgBox "Design error!" & vblf _
& "number of addresses doesn't match Number in table!"
Exit Sub
End If

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow, "B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \
HowManyPerTable

MaxEntries = Application.Max(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow, "B")))

If HowManyTables < MaxEntries Then
HowManyTables = MaxEntries
End If

If TotalEntries HowManyPerTable * HowManyTables Then
MsgBox "Something has to give--not enough room in the table"
Exit Sub
End If

With NewWks.Range("a1").Resize(1, HowManyTables)
.Formula = "=""Bracket""& column()"
.Value = .Value
End With

oRow = 1 'cause you're gonna have headers in row 1
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With

With NewWks
.UsedRange.Columns.AutoFit
For iCol = 1 To HowManyTables
Set myRng = .Range(.Cells(2, iCol), _
.Cells(.Rows.Count, iCol).End(xlUp))
If myRng.Cells.Count 1 Then
myRng.Value =
Application.Transpose(ShuffleArray(myRng.Value))
End If

Set TestWks = Nothing
On Error Resume Next
Set TestWks = Worksheets(.Cells(1, iCol).Value)
On Error GoTo 0

If TestWks Is Nothing Then
MsgBox "Design error!" & vbLf & "No sheet named: " _
& .Cells(1, iCol).Value & vbLf & "Stopping!"
Exit Sub
Else
'avoid the headers
For iRow = 2 To HowManyPerTable + 1
myName = .Cells(iRow, iCol).Value
If myName = "" Then
myName = "Bye"
End If
'just for testing
myName = iRow - 1 & "." & " " & myName
TestWks.Range(AddressesInBrktWks(iRow - 2)).Value _
= myName
Next iRow
End If
Next iCol
End With
End Sub
Public Function ShuffleArray(varr)

'
' Algorithm from:
' The Art of Computer Programming: _
' SemiNumerical Algorithms Vol 2, 2nd Ed.
' Donald Knuth
' p. 139
'
'
Dim List() As Variant
Dim t As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim VarTemp As Variant

t = UBound(varr, 1) - LBound(varr, 1) + 1
ReDim List(1 To t)
For i = 1 To t
List(i) = varr(i, 1)
Next i

j = t
Randomize
For i = 1 To t
k = Int(Rnd * j) + 1
VarTemp = List(j)
List(j) = List(k)
List(k) = VarTemp
j = j - 1
Next i
ShuffleArray = List
End Function






OdAwG wrote:

Hey Sir,

I think I got that one solved but need your help on two (2) things then.

1. within your code that you have provided, is their a way to fill in
the
word bye into those brackets that have less than 8 names.

2. Also within your code, is there a way to copy those names from each
(bracket) table to each sheet. The table names used is the same as the
sheet name. Bracket1, Bracket2, and etc... The names would need to be
pasted in Column O in each sheet.

With the above said, I think we can do the following using rand() to
assign
a numeric value. Using Columns O, P, Q, and R is static and the same on
every sheet.

1. I would copy the names from Bracket1 from sheet "GetNames" to
sheet
"Bracket1" to column O
2. I would then make a copy of Column O and paste it into Column P.
3. Then I would place =rand() in column Q next to each name to get a
numeric value.
4. I would then highlight both column P and Q and sort by column Q.
5. finally in column R would place the following formula
=IF(O1=P1,"ReRun"," ")
6. I would hit F9 to generate new random numbers
7. If "ReRun" appears in any row in column R the hit F9 again until

What do y ou think,

Argus

"Dave Peterson" wrote in message
...
Glad you got something you can work with.

I don't have any specific code suggestion, but when you're laying out
the
brackets with less 8 people, I would think you'd want to give byes to
as
many
people as possible. You wouldn't want a bye playing a bye if and have
people
playing each other.

I think I'd seed them in this kind of order (with 1-x real people and
x-8
the
byes):

1
----------+
5 |----
----------+

3
----------+
7 |----
----------+

2
----------+
6 |----
----------+

4
----------+
8 |----
----------+







OdAwG wrote:

Hey Mr. "DP"

U-Da-Man,

Thanks for the help, yet again. I guess you can tell, I'm a newbie
huh
...
:)

Argus

"Dave Peterson" wrote in message
...
#1. You could add this code:

Set NewWks = Worksheets.Add
on error resume next
worksheets("getnames").delete 'in case there's an existing sheet
on error goto 0
newwks.name = "GetNames"

#2. change this:
oRow = 0
to
oRow = 1 'cause you're gonna have headers in row 1
with newwks.range("a1").resize(1,howmanytables)
.formula = "=""Bracket""& column()"
.value = .value
end with

OdAwG wrote:

Hey D

I agree with your suggestion and I will do it the way you have
suggested.
However, one other quick question, ok acutally two questions

001. in your code Set NewWks = Worksheets.Add - can we name this
to
a
unique name like GetNames
002. can we add column heading Table1, Table2 or Bracket1,
Bracket2,
etc...in that new sheet

Argus

"Dave Peterson" wrote in message
...
I think you could do that with the table that's created from that
second
macro.

You could fill in the empty cells with "bye" so that any bracket
with
less
than
8 entries would have some players that advance to the second
round.
(I'd
try to
make it so that no bye played a bye, though.)

And, to me, it makes more sense to fill them the way the macro
did--you'd
never
end up with one bracket with just 1 name.



OdAwG wrote:

Hey David,

Oh cool, thanks for the help.

I guess to make things all clear, what I am trying to do is
automate a
bracket sheet that we can use for bowling. People would sign up
to
enter
into brackets. All they would have to do is give their names
and
the
number
of brackets they want. The 8 bracket sheet is used (because we
only
bowl
3
games).

Game1 Game2 Game3

----------|
|--------|
----------| |
|------|
----------| | |
|--------| |
----------| |
|-----------
----------| |
|--------| |
----------| | |
|------|
----------| |
|--------|
----------|

In order to do a bracket sheet like above, we have to have the
names
filled
in. I thought the best way was to create a master sheet with
only
the
bowlers name and number of brackets entered. Based on the
number
of
brackets entry, will determing the number of brackets to use.
That
is
what
we were trying to accomplish. I wanted to do this automatically
with
a
macro so that we wouldn't do it manually week to week.

Once the tables or brackets were completed, I already have a
macro
that
would copy those names over to the bracket sheets and then
randomize
them
into the bracket itself.

001. take table1 and copy to a sheet called bracket1 (do the
same
with
each
table)
002. Once on the bracket sheet, I would randomize the name and
place
them
in the brackets

Does that make it more clearer? I know what I want to say but
typing
it
out
is has been a challenge. hehe ahhah LOL :)

Argus

"Dave Peterson" wrote in message
...
You can determine the maximum number of tables without
sorting.
Just
like
using: =max(b1:b99) in a worksheet.

And I see no compelling reason to fill each table with 8 names
first.
So
I
wouldn't bother. In fact, I would think that populating each
table
so
that
they're approximately the same size (give or take one) would
be
better
than
having 4 tables with 8 and one table with 1.

But I really don't understand what you're doing.

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim MaxEntries As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow,
"B"),
_

.Cells(LastRow,
"B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \
HowManyPerTable

MaxEntries = Application.Max(.Range(.Cells(FirstRow,
"B"),
_

.Cells(LastRow,
"B")))

If HowManyTables < MaxEntries Then
HowManyTables = MaxEntries
End If

If TotalEntries HowManyPerTable * HowManyTables Then
MsgBox "Something has to give--not enough room in
the
table"
Exit Sub
End If

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub



OdAwG wrote:

Hey Dave,

Sorry for not being clear.

Let me try again:
001. The total number of table columns created depends on
the
total
number
of entries.
002. To calculate the total number of tables columns, add up
all
entries
and divide by 8
003. Each table can only have a max of 8 names per table.
004. No duplications of names in the table columns. So, if
Oscar
has
5,
then his name should
should appear in five (5) separate tables.

I like the way you've done it in the your code below. I
guess
what
needs
to
happen first then is to sort the entry column first decended
to
get
the
highest numeric value up top first to create the total number
of
table
columns to create.

But, i think the tables should be filled first with eight (8)
names
before
moving to the next table. What do you think?

Argus

"Dave Peterson" wrote in message
...
I don't think you explained how you got that second
layout--well,
not
enough for
me to understand.

I don't see why Chucky Cheese couldn't be in the 5th
table--right
next
to
the
4th Adam Doe entry. Then Chucky Cheese could be continued
on
the
next
line in
tables 1-3.

Then David Crocket would pick up in the next column (table
4,
table
5),
then the
next row tables 1 and 2.

And so forth.

If this kind of layout is ok:

Oscar Delahoya Oscar Delahoya Oscar Delahoya Oscar Delahoya
Oscar
Delahoya
Quincy Jones Quincy Jones Quincy Jones Quincy Jones
Quincy
Jones
Xiasha Jones Xiasha Jones Xiasha Jones Xiasha Jones
Xiasha
Jones
Adam Doe Adam Doe Adam Doe Adam Doe
chucky
cheese
chucky cheese chucky cheese chucky cheese David Crocket
David
Crocket
David Crocket David Crocket Frank Valins Frank Valins
Frank
Valins
Frank Valins Robert Ronins Robert Ronins Robert Ronins
Robert
Ronins
Kent Clark

Just filling them up across the tables, then going to the
next
row,
then
you
could use a macro like:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries =
Application.Sum(.Range(.Cells(FirstRow,
"B"),
_

.Cells(LastRow,
"B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1)
\
HowManyPerTable

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value
If HowManyForThisPerson HowManyTables Then
MsgBox "not enough tables for: " &
ThisPerson _
& "Stopping!"
Exit Sub
End If

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub

And I don't understand what would happen if Oscar had 6
entries????

Maybe the maximum number of tables should be the based on
the
max
number
at the
table and the number of entries--along with the maximum
number
of
entries
any
one person has???

So if Oscar had 7 entries, but the calculation showed 5
tables
were
necessary,
then you'd end up going with 7 tables.




OdAwG wrote:

Hello All,

My question is, how can I do this automatically with a
macro
or
with a
high
speed low drag formula. I took this over from someone who
was
doing
it
manually on paper and I converted it into a spreadsheet.
It's
only
been
three months into the new year and I find this very
tedious
and
I
now
know
why that person no longer wanted to do this.

Given the following names listed below with the number of
entries,
I
need
to
figure out how many table columns needs to be created and
put
each
name
in
each table column only once by their corresponding
entries.
In
the
example
below, we have a total of 36 entries (adding up all the
entries).
To
get
the total numbers of tables to create, I divided 36 by 8
(8
names
per
table)
and that gives me a total of 5 table columns to create.

Oscar Delahoya 5
Quincy Jones 5
Xiasha Jones 5
Adam Doe 4
chucky cheese 4
David Crocket 4
Frank Valins 4
Robert Ronins 4
Kent Clark 1
------------------
36/8 = 4.5 or 5 tables

Table1 Table2 Table3 Table4 Table5
------------------------------------------------
Oscar Oscar Oscar Oscar Oscar
Quincy Quincy Quincy Quincy Quincy
Xiasha Xiasha Xiasha Xiasha Xiasha
Adam Adam Adam Adam Kent
Chucky Chucky Chucky Chucky
David David David David
Frank Frank Frank Frank
Robert Robert Robert Robert

Any and all help in this matter is GREATLY APPRECIATED :)

Argus

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

#1. I would concatenate the hdcp and the name into one cell.

=a2&" "&c2
and use that column (instead of column A) when creating the tables.

#2. That's beyond the scope of the newsgroup (for me anyway). But you could
try it yourself and if you have problems, post back with your specific
question. Or maybe someone else will chime in.



OdAwG wrote:

Mr. "D",

I have a need for your services one last time. With your help, I have
chuncked all of my work and went a 100% yours in regards to this adventure.

in the master sheet, where I have entered all the names and the number of
brackets for each person, listed below:

Name BRKTS
John Doe 4

I have now include the following:

Name Brkts HDCP GM1 GM2 GM3
John Doe 4 45 180 195 210

What I need help with now is the following:

How can I include the HDCP for each bowler and then include their games
after they have bowled it to the corresponding brackets.

1. so after the brackets are made with the code that you have already
provided how can we include the persons handicap.

2. After the bowlers bowl their first game, I would like to enter that into
the master sheet next to their names and have it populate to the
corresponding Brackets where there name is.

I was planning on doing this manually with four or five brackets, (which is
managable manually) but what happens if I have 10 or more brackets. If I
could enter the bowlers score on one sheet and then have it go thru each
brackets and populate that score to each name, then it's only one place
where i have to update instead of all bracket sheets.

Is this doable sir? As always, your assistance and help in this matter is
greatly appreciated.

Argus

"Dave Peterson" wrote in message
...
#1. I wouldn't bother with the word Bye until later.
#2. You could sort each column in the same procedure that creates
GetNames.
#3. You could populate each worksheet bracket## in the code.

This seemed to work ok for me. (The shufflearray procedure was stolen
from a
Tom Ogilvy post):

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim MaxEntries As Long
Dim hCtr As Long

Dim iCol As Long
Dim myRng As Range
Dim myArr As Variant
Dim TestWks As Worksheet
Dim AddressesInBrktWks As Variant
Dim myName As String

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("getnames").Delete 'in case there's an existing sheet
Application.DisplayAlerts = True
On Error GoTo 0
NewWks.Name = "GetNames"

HowManyPerTable = 8

'list the addresses in the order that gets populated
AddressesInBrktWks = Array("A1", "A11", "a6", "a16", _
"A3", "A13", "a8", "a18")

If UBound(AddressesInBrktWks) - LBound(AddressesInBrktWks) + 1 _
< HowManyPerTable Then
MsgBox "Design error!" & vblf _
& "number of addresses doesn't match Number in table!"
Exit Sub
End If

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow, "B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \
HowManyPerTable

MaxEntries = Application.Max(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow, "B")))

If HowManyTables < MaxEntries Then
HowManyTables = MaxEntries
End If

If TotalEntries HowManyPerTable * HowManyTables Then
MsgBox "Something has to give--not enough room in the table"
Exit Sub
End If

With NewWks.Range("a1").Resize(1, HowManyTables)
.Formula = "=""Bracket""& column()"
.Value = .Value
End With

oRow = 1 'cause you're gonna have headers in row 1
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With

With NewWks
.UsedRange.Columns.AutoFit
For iCol = 1 To HowManyTables
Set myRng = .Range(.Cells(2, iCol), _
.Cells(.Rows.Count, iCol).End(xlUp))
If myRng.Cells.Count 1 Then
myRng.Value =
Application.Transpose(ShuffleArray(myRng.Value))
End If

Set TestWks = Nothing
On Error Resume Next
Set TestWks = Worksheets(.Cells(1, iCol).Value)
On Error GoTo 0

If TestWks Is Nothing Then
MsgBox "Design error!" & vbLf & "No sheet named: " _
& .Cells(1, iCol).Value & vbLf & "Stopping!"
Exit Sub
Else
'avoid the headers
For iRow = 2 To HowManyPerTable + 1
myName = .Cells(iRow, iCol).Value
If myName = "" Then
myName = "Bye"
End If
'just for testing
myName = iRow - 1 & "." & " " & myName
TestWks.Range(AddressesInBrktWks(iRow - 2)).Value _
= myName
Next iRow
End If
Next iCol
End With
End Sub
Public Function ShuffleArray(varr)

'
' Algorithm from:
' The Art of Computer Programming: _
' SemiNumerical Algorithms Vol 2, 2nd Ed.
' Donald Knuth
' p. 139
'
'
Dim List() As Variant
Dim t As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim VarTemp As Variant

t = UBound(varr, 1) - LBound(varr, 1) + 1
ReDim List(1 To t)
For i = 1 To t
List(i) = varr(i, 1)
Next i

j = t
Randomize
For i = 1 To t
k = Int(Rnd * j) + 1
VarTemp = List(j)
List(j) = List(k)
List(k) = VarTemp
j = j - 1
Next i
ShuffleArray = List
End Function






OdAwG wrote:

Hey Sir,

I think I got that one solved but need your help on two (2) things then.

1. within your code that you have provided, is their a way to fill in
the
word bye into those brackets that have less than 8 names.

2. Also within your code, is there a way to copy those names from each
(bracket) table to each sheet. The table names used is the same as the
sheet name. Bracket1, Bracket2, and etc... The names would need to be
pasted in Column O in each sheet.

With the above said, I think we can do the following using rand() to
assign
a numeric value. Using Columns O, P, Q, and R is static and the same on
every sheet.

1. I would copy the names from Bracket1 from sheet "GetNames" to
sheet
"Bracket1" to column O
2. I would then make a copy of Column O and paste it into Column P.
3. Then I would place =rand() in column Q next to each name to get a
numeric value.
4. I would then highlight both column P and Q and sort by column Q.
5. finally in column R would place the following formula
=IF(O1=P1,"ReRun"," ")
6. I would hit F9 to generate new random numbers
7. If "ReRun" appears in any row in column R the hit F9 again until

What do y ou think,

Argus

"Dave Peterson" wrote in message
...
Glad you got something you can work with.

I don't have any specific code suggestion, but when you're laying out
the
brackets with less 8 people, I would think you'd want to give byes to
as
many
people as possible. You wouldn't want a bye playing a bye if and have
people
playing each other.

I think I'd seed them in this kind of order (with 1-x real people and
x-8
the
byes):

1
----------+
5 |----
----------+

3
----------+
7 |----
----------+

2
----------+
6 |----
----------+

4
----------+
8 |----
----------+







OdAwG wrote:

Hey Mr. "DP"

U-Da-Man,

Thanks for the help, yet again. I guess you can tell, I'm a newbie
huh
...
:)

Argus

"Dave Peterson" wrote in message
...
#1. You could add this code:

Set NewWks = Worksheets.Add
on error resume next
worksheets("getnames").delete 'in case there's an existing sheet
on error goto 0
newwks.name = "GetNames"

#2. change this:
oRow = 0
to
oRow = 1 'cause you're gonna have headers in row 1
with newwks.range("a1").resize(1,howmanytables)
.formula = "=""Bracket""& column()"
.value = .value
end with

OdAwG wrote:

Hey D

I agree with your suggestion and I will do it the way you have
suggested.
However, one other quick question, ok acutally two questions

001. in your code Set NewWks = Worksheets.Add - can we name this
to
a
unique name like GetNames
002. can we add column heading Table1, Table2 or Bracket1,
Bracket2,
etc...in that new sheet

Argus

"Dave Peterson" wrote in message
...
I think you could do that with the table that's created from that
second
macro.

You could fill in the empty cells with "bye" so that any bracket
with
less
than
8 entries would have some players that advance to the second
round.
(I'd
try to
make it so that no bye played a bye, though.)

And, to me, it makes more sense to fill them the way the macro
did--you'd
never
end up with one bracket with just 1 name.



OdAwG wrote:

Hey David,

Oh cool, thanks for the help.

I guess to make things all clear, what I am trying to do is
automate a
bracket sheet that we can use for bowling. People would sign up
to
enter
into brackets. All they would have to do is give their names
and
the
number
of brackets they want. The 8 bracket sheet is used (because we
only
bowl
3
games).

Game1 Game2 Game3

----------|
|--------|
----------| |
|------|
----------| | |
|--------| |
----------| |
|-----------
----------| |
|--------| |
----------| | |
|------|
----------| |
|--------|
----------|

In order to do a bracket sheet like above, we have to have the
names
filled
in. I thought the best way was to create a master sheet with
only
the
bowlers name and number of brackets entered. Based on the
number
of
brackets entry, will determing the number of brackets to use.
That
is
what
we were trying to accomplish. I wanted to do this automatically
with
a
macro so that we wouldn't do it manually week to week.

Once the tables or brackets were completed, I already have a
macro
that
would copy those names over to the bracket sheets and then
randomize
them
into the bracket itself.

001. take table1 and copy to a sheet called bracket1 (do the
same
with
each
table)
002. Once on the bracket sheet, I would randomize the name and
place
them
in the brackets

Does that make it more clearer? I know what I want to say but
typing
it
out
is has been a challenge. hehe ahhah LOL :)

Argus

"Dave Peterson" wrote in message
...
You can determine the maximum number of tables without
sorting.
Just
like
using: =max(b1:b99) in a worksheet.

And I see no compelling reason to fill each table with 8 names
first.
So
I
wouldn't bother. In fact, I would think that populating each
table
so
that
they're approximately the same size (give or take one) would
be
better
than
having 4 tables with 8 and one table with 1.

But I really don't understand what you're doing.

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim MaxEntries As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow,
"B"),
_

.Cells(LastRow,
"B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \
HowManyPerTable

MaxEntries = Application.Max(.Range(.Cells(FirstRow,
"B"),
_

.Cells(LastRow,
"B")))

If HowManyTables < MaxEntries Then
HowManyTables = MaxEntries
End If

If TotalEntries HowManyPerTable * HowManyTables Then
MsgBox "Something has to give--not enough room in
the
table"
Exit Sub
End If

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub



OdAwG wrote:

Hey Dave,

Sorry for not being clear.

Let me try again:
001. The total number of table columns created depends on
the
total
number
of entries.
002. To calculate the total number of tables columns, add up
all
entries
and divide by 8
003. Each table can only have a max of 8 names per table.
004. No duplications of names in the table columns. So, if
Oscar
has
5,
then his name should
should appear in five (5) separate tables.

I like the way you've done it in the your code below. I
guess
what
needs
to
happen first then is to sort the entry column first decended
to
get
the
highest numeric value up top first to create the total number
of
table
columns to create.

But, i think the tables should be filled first with eight (8)
names
before
moving to the next table. What do you think?

Argus

"Dave Peterson" wrote in message
...
I don't think you explained how you got that second
layout--well,
not
enough for
me to understand.

I don't see why Chucky Cheese couldn't be in the 5th
table--right
next
to
the
4th Adam Doe entry. Then Chucky Cheese could be continued
on
the
next
line in
tables 1-3.

Then David Crocket would pick up in the next column (table
4,
table
5),
then the
next row tables 1 and 2.

And so forth.

If this kind of layout is ok:

Oscar Delahoya Oscar Delahoya Oscar Delahoya Oscar Delahoya
Oscar
Delahoya
Quincy Jones Quincy Jones Quincy Jones Quincy Jones
Quincy
Jones
Xiasha Jones Xiasha Jones Xiasha Jones Xiasha Jones
Xiasha
Jones
Adam Doe Adam Doe Adam Doe Adam Doe
chucky
cheese
chucky cheese chucky cheese chucky cheese David Crocket
David
Crocket
David Crocket David Crocket Frank Valins Frank Valins
Frank
Valins
Frank Valins Robert Ronins Robert Ronins Robert Ronins
Robert
Ronins
Kent Clark

Just filling them up across the tables, then going to the
next
row,
then
you
could use a macro like:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries =
Application.Sum(.Range(.Cells(FirstRow,
"B"),
_

.Cells(LastRow,
"B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1)
\
HowManyPerTable

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value
If HowManyForThisPerson HowManyTables Then
MsgBox "not enough tables for: " &
ThisPerson _
& "Stopping!"
Exit Sub
End If

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub

And I don't understand what would happen if Oscar had 6
entries????

Maybe the maximum number of tables should be the based on
the
max
number
at the
table and the number of entries--along with the maximum
number
of
entries
any
one person has???

So if Oscar had 7 entries, but the calculation showed 5
tables
were
necessary,
then you'd end up going with 7 tables.




OdAwG wrote:

Hello All,

My question is, how can I do this automatically with a
macro
or
with a
high
speed low drag formula. I took this over from someone who
was
doing
it
manually on paper and I converted it into a spreadsheet.
It's
only
been
three months into the new year and I find this very
tedious
and
I
now
know
why that person no longer wanted to do this.

Given the following names listed below with the number of
entries,
I
need
to
figure out how many table columns needs to be created and
put
each
name
in
each table column only once by their corresponding
entries.
In
the
example
below, we have a total of 36 entries (adding up all the
entries).
To
get
the total numbers of tables to create, I divided 36 by 8
(8
names
per
table)
and that gives me a total of 5 table columns to create.

Oscar Delahoya 5
Quincy Jones 5
Xiasha Jones 5
Adam Doe 4
chucky cheese 4
David Crocket 4
Frank Valins 4
Robert Ronins 4
Kent Clark 1
------------------
36/8 = 4.5 or 5 tables

Table1 Table2 Table3 Table4 Table5
------------------------------------------------
Oscar Oscar Oscar Oscar Oscar
Quincy Quincy Quincy Quincy Quincy
Xiasha Xiasha Xiasha Xiasha Xiasha
Adam Adam Adam Adam Kent
Chucky Chucky Chucky Chucky
David David David David
Frank Frank Frank Frank
Robert Robert Robert Robert

Any and all help in this matter is GREATLY APPRECIATED :)

Argus

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

thanks for all the help sir...i will do that...

Argus


"Dave Peterson" wrote in message
...
#1. I would concatenate the hdcp and the name into one cell.

=a2&" "&c2
and use that column (instead of column A) when creating the tables.

#2. That's beyond the scope of the newsgroup (for me anyway). But you
could
try it yourself and if you have problems, post back with your specific
question. Or maybe someone else will chime in.



OdAwG wrote:

Mr. "D",

I have a need for your services one last time. With your help, I have
chuncked all of my work and went a 100% yours in regards to this
adventure.

in the master sheet, where I have entered all the names and the number of
brackets for each person, listed below:

Name BRKTS
John Doe 4

I have now include the following:

Name Brkts HDCP GM1 GM2 GM3
John Doe 4 45 180 195 210

What I need help with now is the following:

How can I include the HDCP for each bowler and then include their games
after they have bowled it to the corresponding brackets.

1. so after the brackets are made with the code that you have already
provided how can we include the persons handicap.

2. After the bowlers bowl their first game, I would like to enter that
into
the master sheet next to their names and have it populate to the
corresponding Brackets where there name is.

I was planning on doing this manually with four or five brackets, (which
is
managable manually) but what happens if I have 10 or more brackets. If I
could enter the bowlers score on one sheet and then have it go thru each
brackets and populate that score to each name, then it's only one place
where i have to update instead of all bracket sheets.

Is this doable sir? As always, your assistance and help in this matter
is
greatly appreciated.

Argus

"Dave Peterson" wrote in message
...
#1. I wouldn't bother with the word Bye until later.
#2. You could sort each column in the same procedure that creates
GetNames.
#3. You could populate each worksheet bracket## in the code.

This seemed to work ok for me. (The shufflearray procedure was stolen
from a
Tom Ogilvy post):

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim MaxEntries As Long
Dim hCtr As Long

Dim iCol As Long
Dim myRng As Range
Dim myArr As Variant
Dim TestWks As Worksheet
Dim AddressesInBrktWks As Variant
Dim myName As String

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("getnames").Delete 'in case there's an existing sheet
Application.DisplayAlerts = True
On Error GoTo 0
NewWks.Name = "GetNames"

HowManyPerTable = 8

'list the addresses in the order that gets populated
AddressesInBrktWks = Array("A1", "A11", "a6", "a16", _
"A3", "A13", "a8", "a18")

If UBound(AddressesInBrktWks) - LBound(AddressesInBrktWks) + 1 _
< HowManyPerTable Then
MsgBox "Design error!" & vblf _
& "number of addresses doesn't match Number in table!"
Exit Sub
End If

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries = Application.Sum(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow,
"B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1) \
HowManyPerTable

MaxEntries = Application.Max(.Range(.Cells(FirstRow, "B"), _
.Cells(LastRow,
"B")))

If HowManyTables < MaxEntries Then
HowManyTables = MaxEntries
End If

If TotalEntries HowManyPerTable * HowManyTables Then
MsgBox "Something has to give--not enough room in the table"
Exit Sub
End If

With NewWks.Range("a1").Resize(1, HowManyTables)
.Formula = "=""Bracket""& column()"
.Value = .Value
End With

oRow = 1 'cause you're gonna have headers in row 1
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With

With NewWks
.UsedRange.Columns.AutoFit
For iCol = 1 To HowManyTables
Set myRng = .Range(.Cells(2, iCol), _
.Cells(.Rows.Count, iCol).End(xlUp))
If myRng.Cells.Count 1 Then
myRng.Value =
Application.Transpose(ShuffleArray(myRng.Value))
End If

Set TestWks = Nothing
On Error Resume Next
Set TestWks = Worksheets(.Cells(1, iCol).Value)
On Error GoTo 0

If TestWks Is Nothing Then
MsgBox "Design error!" & vbLf & "No sheet named: " _
& .Cells(1, iCol).Value & vbLf & "Stopping!"
Exit Sub
Else
'avoid the headers
For iRow = 2 To HowManyPerTable + 1
myName = .Cells(iRow, iCol).Value
If myName = "" Then
myName = "Bye"
End If
'just for testing
myName = iRow - 1 & "." & " " & myName
TestWks.Range(AddressesInBrktWks(iRow - 2)).Value _
= myName
Next iRow
End If
Next iCol
End With
End Sub
Public Function ShuffleArray(varr)

'
' Algorithm from:
' The Art of Computer Programming: _
' SemiNumerical Algorithms Vol 2, 2nd Ed.
' Donald Knuth
' p. 139
'
'
Dim List() As Variant
Dim t As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim VarTemp As Variant

t = UBound(varr, 1) - LBound(varr, 1) + 1
ReDim List(1 To t)
For i = 1 To t
List(i) = varr(i, 1)
Next i

j = t
Randomize
For i = 1 To t
k = Int(Rnd * j) + 1
VarTemp = List(j)
List(j) = List(k)
List(k) = VarTemp
j = j - 1
Next i
ShuffleArray = List
End Function






OdAwG wrote:

Hey Sir,

I think I got that one solved but need your help on two (2) things
then.

1. within your code that you have provided, is their a way to fill in
the
word bye into those brackets that have less than 8 names.

2. Also within your code, is there a way to copy those names from
each
(bracket) table to each sheet. The table names used is the same as
the
sheet name. Bracket1, Bracket2, and etc... The names would need to
be
pasted in Column O in each sheet.

With the above said, I think we can do the following using rand() to
assign
a numeric value. Using Columns O, P, Q, and R is static and the same
on
every sheet.

1. I would copy the names from Bracket1 from sheet "GetNames" to
sheet
"Bracket1" to column O
2. I would then make a copy of Column O and paste it into Column
P.
3. Then I would place =rand() in column Q next to each name to
get a
numeric value.
4. I would then highlight both column P and Q and sort by column
Q.
5. finally in column R would place the following formula
=IF(O1=P1,"ReRun"," ")
6. I would hit F9 to generate new random numbers
7. If "ReRun" appears in any row in column R the hit F9 again
until

What do y ou think,

Argus

"Dave Peterson" wrote in message
...
Glad you got something you can work with.

I don't have any specific code suggestion, but when you're laying
out
the
brackets with less 8 people, I would think you'd want to give byes
to
as
many
people as possible. You wouldn't want a bye playing a bye if and
have
people
playing each other.

I think I'd seed them in this kind of order (with 1-x real people
and
x-8
the
byes):

1
----------+
5 |----
----------+

3
----------+
7 |----
----------+

2
----------+
6 |----
----------+

4
----------+
8 |----
----------+







OdAwG wrote:

Hey Mr. "DP"

U-Da-Man,

Thanks for the help, yet again. I guess you can tell, I'm a newbie
huh
...
:)

Argus

"Dave Peterson" wrote in message
...
#1. You could add this code:

Set NewWks = Worksheets.Add
on error resume next
worksheets("getnames").delete 'in case there's an existing sheet
on error goto 0
newwks.name = "GetNames"

#2. change this:
oRow = 0
to
oRow = 1 'cause you're gonna have headers in row 1
with newwks.range("a1").resize(1,howmanytables)
.formula = "=""Bracket""& column()"
.value = .value
end with

OdAwG wrote:

Hey D

I agree with your suggestion and I will do it the way you have
suggested.
However, one other quick question, ok acutally two questions

001. in your code Set NewWks = Worksheets.Add - can we name
this
to
a
unique name like GetNames
002. can we add column heading Table1, Table2 or Bracket1,
Bracket2,
etc...in that new sheet

Argus

"Dave Peterson" wrote in message
...
I think you could do that with the table that's created from
that
second
macro.

You could fill in the empty cells with "bye" so that any
bracket
with
less
than
8 entries would have some players that advance to the second
round.
(I'd
try to
make it so that no bye played a bye, though.)

And, to me, it makes more sense to fill them the way the macro
did--you'd
never
end up with one bracket with just 1 name.



OdAwG wrote:

Hey David,

Oh cool, thanks for the help.

I guess to make things all clear, what I am trying to do is
automate a
bracket sheet that we can use for bowling. People would sign
up
to
enter
into brackets. All they would have to do is give their names
and
the
number
of brackets they want. The 8 bracket sheet is used (because
we
only
bowl
3
games).

Game1 Game2 Game3

----------|
|--------|
----------| |
|------|
----------| | |
|--------| |
----------| |
|-----------
----------| |
|--------| |
----------| | |
|------|
----------| |
|--------|
----------|

In order to do a bracket sheet like above, we have to have
the
names
filled
in. I thought the best way was to create a master sheet with
only
the
bowlers name and number of brackets entered. Based on the
number
of
brackets entry, will determing the number of brackets to use.
That
is
what
we were trying to accomplish. I wanted to do this
automatically
with
a
macro so that we wouldn't do it manually week to week.

Once the tables or brackets were completed, I already have a
macro
that
would copy those names over to the bracket sheets and then
randomize
them
into the bracket itself.

001. take table1 and copy to a sheet called bracket1 (do the
same
with
each
table)
002. Once on the bracket sheet, I would randomize the name
and
place
them
in the brackets

Does that make it more clearer? I know what I want to say
but
typing
it
out
is has been a challenge. hehe ahhah LOL :)

Argus

"Dave Peterson" wrote in message
...
You can determine the maximum number of tables without
sorting.
Just
like
using: =max(b1:b99) in a worksheet.

And I see no compelling reason to fill each table with 8
names
first.
So
I
wouldn't bother. In fact, I would think that populating
each
table
so
that
they're approximately the same size (give or take one)
would
be
better
than
having 4 tables with 8 and one table with 1.

But I really don't understand what you're doing.

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim MaxEntries As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries =
Application.Sum(.Range(.Cells(FirstRow,
"B"),
_

.Cells(LastRow,
"B")))
HowManyTables = (TotalEntries + HowManyPerTable - 1)
\
HowManyPerTable

MaxEntries = Application.Max(.Range(.Cells(FirstRow,
"B"),
_

.Cells(LastRow,
"B")))

If HowManyTables < MaxEntries Then
HowManyTables = MaxEntries
End If

If TotalEntries HowManyPerTable * HowManyTables
Then
MsgBox "Something has to give--not enough room
in
the
table"
Exit Sub
End If

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow, "B").Value

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value = ThisPerson
Next hCtr
Next iRow
End With
End Sub



OdAwG wrote:

Hey Dave,

Sorry for not being clear.

Let me try again:
001. The total number of table columns created depends on
the
total
number
of entries.
002. To calculate the total number of tables columns, add
up
all
entries
and divide by 8
003. Each table can only have a max of 8 names per table.
004. No duplications of names in the table columns. So,
if
Oscar
has
5,
then his name should
should appear in five (5) separate tables.

I like the way you've done it in the your code below. I
guess
what
needs
to
happen first then is to sort the entry column first
decended
to
get
the
highest numeric value up top first to create the total
number
of
table
columns to create.

But, i think the tables should be filled first with eight
(8)
names
before
moving to the next table. What do you think?

Argus

"Dave Peterson" wrote in
message
...
I don't think you explained how you got that second
layout--well,
not
enough for
me to understand.

I don't see why Chucky Cheese couldn't be in the 5th
table--right
next
to
the
4th Adam Doe entry. Then Chucky Cheese could be
continued
on
the
next
line in
tables 1-3.

Then David Crocket would pick up in the next column
(table
4,
table
5),
then the
next row tables 1 and 2.

And so forth.

If this kind of layout is ok:

Oscar Delahoya Oscar Delahoya Oscar Delahoya Oscar
Delahoya
Oscar
Delahoya
Quincy Jones Quincy Jones Quincy Jones Quincy
Jones
Quincy
Jones
Xiasha Jones Xiasha Jones Xiasha Jones Xiasha
Jones
Xiasha
Jones
Adam Doe Adam Doe Adam Doe Adam Doe
chucky
cheese
chucky cheese chucky cheese chucky cheese David
Crocket
David
Crocket
David Crocket David Crocket Frank Valins Frank
Valins
Frank
Valins
Frank Valins Robert Ronins Robert Ronins Robert
Ronins
Robert
Ronins
Kent Clark

Just filling them up across the tables, then going to
the
next
row,
then
you
could use a macro like:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oRow As Long
Dim oCol As Long

Dim HowManyPerTable As Long
Dim TotalEntries As Long
Dim HowManyTables As Long
Dim ThisPerson As String
Dim HowManyForThisPerson As Long
Dim hCtr As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

HowManyPerTable = 8

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TotalEntries =
Application.Sum(.Range(.Cells(FirstRow,
"B"),
_

.Cells(LastRow,
"B")))
HowManyTables = (TotalEntries + HowManyPerTable -
1)
\
HowManyPerTable

oRow = 0
oCol = 999999
For iRow = FirstRow To LastRow
ThisPerson = .Cells(iRow, "A").Value
HowManyForThisPerson = .Cells(iRow,
"B").Value
If HowManyForThisPerson HowManyTables Then
MsgBox "not enough tables for: " &
ThisPerson _
& "Stopping!"
Exit Sub
End If

For hCtr = 1 To HowManyForThisPerson
If oCol = HowManyTables Then
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
NewWks.Cells(oRow, oCol).Value =
ThisPerson
Next hCtr
Next iRow
End With
End Sub

And I don't understand what would happen if Oscar had 6
entries????

Maybe the maximum number of tables should be the based
on
the
max
number
at the
table and the number of entries--along with the maximum
number
of
entries
any
one person has???

So if Oscar had 7 entries, but the calculation showed 5
tables
were
necessary,
then you'd end up going with 7 tables.




OdAwG wrote:

Hello All,

My question is, how can I do this automatically with a
macro
or
with a
high
speed low drag formula. I took this over from someone
who
was
doing
it
manually on paper and I converted it into a
spreadsheet.
It's
only
been
three months into the new year and I find this very
tedious
and
I
now
know
why that person no longer wanted to do this.

Given the following names listed below with the number
of
entries,
I
need
to
figure out how many table columns needs to be created
and
put
each
name
in
each table column only once by their corresponding
entries.
In
the
example
below, we have a total of 36 entries (adding up all the
entries).
To
get
the total numbers of tables to create, I divided 36 by
8
(8
names
per
table)
and that gives me a total of 5 table columns to create.

Oscar Delahoya 5
Quincy Jones 5
Xiasha Jones 5
Adam Doe 4
chucky cheese 4
David Crocket 4
Frank Valins 4
Robert Ronins 4
Kent Clark 1
------------------
36/8 = 4.5 or 5 tables

Table1 Table2 Table3 Table4 Table5
------------------------------------------------
Oscar Oscar Oscar Oscar Oscar
Quincy Quincy Quincy Quincy Quincy
Xiasha Xiasha Xiasha Xiasha Xiasha
Adam Adam Adam Adam Kent
Chucky Chucky Chucky Chucky
David David David David
Frank Frank Frank Frank
Robert Robert Robert Robert

Any and all help in this matter is GREATLY APPRECIATED
:)

Argus

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson