match and count words

[Guest]

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]

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 ..

 

[Guest]

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.

 

[Tom Ogilvy]

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

 

[Guest]

Thanks Tom,

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


David

 

[Max]

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> ..