Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
David
 
Posts: n/a
Default match and count words

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.

  #2   Report Post  
Max
 
Posts: n/a
Default

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.



  #3   Report Post  
David
 
Posts: n/a
Default

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.




  #4   Report Post  
Tom Ogilvy
 
Posts: n/a
Default

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.






  #5   Report Post  
David
 
Posts: n/a
Default

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.









  #6   Report Post  
Max
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
"COUNTU" function in Excel to count unique entries in a range WayneL Excel Worksheet Functions 20 September 19th 08 03:50 AM
Match Last Occurrence of two numbers and Count to Previous Occurence Sam via OfficeKB.com Excel Worksheet Functions 33 April 4th 05 02:17 PM
If two columns match then count one. How? Tried countif and sum . IMC Medrec Excel Worksheet Functions 1 February 14th 05 01:28 PM
SUMPRODUCT Formula to Count Row of data Below Matched Criteria Sam via OfficeKB.com Excel Worksheet Functions 8 February 3rd 05 02:37 AM
lookup, count Stan Altshuller Excel Worksheet Functions 1 January 7th 05 12:08 AM


All times are GMT +1. The time now is 11:34 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"