Identify records with same value in Column 1 but different value in Column 2

yatin.ajbani
yatin.ajbani
in Modeling

Hi Team 

I need to identify records that have same value in Column 1 but different values in Column 2. 

For eg, from the below data set, I want to identify the 1st 2 records may be by adding another line item

NameCodeTimeline
Roger F1Month
Roger Federer1Quarter
Rafael N2Month
Rafael Nadal2Month

Thanks

Tagged:

Best Answer

  • JaredDolich
    JaredDolich
    Answer ✓

    @yatin.ajbani 

    Great use case. I can think of a couple ways to handle this, I'm sure there are others.

    1. Create two line items one for code and one for the time scale. Add another line item in your system module for the names that equals the FIRSTOCCURANCE for the time scale for each name. The code line item should equal one (1). The time scale is a lookup that equals 1 if it matches the FIRSTOCCURANCE, 0 if not. If the SUM of the codes equals the SUM of the time scale then they're all the same time scale. If they differ then you have duplicates and you can use the 0 or 1 to identify them.
    2. Concatenate the Code and the Time Scale, create a line item that equals one (1) and use the SUM function on the list formatted line item, presumably the code in your case. If the SUM for any of the concatenated list items equals the count of the name list item then no duplicates else duplicates found. Downside to this is you have to create another list for the concatenated combinations.

    I was thinking there might be a way to use the RANK function too but that would require some testing to see if that would work. With the RANK function you can use the group text variable. If the MAX rank is greater than 1 then you have duplicates and any rank greater than 1 you can use to identify as an exception. Not sure if that would work but it would be easier than the two options above.

Answers

Categories