Counting number of times a name appears when there are several in each column

[peggrif]

Hi,

I'd really appreciate any help with this.

I have a spreadsheet which contains information about files. It has the file name and then a set of usernames that have access to it, along with a lot of other information. So it looks similar like this:

File	Path	Members	Size	Accessed
File1	Pathforfile1	Jude;Sam;Lucy	105gb	01/01/22
File2	Pathforfile2	Jude;Lucy;Steve;Helen	96gb	01/05/22

And continues for several thousand rows. The 'members' column isn't always the same number of people per column because some files are only accessed by one or two people and some are accessed by hundreds.

Essentially I want to find out which of the members have access to the largest number of files. Ideally I'd end up with a table saying:

Member	Number of files
Jude	1050
Lucy	1003
Steve	978

and so on for each person.

What would be even better would be to have something like this:

Member	Number of files over 10000gb the person has access to	Number of files over 7000gb the person has access to
Jude	12	100
Lucy	40	102
Steve	17	64

and so on - is that something that could realistically be done?

Thanks in advance for any help.

 

[offthelip]

If this is a one off problem the easy way to get the count is to cut and paste the Members column into a new workbook and then use Text to columns to split it into separate columns, then a simple countif will give you the counts. If you need to do this more than once then this is the sort of process that could be automated.
To get the file sizes you will need to run through all the files which can be done using the filesystem object and looking at filename.size attribute. So it is possible to do this with VBA

 

[peggrif]

Thank you for your reply.

It's a one off problem in that I only need to do this to one worksheet, but there are several thousand people on the worksheet.

I've copied that column into a new workbook, used text to columns and made it a table called 'Table1', which looks like this:

Member 1	Member 2	Member 3	Member 4	Member 5	Member 6
Lucy	Steve	Paul
Jude	Caroline	Helen	Sam	Steve

but has several hundred columns and several thousand rows.


I can make a sheet that looks like this:

Name	Files
Lucy	=countif(table1, A2)
Steve	=countif(table1, A3)

By copying each name individually from one of the cells in the members column, then typing the formula in the right hand column.

Is there an easy way to make Excel give every name that appears in Table1 its own row in one column (so that it creates the 'Name' column above but one for all of the different names that appear), so that I can then copy down the countif formula and it will calculate each name?

 

[offthelip]

Since you have office 365 you can use filter unique. I don't have it so I can't step you through the process but this page should help:
Filter for unique values or remove duplicate values.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F
1	Member 1	Member 2	Member 3	Member 4	Member 5	Member 6
2	Lucy	Steve	Paul
3	Jude	Caroline	Helen	Sam	Steve
4
5
6
7	Caroline
8	Helen
9	Jude
10	Lucy
11	Paul
12	Sam
13	Steve
14
Main

Cell Formulas
Range	Formula
A7:A13	A7	=LET(Rng,A2:F3,r,ROWS(Rng),s,SEQUENCE(r*COLUMNS(Rng),,0),i,INDEX(Rng,MOD(s,r)+1,INT(s/r)+1),SORT(UNIQUE(FILTER(i,i<>""))))
Dynamic array formulas.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Counting how many times names appear when there's 2+ per cell, and comparing other info
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.