Counts of Text Where You Don't Know the Text Values

Hi,

I have a question regarding counting cells where you don't know the values you need to count. If you look at the attached JPG, the data in the table A1:I7 is generated automatically. It shows projects that staff members are working on in the morning and afternoon in any given week.

ExcelCounts.jpg

My question is how can I get the unique project names to display in cells A11, A12, A13 etc? I don't know what the projects are before I get the data, so I need a formula that says get me the names of the unique projects in the range B3:I7 and then give me a count for each of these unique projects. The data I have shown in the JPG in cells A10:B16 is hard written to show what I am looking for.

Thanks in advance for any help.

Cheers,
Steve

Hi Jeff,

Thanks for the quick feedback, but my question really is how can I get the spreadsheet to determine the unique projects for me, and then give me the count. In a real world example there could be different projects in every slot, or the same project in every slot. Once I get the data dump which is in B3:I7 I want the xls to then tell me the names of all the unique projects and the number of times they are represented.

I have attached the working example spreadsheet - hope that helps?

ProjectExample.xlsx

Thanks,
Steve

Hi,

Attached is the before and after of what I am hoping to get. Hope that can shed some more light?

ProjectExample.xlsx

If a VBA script is the only (or best) way then I am open to that - although my VBA skills are rudimentary at best!

Thanks,
Steve

Hi Jeff,

Sorry I am obviously not explaining myself very well! Let me go from the top. I have a system at work where I can export weekly reports that show me staff member names and the projects they are working on Monday to Friday in the AM and PM. This is what you see if the XLS in cells A1:I7.

Once I have this output, I then want the spreadsheet to list all the unique projects that are in cells B3:I7, and then count how many times they are in cells B3:I7.

I think I am OK getting the counts, but the problem is how do I get the spreadsheet to list the unique projects in cell A11 down?

Hope that makes more sense?

Thanks,
Steve

Hi Jerry,

Thanks for the Pivot Table suggestion and video to show it, I have now added that and it works like a dream!

One further question is can the values in the pivot table auto update if I amend the values in B3:I7? The reason I ask is that i wll drop data into these cells quite regulalry so would then like a quick way to update the results in the pivot table, without having to create a pivot every time?

Cheers,
Steve

Hi Jerry, sorry ignore me, just found the Refresh function when I right click the data in the pivot table!
Cheers,
Steve

If you want a purely formula-driven approach, then this site:

http://www.get-digital-help.com/2009...rray-formulas/

shows how you can get a unique list from multiple columns of data.

Hope this helps.

Pete

Hi Pete, thanks for that. Will take a look at the forumula option as well. Appreciate your help.

Steve