Task counting

Hi all,

Everyday the department receives an email with the daily tasks. In the body of email (not any attached excels) there is a table with the following format:

---

John Doe Kitchen / Cleaning

---

Max the Driver Driving / Delivering / Reporting

---

Charles Mg Reception

---

Nick Pet Maintenance / Reporting

Some people may have one task, while others may have two or more separated each time by a "/".

When this table is copied to an excel (sheet2) it creates a table with two columns, the first being the names (A1 downwards) and the second being the tasks (B1 downwards). In sheet 1 there is a list with all the names on column A (A2 - A30) and all the tasks in row 1 (B1-P1). Length of both columns and rows might change as people might be hired/fired and tasks be removed / added. This will be known in advance though.

So we have a grid of names and tasks (sheet1). The scope here is to create a monthly report of tasks that is adding the task table in sheet 2 and run a macro perhaps to auto count the tasks for each person and add a count (1,2 etc) to sheet1. Since tasks are daily and the report will be monthly, tasks will have to be added (summed up) in the grid from the second day onwards.


Any help would be greatly appreciated.

Thank you in advance

Two things.

1. A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

2. Please confrm the Excel version you are using (you joined 7 years ago and are showing Excel 2007... is that still the case?).

Thanks. In your sample, the names are in the same order in BOTH sheets. Is that the case in your real data?

If they are, use this:

=IFERROR(SUM(IF(ISNUMBER(SEARCH(B$1,Sheet2!$B1)),1,"")),"")

If they are in a different order, use this:

=IFERROR(SUM(IF(ISNUMBER(SEARCH(B$1,VLOOKUP($A2,Sheet2!$A:$B,2,FALSE))),1,"")),"")

i forgot to say.... maintenance needs to be spelled the same way in both sheets...

Post an updated sheet, showing what you mean.

OK. This is OK in my version of Excel.... However, I have lost a little confidence in Excel's compatability checker in recent days. Check it carefully in your version.

=IFERROR(1/(1/SUM(IF(ISNUMBER(SEARCH(B$1,INDEX(Sheet2!$B:$B,AGGREGATE(15,6,ROW(Sheet2!$A$1:$A$50)/(Sheet2!$A$1:$A$50=Sheet1!$A2),ROW($1:$100))))),1,""))),"")


array entered in B2, then copeid across and down. The formulae in the sheet are enclosed within a pair of { }, and are array formulae.

These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Glad to help. thanks for the feedback.