Hi Divisha,

It depends what you mean by compare?

That said, the numbers in that table are text so Excel can't do any math calculations on it. You can't have two values in a single cell and retain number characteristics. It will automatically be converted to text.

How did you want to compare them? Can you please give an example based on the data in the image. Better still, can you please upload a sample file so we can test any solutions we might have in mind.

Thanks,

Mynda

Figured it out, but took a while to execute. However, wondering if there is an easier method?

Using Text to columns, I split the range into a lower and upper limit for 2013 data and cleaned it up. Did the same for the 2016 data.

The format was text with a $ value, so used the substitute function to remove that and then paste as value and converted to number. What I could have done was to execute this at the raw data stage, would have reduced the number of instances I had do the step.

Then for each agency, I subtracted the data from the corresponding 2013 data and put and IFERROR function to cover my basis for N/A "=IFERROR(D4-$B4,"N/A")".

After which I applied a conditional format to highlight the differences. It resulted in this.

Hi Divisha,

Glad you figured out a solution. I think you answered your own question about what you could do to make this easier: "What I could have done was to execute this at the raw data stage, would have reduced the number of instances I had do the step."

The other option is to use Power Query to tidy up the data. If this is something you're going to do regularly then I'd highly recommend Power Query.

Mynda