Formula to track duplicate text/values across multiple columns

Simon Haddock · 02/23/21

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

Bassam Khalil · 02/23/21

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.

Andrée Starå · 02/23/21

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, andree@workbold.com)

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!

Simon Haddock · 02/23/21

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å · 02/23/21

@Simon Haddock

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

Make sense?

Simon Haddock · 02/23/21

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

Andrée Starå · 02/23/21

@Simon Haddock

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

Make sense?

Formula to track duplicate text/values across multiple columns

Answers

Help Article Resources

Categories