Question

Excel Question: I'm assigning user names like PattersonC but want to look out for potential conflicts like Charlie Patterson and Caroline Patterson. I want each logical step to be a new column. Clmn C finds duplicate last names; Clmn D marks when a new group of last names begins. Can you read my pseudo code for Clmn E and help me figure out how to check for first-name conflicts within a last-name grouping? Here's the doc:

https://docs.google.com/open?id=0B8qtChBDdeKvZWU4ZGQ3NmQtMDZjOS00ZmYwLTlmNGYtMWYxMDg3NzkyNjll Excel Question: I'm assigning user names like PattersonC but want to look out for potential conflicts like Charlie Patterson and Caroline Patterson. I want each logical step to be a new column. Clmn C finds duplicate last names; Clmn D marks when a new group of last names begins. Can you read my pseudo code for Clmn E and help me figure out how to check for first-name conflicts within a last-name grouping? Here's the doc:

https://docs.google.com/open?id=0B8qtChBDdeKvZWU4ZGQ3NmQtMDZjOS00ZmYwLTlmNGYtMWYxMDg3NzkyNjll @Computer Science

Answer 1

First, for column D, it seems like you'd need to search everyone's first initial when column C is greater than 1. Thus, I don't think column D really gets you anything. In fact, using column D instead of column C seems like the wrong thing to do.

Your "pseudocode" kinda' reveals this. You say "search within the appropriate range". There's no command to get an "appropriate range". You have to explicitly explain exactly which range, even in pseudocode.

In fact, column C might even be misleading. Look at your patterson/posner groups.

I'd just do it all in one step. If this row's last name is the same as the previous row's last name, then check first initials. If they're the same, there's a problem. Here's the OOo Calc formula:
=IF(B3=B2; IF(LEFT(A3;1)=LEFT(A2;1);"problem";"");"")

Answer 2

Pointing out the LEFT command helps. Looks like Excel and OOo have that in common. Having two IF statements in one line looks useful, too. Do Excel and OOo use the same syntax for that? Is that called nesting the commands, or is nesting something different?

Yeah--simplifying ending up being the way to go. I decided to create the potential usernames right off the bat by using CONCATENATE. After that, I only had to check for duplicates in one column.

Thanks for helping!

Answer 3

There ARE differences between Excel and Calc, but I don't know enough to say what they all are. I think Excel uses commas in formulas, and Calc uses semicolons.

I don't know what exactly you're referring to when you say "nesting" but my IF statements are nested. I didn't look to hard for a way to combine IF statements with some kind of AND operator, so I just nested them. I'm not an Excel formula expert, but I knew A way to do it, and that was good enough.

Of course. Your method turns it into one simple comparison.