Looking for a formula

Occasional Visitor

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?

 

1 Reply

@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?