I have a worksheet ("Sheet 1") containing 10.000 rows of text,
covering columns A to V.
In another worksheet ("Sheet 2") of the same workbook, I've created a table
which contains statistics regarding those 10.000 rows.
The amount of statistics included in this table is too large to mention here.
There are 2 pieces of statistics however, which I've been unable to include
in the table:

A) Statistic (Formula) 1 needed:
a. Columns F & H of Sheet 1, contain names which can appear either
on column
F or H, but never simultaneously on the same row.
Ex: Row 20, may contain "Larry" on column F & "John" on column H.
There will never be a row however, containing, say "Larry" on
both: columns
F & H, simultaneously.
b. I need to be able to figure out whenever, say "Larry" appears on
column F,
what happens to column I. (Column I contains other text,
relating to "Larry"
"John"...)
c. Ex: Say that "Larry" appears on ROW 20 of column F, and that on
the same
row, column I reads: "X".
The formula would ideally tell me that:
(Ex) Throughout 10.000 rows, "Larry" appeared 200 times.
Additionally, when
he appeared, column I, read:

[The following message isn't a reply, but rather a correction of a previous
question
posted under an almost identical Subject name, ommitting the: "(Part 1)". ]


I have a worksheet ("Sheet 1") containing 10.000 rows of text,
covering columns A to V.
In another worksheet ("Sheet 2") of the same workbook, I've created a table
which contains statistics regarding those 10.000 rows.
The amount of statistics included in this table is too large to mention here.
There are 2 pieces of statistics however, which I've been unable to include
in the table:

A) Statistic (Formula) 1 needed:
a. Columns F & H of Sheet 1, contain names which can appear either
on column F or H, but never simultaneously on the same row.
Ex: Row 20, may contain "Larry" on column F & "John" on column
H.
There will never be a row however, containing, say "Larry" on
both: columns F & H, simultaneously.
b. Column I will always contain one of either of the following words:
"IN" or "OUT" or "GO".
c. I need to be able to figure out whenever, say "Larry" appears on
column F,
what happens to column I.
d. Ex: Say that "Larry" appears on ROW 20 of column F, and that on
the same row, column I reads: "IN".

The needed formula would ideally tell me that (Ex):

I) Throughout 10.000 rows, "Larry" appeared 200 times.
&
II) Of those appearances, column I, read:
i) "IN": (Ex) 60 times
ii) "OUT": (Ex) 40 times
iii) "GO": (Ex) 100 times

There are a lot of ways to do this but this is the easiest I could think of:

Sub main()
Dim myString As String
Dim lastCell As Long
Dim myCounter As Long
Dim findName As String
lastCell = Cells(Rows.Count, "F").End(xlUp).Row
myCounter = 0

findName = "larry"


For i = 2 To lastCell
If Cells(i, "F") = "larry" Then
If myString = "" Then myString = Cells(i, "I") Else myString = myString &
vbCrLf & Cells(i, "I")
myCounter = myCounter + 1
End If
Next

lastCell = Cells(Rows.Count, "H").End(xlUp).Row

For i = 2 To lastCell
If Cells(i, "H") = "larry" Then
If myString = "" Then myString = Cells(i, "I") Else myString = myString &
vbCrLf & Cells(i, "I")
myCounter = myCounter + 1
End If
Next
MsgBox "Throughout " & lastCell & " rows, " & findName & " was found " & _
myCounter & " times. Additionally, when he appeared column I read: " &
vbCrLf & myString
End Sub

--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"Vasilis Tergen" wrote:

I have a worksheet ("Sheet 1") containing 10.000 rows of text,
covering columns A to V.
In another worksheet ("Sheet 2") of the same workbook, I've created a table
which contains statistics regarding those 10.000 rows.
The amount of statistics included in this table is too large to mention here.
There are 2 pieces of statistics however, which I've been unable to include
in the table:

A) Statistic (Formula) 1 needed:
a. Columns F & H of Sheet 1, contain names which can appear either
on column
F or H, but never simultaneously on the same row.
Ex: Row 20, may contain "Larry" on column F & "John" on column H.
There will never be a row however, containing, say "Larry" on
both: columns
F & H, simultaneously.
b. I need to be able to figure out whenever, say "Larry" appears on
column F,
what happens to column I. (Column I contains other text,
relating to "Larry"
"John"...)
c. Ex: Say that "Larry" appears on ROW 20 of column F, and that on
the same
row, column I reads: "X".
The formula would ideally tell me that:
(Ex) Throughout 10.000 rows, "Larry" appeared 200 times.
Additionally, when
he appeared, column I, read:

Sir,

Thank you for the reply, it is greatly appreciated.
Unfortunately, as noted in the original message- question, macros cannot be
used to perform the calculations. The reason for this is that the names
needing statistical processing ("Larry", "John"...) exceed 800 in total.
Thus, I couldn't contain 800 macros for each name in a worksheet, nor spend
the
much increased time needed to execute the macros.
Instead, what is needed is a formula(s), to be inputed in the target table
of statistics, so that its' result(s) appear on that statistical table.
Lastly, this original message is written wrongly.
The correct version is found under this category as well, named similarly,
lest for
the (Part 1) & or (Part 2) following the description of the message.

Thank you.

"John Bundy" wrote:

There are a lot of ways to do this but this is the easiest I could think of:

Sub main()
Dim myString As String
Dim lastCell As Long
Dim myCounter As Long
Dim findName As String
lastCell = Cells(Rows.Count, "F").End(xlUp).Row
myCounter = 0

findName = "larry"


For i = 2 To lastCell
If Cells(i, "F") = "larry" Then
If myString = "" Then myString = Cells(i, "I") Else myString = myString &
vbCrLf & Cells(i, "I")
myCounter = myCounter + 1
End If
Next

lastCell = Cells(Rows.Count, "H").End(xlUp).Row

For i = 2 To lastCell
If Cells(i, "H") = "larry" Then
If myString = "" Then myString = Cells(i, "I") Else myString = myString &
vbCrLf & Cells(i, "I")
myCounter = myCounter + 1
End If
Next
MsgBox "Throughout " & lastCell & " rows, " & findName & " was found " & _
myCounter & " times. Additionally, when he appeared column I read: " &
vbCrLf & myString
End Sub

--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"Vasilis Tergen" wrote:

I have a worksheet ("Sheet 1") containing 10.000 rows of text,
covering columns A to V.
In another worksheet ("Sheet 2") of the same workbook, I've created a table
which contains statistics regarding those 10.000 rows.
The amount of statistics included in this table is too large to mention here.
There are 2 pieces of statistics however, which I've been unable to include
in the table:

A) Statistic (Formula) 1 needed:
a. Columns F & H of Sheet 1, contain names which can appear either
on column
F or H, but never simultaneously on the same row.
Ex: Row 20, may contain "Larry" on column F & "John" on column H.
There will never be a row however, containing, say "Larry" on
both: columns
F & H, simultaneously.
b. I need to be able to figure out whenever, say "Larry" appears on
column F,
what happens to column I. (Column I contains other text,
relating to "Larry"
"John"...)
c. Ex: Say that "Larry" appears on ROW 20 of column F, and that on
the same
row, column I reads: "X".
The formula would ideally tell me that:
(Ex) Throughout 10.000 rows, "Larry" appeared 200 times.
Additionally, when
he appeared, column I, read:

I found the answer to my question:
(For anyone else that may find this information useful)


(To find how many times both "Larry" and "IN" appeared on the same row: )

=SUMPRODUCT(('Sheet 1'!$F$1:$H$50000="Larry")*('Sheet 1'!$S$1:$S$50000="IN")





"Vasilis Tergen" wrote:

Sir,

Thank you for the reply, it is greatly appreciated.
Unfortunately, as noted in the original message- question, macros cannot be
used to perform the calculations. The reason for this is that the names
needing statistical processing ("Larry", "John"...) exceed 800 in total.
Thus, I couldn't contain 800 macros for each name in a worksheet, nor spend
the
much increased time needed to execute the macros.
Instead, what is needed is a formula(s), to be inputed in the target table
of statistics, so that its' result(s) appear on that statistical table.
Lastly, this original message is written wrongly.
The correct version is found under this category as well, named similarly,
lest for
the (Part 1) & or (Part 2) following the description of the message.

Thank you.

"John Bundy" wrote:

There are a lot of ways to do this but this is the easiest I could think of:

Sub main()
Dim myString As String
Dim lastCell As Long
Dim myCounter As Long
Dim findName As String
lastCell = Cells(Rows.Count, "F").End(xlUp).Row
myCounter = 0

findName = "larry"


For i = 2 To lastCell
If Cells(i, "F") = "larry" Then
If myString = "" Then myString = Cells(i, "I") Else myString = myString &
vbCrLf & Cells(i, "I")
myCounter = myCounter + 1
End If
Next

lastCell = Cells(Rows.Count, "H").End(xlUp).Row

For i = 2 To lastCell
If Cells(i, "H") = "larry" Then
If myString = "" Then myString = Cells(i, "I") Else myString = myString &
vbCrLf & Cells(i, "I")
myCounter = myCounter + 1
End If
Next
MsgBox "Throughout " & lastCell & " rows, " & findName & " was found " & _
myCounter & " times. Additionally, when he appeared column I read: " &
vbCrLf & myString
End Sub

--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"Vasilis Tergen" wrote:

I have a worksheet ("Sheet 1") containing 10.000 rows of text,
covering columns A to V.
In another worksheet ("Sheet 2") of the same workbook, I've created a table
which contains statistics regarding those 10.000 rows.
The amount of statistics included in this table is too large to mention here.
There are 2 pieces of statistics however, which I've been unable to include
in the table:

A) Statistic (Formula) 1 needed:
a. Columns F & H of Sheet 1, contain names which can appear either
on column
F or H, but never simultaneously on the same row.
Ex: Row 20, may contain "Larry" on column F & "John" on column H.
There will never be a row however, containing, say "Larry" on
both: columns
F & H, simultaneously.
b. I need to be able to figure out whenever, say "Larry" appears on
column F,
what happens to column I. (Column I contains other text,
relating to "Larry"
"John"...)
c. Ex: Say that "Larry" appears on ROW 20 of column F, and that on
the same
row, column I reads: "X".
The formula would ideally tell me that:
(Ex) Throughout 10.000 rows, "Larry" appeared 200 times.
Additionally, when
he appeared, column I, read: