CODEWITHSUNDEEP
excelconcatenationexcel-indirect
BillyBoy
BillyBoy

Reputation: 111

Comparing Columns in Multiple Excel Tabs using INDIRECT

I am looking for a very broad and dynamic way to check and see if the content in two columns are the same (the columns are in two different tabs). The content varies from text, numbers, and dates.

So far the only way I have tried doing this is by combining all the entries in each column and comparing them. For example, I would append all the entries of a column together and compare it with the other appended column. Unfortunately I haven't been able to find a way to append the columns in a quick and efficient manner.

I started with the CONCATENATE function but quickly realized that this function requires manual input of values and is not capable of recognizing ranges created with INDIRECT. If I could use INDIRECT with CONCATENATE then I would be able to compare the values of two columns easily.

I would ideally like to use INDIRECT to create the ranges since there are thousands of columns that I need to compare and I since know the locations of all the columns I am comparing.

The example below is very basic and is merely used to explain my needs. I am trying to compare cells C7:C16 with the column located within "Sheet1", cells A1:A10.

Another example, CONCATENATE(D7:16) would result it abcdefghij and would be compared with the CONCATENATE found in Sheet3!A1:A10.

Need: A way to concatenate entries in specified ranges using INDIRECT so they can be compared for correctness.

Any input on how to achieve this?

enter image description here

Upvotes: 0

Views: 723

Answers (1)

teylyn
teylyn

Reputation: 35915

You are trying to use Concatenate to append all cells in a range. That will not work. Concatenate needs individual arguments, not a range.

For such a comparison you would need an array formula.

Select all cells from C7 down to C100 (or however many rows you will need for the column with the most data), then enter this formula

=INDIRECT("'"&C$1&"'"&"!"&C$2&C$3&":"&C$2&C$4)

and confirm with Ctrl-Shift-Enter

All cells will be filled at once and you will see the values of the range you specified in the first four rows.

Copy the formulas to column D.

In E7 enter the formula

=C7=D7

and copy down. Now you can use a Countif to count the FALSE values in column E and use that to determine if the columns are identical. In C6:

=IF(COUNTIF(E:E,FALSE),"not the same","identical")

You can use conditional formatting to highlight the "FALSE" values.

Refer the screenshot.

enter image description here

Upvotes: 1

Related Questions