Is there a formula I can use to match a file name in one column with any
matches in other columns and give a total count for each column?

Example:
A B C D E
F
05l.jpg 106.jpg 543.gif 106.jpg 37l.jpg
106.jpg= 0 in column A, 1 in column B, 1 in column C, 2 in column D etc etc€¦

12l.jpg 246.jpg 106.jpg 112.jpg 002.bmp 107.jpg= 0
in column A, 0 in column B, 1 in column C, 0 in column D etc etc€¦

37l.jpg 1141.jpg 413.gif 37l.jpg 1141.jpg
109.jpg= 0 in column A, 0 in column B, 1 in column C, 0 in column D etc etc€¦

69l.jpg 351.jpg 107.gif 002.bmp 084.bmp
112.jpg= etc. etc€¦

35l.jpg 112.jpg 109.jpg 106.jpg 124.bmp
1141.jpg= etc. etc...

Any suggestions, ideas would be greatly appreciated. Thank you.

Here's one guess at it ..

Assume the data below is in Sheet1,
in cols A to F, data from row1 down

05l.jpg 106.jpg 543.gif 106.jpg 37l.jpg 106.jpg
12l.jpg 246.jpg 106.jpg 112.jpg 002.bmp 107.jpg
37l.jpg 1141.jpg 413.gif 37l.jpg 1141.jpg 109.jpg
etc

In Sheet2
------
Put in A1: =COUNTIF(Sheet1!$A1:$F1,Sheet1!A1)-1

Copy A1 across to F1, fill down to cover as many rows
as there is data in Sheet1

Sheet2 returns the count of matches that each data item in Sheet1 has with
the other items on the same row. A zero return means the data item in Sheet1
is unique within that row. For the sample data, the results would be:

0 2 0 2 0 2
0 0 0 0 0 0
1 1 0 1 1 0

Adapt to suit if the above is close to what you're after ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"David" wrote in message
...
Is there a formula I can use to match a file name in one column with any
matches in other columns and give a total count for each column?

Example:
A B C D E
F
05l.jpg 106.jpg 543.gif 106.jpg 37l.jpg
106.jpg= 0 in column A, 1 in column B, 1 in column C, 2 in column D etc
etc.

12l.jpg 246.jpg 106.jpg 112.jpg 002.bmp 107.jpg=
0
in column A, 0 in column B, 1 in column C, 0 in column D etc etc.

37l.jpg 1141.jpg 413.gif 37l.jpg 1141.jpg
109.jpg= 0 in column A, 0 in column B, 1 in column C, 0 in column D etc
etc.

69l.jpg 351.jpg 107.gif 002.bmp 084.bmp
112.jpg= etc. etc.

35l.jpg 112.jpg 109.jpg 106.jpg 124.bmp
1141.jpg= etc. etc...

Any suggestions, ideas would be greatly appreciated. Thank you.

Max,

Thank you, but I was not clear on what I needed to do with the data.

I have a sheet with columns A, B, C, D, and E. Each column is a list of
files from a different storage location. Example, column A would be file
server A, column B would be file server B, column C is file server C, etc. I
have up to 2520 rows filled in with file names.

I have a list of particular files I need to locate and count the total.
Example, €œMy list has file name 106.jpg on it. I need to search/find if it
is located in column A, B, C, D, E or all of them or two of them. Then count
the total found on/in column A, B, C, D, E, etc. etc€¦ such as: file 106.jpg
was found in column A once, found in column B two times, found in column C
zero times, etc€¦ Then file 107.bmp was found in column A zero times, found in
column B one time, etc... €�

I hope this is a clearer description of what I am trying to do. Thank you.


"Max" wrote:

Here's one guess at it ..

Assume the data below is in Sheet1,
in cols A to F, data from row1 down

05l.jpg 106.jpg 543.gif 106.jpg 37l.jpg 106.jpg
12l.jpg 246.jpg 106.jpg 112.jpg 002.bmp 107.jpg
37l.jpg 1141.jpg 413.gif 37l.jpg 1141.jpg 109.jpg
etc

In Sheet2
------
Put in A1: =COUNTIF(Sheet1!$A1:$F1,Sheet1!A1)-1

Copy A1 across to F1, fill down to cover as many rows
as there is data in Sheet1

Sheet2 returns the count of matches that each data item in Sheet1 has with
the other items on the same row. A zero return means the data item in Sheet1
is unique within that row. For the sample data, the results would be:

0 2 0 2 0 2
0 0 0 0 0 0
1 1 0 1 1 0

Adapt to suit if the above is close to what you're after ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"David" wrote in message
...
Is there a formula I can use to match a file name in one column with any
matches in other columns and give a total count for each column?

Example:
A B C D E
F
05l.jpg 106.jpg 543.gif 106.jpg 37l.jpg
106.jpg= 0 in column A, 1 in column B, 1 in column C, 2 in column D etc
etc.

12l.jpg 246.jpg 106.jpg 112.jpg 002.bmp 107.jpg=
0
in column A, 0 in column B, 1 in column C, 0 in column D etc etc.

37l.jpg 1141.jpg 413.gif 37l.jpg 1141.jpg
109.jpg= 0 in column A, 0 in column B, 1 in column C, 0 in column D etc
etc.

69l.jpg 351.jpg 107.gif 002.bmp 084.bmp
112.jpg= etc. etc.

35l.jpg 112.jpg 109.jpg 106.jpg 124.bmp
1141.jpg= etc. etc...

Any suggestions, ideas would be greatly appreciated. Thank you.

Assume your list of particular files to count is in column A of Sheet2 and
starts in A2. In B2 put in
=countif(Sheet1!A:A,$A2)
then drag fill it across to column F
this will give you the counts for each column.
in G2 put in the formula
=Countif(B2:F2,"0")

Now select B2:G2 and drag fill down.

Sums for Column A on sheet1 are found in Column B on sheet2, for Column B on
Sheet1 are found in Column C of sheet2 and so forth. Total columns
containing the file are in Column G of sheet2

--
Regards,
Tom Ogilvy

"David" wrote in message
...
Max,

Thank you, but I was not clear on what I needed to do with the data.

I have a sheet with columns A, B, C, D, and E. Each column is a list of
files from a different storage location. Example, column A would be file
server A, column B would be file server B, column C is file server C, etc.
I
have up to 2520 rows filled in with file names.

I have a list of particular files I need to locate and count the total.
Example, "My list has file name 106.jpg on it. I need to search/find if
it
is located in column A, B, C, D, E or all of them or two of them. Then
count
the total found on/in column A, B, C, D, E, etc. etc. such as: file
106.jpg
was found in column A once, found in column B two times, found in column C
zero times, etc. Then file 107.bmp was found in column A zero times, found
in
column B one time, etc... "

I hope this is a clearer description of what I am trying to do. Thank
you.


"Max" wrote:

Here's one guess at it ..

Assume the data below is in Sheet1,
in cols A to F, data from row1 down

05l.jpg 106.jpg 543.gif 106.jpg 37l.jpg 106.jpg
12l.jpg 246.jpg 106.jpg 112.jpg 002.bmp 107.jpg
37l.jpg 1141.jpg 413.gif 37l.jpg 1141.jpg 109.jpg
etc

In Sheet2
------
Put in A1: =COUNTIF(Sheet1!$A1:$F1,Sheet1!A1)-1

Copy A1 across to F1, fill down to cover as many rows
as there is data in Sheet1

Sheet2 returns the count of matches that each data item in Sheet1 has
with
the other items on the same row. A zero return means the data item in
Sheet1
is unique within that row. For the sample data, the results would be:

0 2 0 2 0 2
0 0 0 0 0 0
1 1 0 1 1 0

Adapt to suit if the above is close to what you're after ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"David" wrote in message
...
Is there a formula I can use to match a file name in one column with
any
matches in other columns and give a total count for each column?

Example:
A B C D
E
F
05l.jpg 106.jpg 543.gif 106.jpg 37l.jpg
106.jpg= 0 in column A, 1 in column B, 1 in column C, 2 in column D
etc
etc.

12l.jpg 246.jpg 106.jpg 112.jpg 002.bmp
107.jpg=
0
in column A, 0 in column B, 1 in column C, 0 in column D etc etc.

37l.jpg 1141.jpg 413.gif 37l.jpg 1141.jpg
109.jpg= 0 in column A, 0 in column B, 1 in column C, 0 in column D
etc
etc.

69l.jpg 351.jpg 107.gif 002.bmp 084.bmp
112.jpg= etc. etc.

35l.jpg 112.jpg 109.jpg 106.jpg 124.bmp
1141.jpg= etc. etc...

Any suggestions, ideas would be greatly appreciated. Thank you.

Thanks Tom,

It worked just fine. I I really appreciate the help. Thank you.


David

"Tom Ogilvy" wrote:

Assume your list of particular files to count is in column A of Sheet2 and
starts in A2. In B2 put in
=countif(Sheet1!A:A,$A2)
then drag fill it across to column F
this will give you the counts for each column.
in G2 put in the formula
=Countif(B2:F2,"0")

Now select B2:G2 and drag fill down.

Sums for Column A on sheet1 are found in Column B on sheet2, for Column B on
Sheet1 are found in Column C of sheet2 and so forth. Total columns
containing the file are in Column G of sheet2

--
Regards,
Tom Ogilvy

"David" wrote in message
...
Max,

Thank you, but I was not clear on what I needed to do with the data.

I have a sheet with columns A, B, C, D, and E. Each column is a list of
files from a different storage location. Example, column A would be file
server A, column B would be file server B, column C is file server C, etc.
I
have up to 2520 rows filled in with file names.

I have a list of particular files I need to locate and count the total.
Example, "My list has file name 106.jpg on it. I need to search/find if
it
is located in column A, B, C, D, E or all of them or two of them. Then
count
the total found on/in column A, B, C, D, E, etc. etc. such as: file
106.jpg
was found in column A once, found in column B two times, found in column C
zero times, etc. Then file 107.bmp was found in column A zero times, found
in
column B one time, etc... "

I hope this is a clearer description of what I am trying to do. Thank
you.


"Max" wrote:

Here's one guess at it ..

Assume the data below is in Sheet1,
in cols A to F, data from row1 down

05l.jpg 106.jpg 543.gif 106.jpg 37l.jpg 106.jpg
12l.jpg 246.jpg 106.jpg 112.jpg 002.bmp 107.jpg
37l.jpg 1141.jpg 413.gif 37l.jpg 1141.jpg 109.jpg
etc

In Sheet2
------
Put in A1: =COUNTIF(Sheet1!$A1:$F1,Sheet1!A1)-1

Copy A1 across to F1, fill down to cover as many rows
as there is data in Sheet1

Sheet2 returns the count of matches that each data item in Sheet1 has
with
the other items on the same row. A zero return means the data item in
Sheet1
is unique within that row. For the sample data, the results would be:

0 2 0 2 0 2
0 0 0 0 0 0
1 1 0 1 1 0

Adapt to suit if the above is close to what you're after ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"David" wrote in message
...
Is there a formula I can use to match a file name in one column with
any
matches in other columns and give a total count for each column?

Example:
A B C D
E
F
05l.jpg 106.jpg 543.gif 106.jpg 37l.jpg
106.jpg= 0 in column A, 1 in column B, 1 in column C, 2 in column D
etc
etc.

12l.jpg 246.jpg 106.jpg 112.jpg 002.bmp
107.jpg=
0
in column A, 0 in column B, 1 in column C, 0 in column D etc etc.

37l.jpg 1141.jpg 413.gif 37l.jpg 1141.jpg
109.jpg= 0 in column A, 0 in column B, 1 in column C, 0 in column D
etc
etc.

69l.jpg 351.jpg 107.gif 002.bmp 084.bmp
112.jpg= etc. etc.

35l.jpg 112.jpg 109.jpg 106.jpg 124.bmp
1141.jpg= etc. etc...

Any suggestions, ideas would be greatly appreciated. Thank you.

Glad to hear you got the answer you wanted from Tom !
Once the discussion "snowball" starts rolling around here,
the solution is seldom far away <g ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----