Formula to Provide Info for Multiple Lines

[LadyDi]

Sorry for the long title, I wasn't sure what to name this thread. I have a spreadsheet that contains a list of orders, and information on them. Each order is part of a project (i.e. project number 325467 could contain order numbers 123, 456, and 789). There is also a column in the spreadsheet that lists the name of the person who worked on (or changed) each order. I would like a formula that will tell me if the same person worked on each of the orders for project 325467. Is that possible? i am attaching a sample spreadsheet to show you exactly what I am talking about. Any assistance you can provide would be greatly appreciated.

 

[techsupport1]

Hey LadyDi,

I'm working on a solution using macros, will that be an issue? Also I'm working off of the fact that the project numbers will be grouped, will this ever not be the case?

 

[LadyDi]

Macros will not be a problem.

The project numbers will always be grouped together.

Thank you very much for your assistance

 

[Kiwiman]

Howzit

THis should work. Put formula in cell H2

=IF(COUNTIF($B$2:$B$25,B2)=SUMPRODUCT(--($B$2:$B$25=B2),--($G$2:$G$25=G2)),1,2)

The formula broken down for informational purposes:

Count the number of orders relevant to the current row - Put in I2

=COUNTIF($B$2:$B$25,B2)

Using SUMPRODUCT determine how many times the same person has changed the the same project - Put in J2

=SUMPRODUCT(--($B$2:$B$25=B2),--($G$2:$G$25=G2))

The values in I2 and J2 should be the same if only the one person has changed a project - Put in K2

=IF(I2=J2,1,2)

 

[LadyDi]

That works perfectly. Thank you so much for your help.

 

[techsupport1]

Hey,

I realize you might have an answer already but I figured I'd send you what I did anyways. The macro below enters the information into column J. I've attached a modified file that will execute the macro when the button is pressed.

Sub Button1_Click()

Row = 2
Do
MultiPerson = False
BaseProjNum = Cells(Row, "B")
BasePerson = Cells(Row, "G")
If BaseProjNum = Empty Then GoTo Terminate
IncRow = Row
Do

RowProjNum = Cells(IncRow, "B")
If Not RowProjNum = BaseProjNum Then
    GoTo ProjComplete
End If
RowPerson = Cells(IncRow, "G")
If Not RowPerson = BasePerson Then
    'MsgBox "more than one person for row " & Row
    MultiPerson = True
End If
IncRow = IncRow + 1
Loop

MsgBox "row: " & Row
ProjComplete:
'MsgBox "Multiperson for " & Row & " is " & MultiPerson
If MultiPerson Then
    Num = Row
    Do While Num < IncRow
        Cells(Num, "J") = 2
        Num = Num + 1
    Loop
Else
    Num = Row
    Do While Num < IncRow
        Cells(Num, "J") = 1
        Num = Num + 1
    Loop
End If
Row = IncRow
Loop
Terminate:
MsgBox "Done!"
End Sub

Hope this helps

 

[LadyDi]

Thank you very much for your assistance. That works great too.