Looking for a formula

[cmequi]

Hello everyone.

I'm looking for a formula that will help me with the following.

Sheet 1 has a list of names in column D. Column C has statuses. The statuses could either be Data Entry, Signatures or Submitted. Some names in column D are repeated and the status for the repeated names could be different from one another.

Sheet 2 has a list of all the names in column A, but the names only show up once. Column B shows how many times the name was repeated on sheet 1. Column C shows what kind of status the person is in on sheet one between pending, submitted or software.

Example: Bob has his name on sheet 1 and his status is Data Entry or Signatures. In sheet 2, his status would need to show up as Pending.

Joe has his name on sheet 1 and his status is submitted. In sheet 2, his status would need to show up as Submitted.

Mike has his name on sheet 1 twice and his statuses are Data Entry and Submitted. In sheet 2, his status would need to show up as Pending/Submitted.

What formula can I use to show that?

[#2]

@cmequi 

Might I offer the observation that you need more than a formula. Before a functional formula could be developed, you'd need to come up with a clear and consistent set of ground rules. Your current description has some inconsistencies, or at the very least points of confusion.

First paragraph describes the available status labels as: "Data Entry, Signatures or Submitted"

Second paragraph uses: "pending, submitted or software"

Third offers several:  

• "if (in Sheet 1) Data Entry or Signatures. In sheet 2, his status would need to show up as Pending."
• "his status (in Sheet 1) is submitted. In sheet 2, his status would need to show up as Submitted."
• "If (shows up in Sheet 1 twice) and his statuses are Data Entry and Submitted. In sheet 2, his status would need to show up as Pending/Submitted."

1. But "Pending/Submitted" wasn't one of the statuses available, and paragraph 2 made no reference to combinations of status indicators. The normal presumption coming into this is that the most recent would dictate--but is there a date for the entries in Sheet 1?
2. And what determines when "Software" should be the selection?