Array Formula to count specific text ignoring duplicates.

[JRidge]

Hello all,

I am trying to Automize 2 tables in my work book.

Copy of tables attached.

The first table consists of dates members taught specific modules.

Characteristics:

Column A = Date (5 rows are always used for 1 date).
Row 1 = Module name (Upto 30 Modules)
Members are added by drop down menu and can be duplicated in date and/or module.

Requirements:

When table is filled out the Formula would count how many days the specific member was attending. Duplicate enties for a member on the same day will be counted as 1.


Second table consists of Member, Overnight and Total days.

Requirements:

Column "member" is autofilled when a member is added to table 1.
Column "Overnight" is filled by drop down menu.
Column "Total Days" is autofilled in relation to what is chosen in Column "Overnight":
"Yes +1" = Total from Table 1 +1
"Yes" = Total from Table 1
"No" = 0

1. Is this at all possible?
2. What would be used, Formula, Array or Code.
3. Anyone have any ideas how i could accomplish this.

Thanks in advance for any help or ideas anyone can give.

[XOR LX]

Hi,

Can you just explain how you get your desired results of 0 for JüRi and SoBe?

The former appears against 29/ Aug/ and the latter against 1/ Sep/, so I would have thought their results should both be 1?

Regards

[XOR LX]

Apologies - I didn't fully read your subsequent paragraph:

Try this array formula in O2 and copy down (note: you have extra spacing in your "Yes" value in the drop-down menu for Overnight which will need to be removed in order for this to give correct results):

=IF(N2="No",0,SUMPRODUCT(--(MMULT((COLUMN($B$1:$J$1)^0),TRANSPOSE(--($B$2:$J$31=M2)))>0))+--(N2<>"Yes"))

Regards

[JRidge]

Hi XOR LX

Thanks for the quick response.

When i apply that to o" i get #NAME?

I am using a german version of Excel 2007 does that make a difference with the formula as excel is saying that the "," in the formula are an error and when i change them to ";" i then get the NAME error.

regards

Apologies - I didn't fully read your subsequent paragraph:

Try this array formula in O2 and copy down (note: you have extra spacing in your "Yes" value in the drop-down menu for Overnight which will need to be removed in order for this to give correct results):

=IF(N2="No",0,SUMPRODUCT(--(MMULT((COLUMN($B$1:$J$1)^0),TRANSPOSE(--($B$2:$J$31=M2)))>0))+--(N2<>"Yes"))

Regards

[arlu1201]

JRidge,

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[XOR LX]

And I trust you've translated all the English formula names into their German equivalents?

Regards

[JRidge]

Ha Ha..

Just done that and is now working with 1 error...

The "Yes +1" is adding 2 and not 1?

Regards

[JRidge]

Hi XOR LX,

I have found the problem but don't know the remedy:

When a member teaches the same module on the same day it counts 2 and not one, i can get around this by the member has only one entry under a module on a specific day.

Thanks very much for your time and help.

Regards

[XOR LX]

No. It is adding one.

Based on the information you gave, I disagree with your expected results for JeRi: there are seven matches for this name (rows 2, 7, 12, 14, 17, 22 and 27), so 7+1=8.

Regards

[XOR LX]

"When a member teaches the same module on the same day it counts 2 and not one, i can get around this by the member has only one entry under a module on a specific day."

Ah. This is an entirely different matter and something which you did not mention in your original post.

Do you wish me to look for a formula solution that takes this into account? Or are you happy to deal with it as you said?

Regards

[JRidge]

Hi XOR LX,

There are only 6 dates so there can only be a maximum of 7 (6+1).

Its because it doesn#t count the duplicate name in the same column on the same day as 1.

regards

[XOR LX]

Ok, so I see you've marked this as SOLVED now, so I presume you're happy to make some changes to your set-up now so that this is not an issue?

Glad I could help.

Cheers.

[JRidge]

If there is a formula solution, i would be greatful as other users will be filling this out so i would like to make it as simple as possible.

regards

[XOR LX]

Ok, I'll have another look.

[JRidge]

Hi XOR LX,

I have applied the Array to my main document and i do need to be able to add a member multiple times to the same module on the same day, this is because another formula i have written adds the time in Hours and minutes the member has taught during the day for costing purposes.

So if it is possible to not count duplicate entries under the same module on the same day i would be very greatful.

regards

[XOR LX]

Ok,

Had to try a new approach. Replace your formula in O2 with this (again, array) formula and copy down:

=IF(N2="No",0,SUM(--(FREQUENCY(SMALL(IF($B$2:$J$31=M2,ROW($B$2:$J$31)),ROW(INDIRECT("1:"&COUNTIF($B$2:$J$31,M2)))),5*(ROW(INDIRECT("1:"&ROWS($B$2:$J$31)/5+1))-1))>0))+(N2<>"Yes"))

Regards

[JRidge]

Good Morning XOR LX,

Thanks for all your time on this.

Translated:

=WENN(N2="No";0;SUMME(--(HÄUFIGKEIT(KKLEINSTE(WENN($B$2:$J$31=M2;ZEILE($B$2:$J$31));ZEILE(INDIREKT("1:"&ZÄHLENWENN($B$2:$J$31;M2))));5*(ZEILE(INDIREKT("1:"&ZEILEN($B$2:$J$31)/5+1))-1))>0))+(N2<>"Yes"))

Works perfectly.

Once again thank you for all your help.

Regards

Jeremy

[XOR LX]

You're welcome.

And thanks for the translation - to someone not too familiar with the German language, it adds an air of hyperreality to see it written like that!

Cheers

[JRidge]

For XOR LX

Bottom 2 tables on the sheet.

Regards

Jeremy

[XOR LX]

Can you have a look at the attached, test it thoroughly, and let me know if there are any issues?

JRidge 011013.xlsx

Thanks

[JRidge]

Hi XOR LX,

Thank you once again. Works properly once more

If i need to add more date spaces can i just add them to the bottom of the table and then just adjust the ref to the table?

Also is there a way to protect the cells with the formula in so noone can accidently type in it?
Password or something.

Regards

Jeremy

[XOR LX]

"If i need to add more date spaces can i just add them to the bottom of the table and then just adjust the ref to the table?"

Yes, providing they're in row multiples of five, which it seems they always will be in your case.

"Also is there a way to protect the cells with the formula in so noone can accidently type in it?
Password or something."

You can protect the sheet (Review/Protect Sheet) and make sure that these cells are locked (you can also hide the formulas if you want). By default, all cells are locked in a sheet, so I usually select the entire sheet, unlock them all (Format Cells, Protection) and then re-lock only those that I wish to be so.

Hope that helps.

Regards

[JRidge]

One more last question XOR LX,

I am not sure if this is achievable but..

The dates down the left hand side of the table and the "JA" and "JA+"...

Currently when you select JA you get total number of day the User was there. and when you Choose JA+ you get the days he was there and then +1 day travelling.
If the dates are split i.e 1,2,3 and 7,8,9 can it be done that you automatically get 2 travel days when you select JA+ as the user was there 2 times for 3 days each.

I don't know if in the formula you can say when date is not consecative days then add +1 for each Group of days.

Is this fessable or am I asking too much from Excel.

Regards

Jeremy

[XOR LX]

I'm sure that that's achievable. However, it's been a while since I've been involved in this thread and my head takes time to get itself back into the gist of things!

What would really help me is if you could re-attach with some examples of this new criterion and your desired results in each case, so that I can get a feel for it and test some solutions.

Regards

[JRidge]

Hope this makes sense....

[XOR LX]

Hi,

Please see the attached. Once again, I believe it is in need of some thorough testing by yourself to make sure the logic is correct.

JRidge 021013.xlsx

Regards

[JRidge]

Hi,

Please see the attached. Once again, I believe it is in need of some thorough testing by yourself to make sure the logic is correct.

Attachment 268636

Regards

Hi XOR LX,

Wow that is a bit of code....

I will test it and get back to you.

Thanks very much once again.

Regards

Jeremy

[JRidge]

Hi XOR LX

Have come across the following problem.

I have extended the table to use more dates and when i use more than 2 date groups the formula no longer works.

Please see attached for example:


Regards

Jeremy

[XOR LX]

Hi,

You missed just one range change in all that!! (Should be 142, of course).

=IF(E66="Nein",0,SUM(--(FREQUENCY(SMALL(IF($C$83:$AP$142=B66,ROW($C$83:$AP$142)),ROW(INDIRECT("1:"&COUNTIF($C$83:$AP$142,B66)))),4+MIN(ROW($C$83:$AP$142))+5*(ROW(INDIRECT("1:"&ROWS($C$83:$AP$142)/5))-1))>0))+(E66="JA+")*SUM(--(FREQUENCY(IF(ISNUMBER(MATCH(ROW(INDIRECT(MIN($B$83:$B$142)&":"&MAX($B$83:$B$142))),SUBTOTAL(9,OFFSET($B$83,LOOKUP(SMALL(IF($C$83:$AP$142=B66,ROW($B$83:$B$112)-MIN(ROW($B$83:$B$142))),ROW(INDIRECT("1:"&COUNTIF($C$83:$AP$142,B66)))),5*(ROW(INDIRECT("1:"&ROWS($C$83:$AP$142)/5))-1)),,,)),0)),ROW(INDIRECT(MIN($B$83:$B$142)&":"&MAX($B$83:$B$142)))),IF(NOT(ISNUMBER(MATCH(ROW(INDIRECT(MIN($B$83:$B$142)&":"&MAX($B$83:$B$142))),SUBTOTAL(9,OFFSET($B$83,LOOKUP(SMALL(IF($C$83:$AP$142=B66,ROW($B$83:$B$142)-MIN(ROW($B$83:$B$142))),ROW(INDIRECT("1:"&COUNTIF($C$83:$AP$142,B66)))),5*(ROW(INDIRECT("1:"&ROWS($C$83:$AP$142)/5))-1)),,,)),0))),ROW(INDIRECT(MIN($B$83:$B$142)&":"&MAX($B$83:$B$142)))))>0)))

Regards

[JRidge]

Hi XOR LX,

Perfect, thanks very much once again.

I hope i can lay this one to rest now

Regards

Jeremy

[XOR LX]

"I hope i can lay this one to rest now"

So do I! That formula will probably collapse with any more additions, anyway!

Regards