VLOOKUP & COUNTIF Complex

[kingsolo]

Okay, I'm attaching an example of my data that I am asking a question about. I am breaking out data as follows:

I need the COUNTS of the breakdown that's listed on the example sheet, but the thing is, I have to break it down per section, Kilroy & Jetson. To complicate matters even more, the data alternates back & forth like you see for hundreds of rows, and each section is never the same. There may be 15 rows of data entered by Jetson the first time, but next time he may only enter 5 rows.

I hope I have explained it good enough to get some assistance.

[mrice]

Can you manually fill in some figures to give an indication what the correct answer should look like?

[kingsolo]

Okay, filled in the figures that I need it to return. These are the acutal hand counts from the data. I included 2 notes reference data that looks out of place. Anything in {xx} format is not counted. Anything with the (Inc) reference is not counted either.

Thanks for the assist.

[mrice]

This macro seems to give the right answers

Please Login or Register  to view this content.

[kingsolo]

That worked. Now, for the hard question. Can you explain to me what this is doing? If able, can you break down the macro, cause if something ever happens to the sheet or the format of the data changes, I'd like to be able to know how to fix it, or what is going wrong with it. Additionally, I didn't remark that the names, Kilroy & Jetson, will change each time the sheet changes. How will I go about pointing the macro to 2 seperate cells (one for Kilroy and one for Jetson) on a seperate sheet in the workbook to get the name to sort?

Thanks for the help!

[mrice]

The macro is effectively starting at the top of the sheet and working its way downwards with the current row number = N.

It keeps track of the last name that it came across (Current) and uses this to assign the lines that it comes across.

There are a lot of IF...then statements which check for various criteria. Cells(N,8) for instance refers to the cell in row N and column 8. Instr looks for the occurence of a substring in a cell.

Probably the best way of working out what is going on is to step through the macro line by line using F8 and having the windows arranged so that you can see both the line of code that is executing and the effect that its having on the sheet.

Regarding the second part of the question, will there always be two names or could this change to more than two. The current code will only work for two and would need to be made more general for more.

[kingsolo]

There will always be 2 names. Is a breakdown comparison of the data of each name. It would get the name from cell G1 & cell J1, which I didn't put in the Example for some reason... it's late in Korea! Additionally, is there a way of tallying the (Inc) just in the Left,Middle,Right data?

I guess I could have put a break down of what the data is...

Cell G1 is name 1 (Kilroy)
Cell J1 is name 2 (Jetson)

P is Progressed
R is Regressed

Left,Middle,Right is a tracking method of where the project is filed

(+5.5) is a project score
(Inc) is a project turned in but not fully complete
{0} is a project turned in complete that is worth bonus points

Don't know if this helps the matter.

[kingsolo]

Am I right in thinking I can replace the:

Please Login or Register  to view this content.

with the statement:

Please Login or Register  to view this content.

and it will take whatever name is in cell G1?

[mrice]

Even simpler

Please Login or Register  to view this content.