Formula to track duplicate text/values across multiple columns

02/23/21
Answered - Pending Review

Hi there,

Apologies if this question has been asked multiple times but i have searched across the forums and cannot seem to find a definite answer to a problem i'm trying to find a solution for.

I have built a tracker to track web activities across our site encompassing language, page that the activity occurred on along with what is happening. An example of the data is below:-

Language Page Target Activity

US, GB, FR /us/en.html, /us/en/product1.html, /us/en/product2.html image update

US, GB, DE /us/en/product1.html, /us/en/product2.html overlay modal

US, GB, FR /us/en.html, /us/en/product1.html overlay modal

I'm trying to figure out how to build out a formula to identify where the same value occurs across columns therefore indicating a "clash" of activities which would require investigation.

For example, language, page target and activity are the column headings and the rows contains text separated out by commas so it would be great if there was a way for a formula to identify the individual values in a row.

In the table example above, there would be a clash in the US and GB languages on the product1.html page as there is the same activity occuring in the same language on the same page. Is there any type of formula that could help identify this clash either via conditional formatting or another way or am i wishfully thinking 😀?

Hopefully this makes sense and any help is greatly appreciated!

Thank you

Popular Tags:

Answers

  • Bassam.M KhalilBassam.M Khalil ✭✭✭✭✭

    Hi @Simon Haddock 

    Hope you are fine, could you please share exported excel file for your page (Delete/replace any confidential/sensitive information before sharing) and on this excel sheet explain manually a sample of what you want to do, and i will try to help you.

    Best Regards

    Bassam.M Khalil


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as the accepted answer. It will make it easier for other Smartsheet Community members to find this solution or help to answer their questions.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi @Simon Haddock

    I hope you're well and safe!

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Hi there,

    There is quite a lot of sensitive information in my original sheet so i created a dummy and attached a sample screenshot below which admittedly is a simplified version. To explain the process in a bit more detail, our website is quite expansive with a number of marketing activities taking place on various pages. It can become a little unruly to figure out where two items of the same nature potentially appear at the same time on the same page without a bunch of filtering and review.

    Apologies this might be too simplistic for you to help but looking for a starting point with a potential formula.


  • Andrée StaråAndrée Starå ✭✭✭✭✭

    @Simon Haddock

    I'd recommend starting with splitting everything up to separate columns.

    Make sense?

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • You mean splitting up the individual items in each column into their own column?

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    @Simon Haddock

    Yes, exactly. That would make it easier to identify the issues.

    Make sense?

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.