Forum Discussion

dmouser's avatar
dmouser
Frequent Visitor
8 years ago

Comparing Cells after Splitting

I had an issue with a data set that I previously posted about.  However, I've been given additional information/direction from the owners so now I'm dealing with a different challenges.

 

I have a list of items I'm try to measure against, all of which are collected throughout two columns assigned and completed.  I left them as-is, and end up creating a series (about 10) columns to returns results with formulas based on the values or relationships between these columns.  I have now split one of the cells by semi-colon into new rows.  As a benefit this allows me a unique count of items.  However, it breaks a formula I had that compares the two columns.  For examples, I had:

 

Column A

Apple; Orange; Kiwi

Appple

Kiwi

Orange; Kiwi

 

Column B

Apple; Orange

Appple

{Blank}

 Kiwi

 

Now, I have the following for just the first row:

 

Column A

Apple

Orange

Kiwi

 

Column B

Apple; Orange

Apple; Orange

Apple; Orange

 

How, I can I return the same if x, then true,fale results to compare the collective value of a series against the column B grouping?  If it helps, each row contains a unique ID (which has been carried down to multiple rows when I split on column A).

 

Thanks.

Need Help

5 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    8 years ago

    Hi dmouser,

     

    "How, I can I return the same if x, then true,fale results to compare the collective value of a series against the column B grouping?"

     

    <--- Could you clarify more details about your logic to achieve this?

     

    Regards,

    Jimmy Tao

    • dmouser's avatar
      dmouser
      Frequent Visitor
      8 years ago

      I may have solved this, but identified another challenge as a result.  So I could leave my formulas unaltered, I added a step to copy the column, then split.  Not sure if this is a best practice or not, but it allowed me to keep the original column and values to base my formula from.

       

      However, my formula was comparing values of Column A to Column B to determine if they align.  However, I noticed the sequences are not consistent, even though the values are.  So if I'm comparing this Apple; Orange; Kiwi to Kiwi; Apple; Orange they don't match even though the content align.  I do have the items in another table with their own IDs. 

       

      Can I sum of the values in the cell if they are found? 

       

      Fruite | ID

      Apple = 1

      Pear = 2

      Orange = 3

      Kiwi = 5

      Peach = 4

       

      Identified Fruits: Apple; Orange; Kiwi - sum of IDs = 9

      Deliverd Fruits: Kiwi; Apple; Orange - sum of IDs = 9

      • v-yuta-msft's avatar
        v-yuta-msft
        Community Support
        8 years ago

        Hi dmouser ,

         

        "So if I'm comparing this Apple; Orange; Kiwi to Kiwi; Apple; Orange they don't match even though the content align.  I do have the items in another table with their own IDs. "

         

        <---Could you share some sample data and results you want to achieve and explain more details about your requirement?

         

        Regards,

        Jimmy Tao