mercredi 27 mai 2015

IF duplicate cell value found in column then return value

I need to track a person in a data sheet to determine from which location to which location the person moved.

If a person appears more then one time in Column J that means the person has changed the location and the location value is in Column L. For this I have the following code:

=IF(J35=J36;IF(COUNTIF(J:J;J35)>1; "From "&L35 &" to "& IF(J35=J36;L36;"");"");"")

The problem is if the person changes the location more than two times. In Column O to Column AA I have the months of the year which determines the location of the person.

For example:

J18: Adam Smith; L18: Berlin; O18: "blank" P18:1
J19: Adam Smith; L19: London; O19: 1; P19: "blank"
J20: Adam Smith; L20: NYCity; O20: "blank" P20:"blank"

This means that Adam Smith moved from London to Berlin

How can I modify this code to do the above:

=IF(J35=J36;IF(COUNTIF(J:J;J35)>1; "From "&L35 &" to "& IF(J35=J36;L36;"");"");"")

Aucun commentaire:

Enregistrer un commentaire