I have a table set up as follows:

Date Name Place
10/2/07 Name1 Place1
10/2/07 Name1 Place1
10/3/07 Name1 Place2
10/3/07 Name1 Place3
10/3/07 Name1 Place3
10/4/07 Name2 Place1
10/4/07 Name2 Place2

I need to determine how many unique Places are listed for each Name and
Date, and place that number in a new table in which each row corresponds to a
Name, and each column B:HH corresponds to a date, and at the intersection is
the number of unique Places listed on that date for that person.

Functions are not an option, there's just too many calculations and the
thing takes hours to perform them. The list above is just an example of the
full list, which we fully expect to reach 65536 rows at some point. There
are also more than 60 different people, and 365 days in a year--it's a lot of
functions.

Thanks in advance for any ideas (even just approaches I could try) as to how
to get this done.

Mike

I would consider using a Pivot Table.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Heliocracy" wrote in message
...
I have a table set up as follows:

Date Name Place
10/2/07 Name1 Place1
10/2/07 Name1 Place1
10/3/07 Name1 Place2
10/3/07 Name1 Place3
10/3/07 Name1 Place3
10/4/07 Name2 Place1
10/4/07 Name2 Place2

I need to determine how many unique Places are listed for each Name and
Date, and place that number in a new table in which each row corresponds
to a
Name, and each column B:HH corresponds to a date, and at the intersection
is
the number of unique Places listed on that date for that person.

Functions are not an option, there's just too many calculations and the
thing takes hours to perform them. The list above is just an example of
the
full list, which we fully expect to reach 65536 rows at some point. There
are also more than 60 different people, and 365 days in a year--it's a lot
of
functions.

Thanks in advance for any ideas (even just approaches I could try) as to
how
to get this done.

Mike

hi helio (you're not in westwood r u), i'm thinking pivot table as well,
here's link to pv intro
http://www.peltiertech.com/Excel/Pivots/pivottables.htm

anyways, click on one cell of your data, click on "data" on menu bar,
highlight PivotTable and PivotChart" and click. this will bring up menu 1 of
pivot table wizard. make sure first option of each question is selected
[Microsoft Office Excel list/database and Pivot Table], click on "next".

menu 2 asks for a range, and will default to your three columns of data
(make sure the titles: date, name, and place are included in range), click on
"next".

menu 3 defaults to creating pivot table on a new worksheet which is fine.
click on the "options" button. in the "format options" section, remove
checkmarks from "grand totals for columns" and "grand totals for rows", and
place a checkmark "for error values, show", and place a "1" in the box to
right, click on "OK".

click on "layout" button, drag "date" button to "column" area, drag "name"
and "place" buttons to "row" area (be sure "name" is on top of "place").
double right click on "name", and in "subtotals" section select "None"
option, click "OK".

drag another "place" button (drag buttons that line the right edge of layout
menu) to "data" area. double right click that button and in the "summerize
by" menu, choose "Average", click "OK".

to recap, "date" should be in column area, "name" and "place" in row area
with "name" above "place", and "average of place" in data area. click "OK",
menu 3 returns, click "finish".

result should be a pivot table with names in column A, places in column B,
and to the right a grid with dates across the top and number 1 if a meeting
took place on that date, at that place, with that person.

copy the data in pivot table (this can be tricky). click on the upper
right, or lower right corner of table and highlight entire table except top
row (row1), click on "edit" in menu bar, highlight "copy" and click.

on a blank sheet, select cell A1, click on "edit" on menu bar, highlight
"paste special", select "values" in "paste" section, click on "OK". you'll
have to reformat the numbers in the top row to convert from excel date code
back to recognizable dates.

in cell B2 write formula: "=if(A2="",B1,A2)", hit "enter". copy formula
down length of column B. select single cell in table, B2, for example, click
on "data" on menu bar, highlight "subtotals" and click. answer "yes", if you
get question asking if top row of data is labels. in "subtotals" menu box,
select "place" for "at each change in:" section, select "sum" in "use
function" section, remove checkmarks in "name" and "place", place checkmarks
in all dates in "add subtotal to:" sectionl, click "OK".

at top of left most column, just left of column which numbers rows in excel
worksheet, there will be three small buttons numbered 1, 2, 3. click on "2".
hope that's result you were looking for . .

I think this code is what you are looking for. Start with the data in sheet1
and an empty sheet2. The code will automatically put unique Dates in Row 1,
and Unique Names in column A.

Sub maketable()

Sh2Colcount = 1
Sh2Rowcount = 1
With Sheets("Sheet1")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = 1 To LastRow

Mydate = .Cells(RowCount, "A").Value
Name = .Cells(RowCount, "B").Value
Place = .Cells(RowCount, "C").Value

With Sheets("Sheet2")
Set Row1Range = _
.Range(.Cells(1, "A"), _
.Cells(1, Sh2Colcount))
Set c_col = Row1Range.Find(what:=Mydate, _
LookIn:=xlValues)
If c_col Is Nothing Then
Sh2Colcount = Sh2Colcount + 1
Set c_col = .Cells(1, Sh2Colcount)
c_col.Value = Mydate
End If
Set ColARange = _
.Range(.Cells(1, "A"), _
.Cells(Sh2Rowcount, "A"))
Set c_row = ColARange.Find(what:=Name, _
LookIn:=xlValues)
If c_row Is Nothing Then
Sh2Rowcount = Sh2Rowcount + 1
Set c_row = .Cells(Sh2Rowcount, "A")
c_row.Value = Name
End If

oldPlace = .Cells(c_row.Row, c_col.Column)
If InStr(oldPlace, Place) = 0 Then
If Len(oldPlace) = 0 Then
newPlace = Place
Else
newPlace = oldPlace & "," & Place
End If
.Cells(c_row.Row, c_col.Column) = newPlace
End If
End With
Next RowCount
End With
End Sub


"Heliocracy" wrote:

I have a table set up as follows:

Date Name Place
10/2/07 Name1 Place1
10/2/07 Name1 Place1
10/3/07 Name1 Place2
10/3/07 Name1 Place3
10/3/07 Name1 Place3
10/4/07 Name2 Place1
10/4/07 Name2 Place2

I need to determine how many unique Places are listed for each Name and
Date, and place that number in a new table in which each row corresponds to a
Name, and each column B:HH corresponds to a date, and at the intersection is
the number of unique Places listed on that date for that person.

Functions are not an option, there's just too many calculations and the
thing takes hours to perform them. The list above is just an example of the
full list, which we fully expect to reach 65536 rows at some point. There
are also more than 60 different people, and 365 days in a year--it's a lot of
functions.

Thanks in advance for any ideas (even just approaches I could try) as to how
to get this done.

Mike

Thanks, I'll try both ways today and let you know, but I appreciate your
efforts! If I do need to go with the PivotTable, I may need some help with
the GETPIVOTDATA and I'll need to find a way for the table to count unique
records only, which I wasn't able to get it to do before. Thanks again to
all for the help.

Mike

"Joel" wrote:

I think this code is what you are looking for. Start with the data in sheet1
and an empty sheet2. The code will automatically put unique Dates in Row 1,
and Unique Names in column A.

Sub maketable()

Sh2Colcount = 1
Sh2Rowcount = 1
With Sheets("Sheet1")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = 1 To LastRow

Mydate = .Cells(RowCount, "A").Value
Name = .Cells(RowCount, "B").Value
Place = .Cells(RowCount, "C").Value

With Sheets("Sheet2")
Set Row1Range = _
.Range(.Cells(1, "A"), _
.Cells(1, Sh2Colcount))
Set c_col = Row1Range.Find(what:=Mydate, _
LookIn:=xlValues)
If c_col Is Nothing Then
Sh2Colcount = Sh2Colcount + 1
Set c_col = .Cells(1, Sh2Colcount)
c_col.Value = Mydate
End If
Set ColARange = _
.Range(.Cells(1, "A"), _
.Cells(Sh2Rowcount, "A"))
Set c_row = ColARange.Find(what:=Name, _
LookIn:=xlValues)
If c_row Is Nothing Then
Sh2Rowcount = Sh2Rowcount + 1
Set c_row = .Cells(Sh2Rowcount, "A")
c_row.Value = Name
End If

oldPlace = .Cells(c_row.Row, c_col.Column)
If InStr(oldPlace, Place) = 0 Then
If Len(oldPlace) = 0 Then
newPlace = Place
Else
newPlace = oldPlace & "," & Place
End If
.Cells(c_row.Row, c_col.Column) = newPlace
End If
End With
Next RowCount
End With
End Sub


"Heliocracy" wrote:

I have a table set up as follows:

Date Name Place
10/2/07 Name1 Place1
10/2/07 Name1 Place1
10/3/07 Name1 Place2
10/3/07 Name1 Place3
10/3/07 Name1 Place3
10/4/07 Name2 Place1
10/4/07 Name2 Place2

I need to determine how many unique Places are listed for each Name and
Date, and place that number in a new table in which each row corresponds to a
Name, and each column B:HH corresponds to a date, and at the intersection is
the number of unique Places listed on that date for that person.

Functions are not an option, there's just too many calculations and the
thing takes hours to perform them. The list above is just an example of the
full list, which we fully expect to reach 65536 rows at some point. There
are also more than 60 different people, and 365 days in a year--it's a lot of
functions.

Thanks in advance for any ideas (even just approaches I could try) as to how
to get this done.

Mike

Thanks, I believe I can work with this. I changed the delimiter to a
semi-colon, now I just need to do some fancy text-to-column work...for which
I may need help as well lol! Any way to separate Places in each column into
their own columns, each with the same date in the first cell, and not
overwrite the columns to the right in the process?

Thanks!
Mike

"Joel" wrote:

I think this code is what you are looking for. Start with the data in sheet1
and an empty sheet2. The code will automatically put unique Dates in Row 1,
and Unique Names in column A.

Sub maketable()

Sh2Colcount = 1
Sh2Rowcount = 1
With Sheets("Sheet1")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = 1 To LastRow

Mydate = .Cells(RowCount, "A").Value
Name = .Cells(RowCount, "B").Value
Place = .Cells(RowCount, "C").Value

With Sheets("Sheet2")
Set Row1Range = _
.Range(.Cells(1, "A"), _
.Cells(1, Sh2Colcount))
Set c_col = Row1Range.Find(what:=Mydate, _
LookIn:=xlValues)
If c_col Is Nothing Then
Sh2Colcount = Sh2Colcount + 1
Set c_col = .Cells(1, Sh2Colcount)
c_col.Value = Mydate
End If
Set ColARange = _
.Range(.Cells(1, "A"), _
.Cells(Sh2Rowcount, "A"))
Set c_row = ColARange.Find(what:=Name, _
LookIn:=xlValues)
If c_row Is Nothing Then
Sh2Rowcount = Sh2Rowcount + 1
Set c_row = .Cells(Sh2Rowcount, "A")
c_row.Value = Name
End If

oldPlace = .Cells(c_row.Row, c_col.Column)
If InStr(oldPlace, Place) = 0 Then
If Len(oldPlace) = 0 Then
newPlace = Place
Else
newPlace = oldPlace & "," & Place
End If
.Cells(c_row.Row, c_col.Column) = newPlace
End If
End With
Next RowCount
End With
End Sub


"Heliocracy" wrote:

I have a table set up as follows:

Date Name Place
10/2/07 Name1 Place1
10/2/07 Name1 Place1
10/3/07 Name1 Place2
10/3/07 Name1 Place3
10/3/07 Name1 Place3
10/4/07 Name2 Place1
10/4/07 Name2 Place2

I need to determine how many unique Places are listed for each Name and
Date, and place that number in a new table in which each row corresponds to a
Name, and each column B:HH corresponds to a date, and at the intersection is
the number of unique Places listed on that date for that person.

Functions are not an option, there's just too many calculations and the
thing takes hours to perform them. The list above is just an example of the
full list, which we fully expect to reach 65536 rows at some point. There
are also more than 60 different people, and 365 days in a year--it's a lot of
functions.

Thanks in advance for any ideas (even just approaches I could try) as to how
to get this done.

Mike

hi mike (and joel), my apologies but i've been out of the office most of this
week. there is a work around, in the instructions i sent previously, for
getting unique values: asking for "average of place" returns an error but
there is an option to replace an error value with the value of one.

therefore, if a certain place is visited by a person two, three, or more,
times in one day the pivot table will return an error which, by specifying in
the options menu, will return the number 1. if there are no visits then the
space is left blank.

resulting pivot table has values you need to calc unique visits, you just
need the subtotals . .

you shouldn't use GETPIVOTDATA (one of more unwieldy formula) just get
values. click on the bottom right cell of pivot table, highlight all the way
left, and then go up to row with dates . .

hi joel, that's pretty nifty code(!), wouldn't it make sense to have
additional, or separate, sub that goes over grid and replaces places names
with a count of places. i can't think of formula that can do that easily.

hope you're well, that two combo boxes in single userform model is on hold
as the two userform method is working out fine. i do intend to finish that
though, will send it when it's finished.

hope you both have a great weekend!