Identifying identical values in dynamic ranges

[codejak]

Hi all!

Can someone help me with the following (simplified) problem?

I have two worksheets, A and B.

A contains three columns with currency codes (EUR, USD, CHF, etc.):

Please Login or Register  to view this content.

I now want to analyze these columns according to the following rules and write a result in a cell in worksheet B:

• include all filled columns
• if all currencies in the filled columns are the same, write that currency to the cell in B
• if they are not, write "XXX" in the cell in B

Is there a way to realize this with formulas? My problem is the part where I need to have Excel recognize how many of the columns are actually relevant for the analysis (i.e., defining the range dynamically).

Any ideas?

Regards

Matthias

[xld]

All cells in all columns or all cells in each column?

What impact is an empty cell?

[codejak]

Hi xld,

all cells in each row. I want to know for each row if the value for all currency columns is identical.

If a cell is empty, I want to exclude it from the comparison, i.e. check whether the value in the other two columns is identical. Equally, if two cells are empty in one row, the validation should of course return a 'true'.

Regards
Matthias