Thread: Identify cells with same values from two columns

In a spreadsheet, I have 2 columns:
Column A contains the name of all the financial documents reports of our company.
Column B contains the name of all the financial documents which my team has worked (these reports can be for both external clients as well as for our company)
Ex:
Col A Col B
F1 F1
F2 F4
F3 F5
F5 F6
F11 F8
e.t.c
My task is to identify all the reports for which my team has worked. From the example, I should be able to extract the following report names: F1, F5

Thanks in advance for all your help.

Note: I am using MS Excell 2007 (Windows Platform)

Hello,

using a helper column, you can tag the rows that have a match in the other column, for example in column C

=IF(COUNTIF(A:A,B2),ROW(),"")

You can then use a formula to create a contiguous list of items that are found in both columns

=IFERROR(INDEX(B:B,SMALL(C:C,ROW(A1))),"")
Copy down as far as required.

see attached.

cheers,

Hi streetcat

For Common Values in both columns, use this formula:

=IF(COUNTIF($B$2:$B$6;A2)=1;A2;"")

For Uniques values in column A, use this formula:

=IF(COUNTIF($B$2:$B$6;A2)<>1;A2;"")


For Uniques values in column A, use this formula:

=IF(COUNTIF($A$2:$A$6;B2)<>1;B2;"")

Hope to helps you.

Using the same technique, you need two more helper columns, one to tag the rows for unique values in column A, one for column B. For example in column D:

=IF(COUNTIF(B:B,A2),"",ROW())

Then build a contiguous list of unique values in column A with

=IFERROR(INDEX(A:A,SMALL(D:D,ROW(A1))),"")

See attached. [edit: I've been trying to attach a file, but the forum software is in bad shape right now]

cheers,

Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes