Help using a formula to compare two colums of text.

[Fizban]

Hello Forum,

I am trying to compare two columns with location data drawn from two different sources, thusly returned with two different methods of reporting.

Specifically (this data is made up as an example):

Client has two sites. Cardiff as the head office and London as the field office.

Client database lists these two as Cardiff and London.

Discovery tool lists these as HQ and Field respectively.

I am trying to find a function that would allow me to compare the two and match Cardiff to HQ, and London to Field. Reporting it in the manner displayed in the example below:


Sample Data 2.jpg

[Richard Buttrey]

Would you upload the workbook that contains the two lists so that we can see the request in context.

[PeteABC123]

I'd create a helper column consisting of creating a combined value of your row fields.
For example:
In D2 enter
=A2&B2 and copy down.
In C2, enter and copy down:
=IF(COUNTIF(D:D,D2)>1,"TRUE","FALSE")

If this helps you, please let us know.

Pete

[Fizban]

Ok, I'll try that when I get into work. I can imagine it working, not sure why I didn't just do that in the first place :P

I cannot upload the worksheet as it contains sensitive data. Sorry.

[Tony Valko]

Maybe something like this...

=OR(AND(A2="Cardiff",B2="HQ"),AND(A2="London",B2="Field"))

Copy down

[Fizban]

Thanks Tony!

Your formula worked perfectly.

Question Resolved.

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.