Row report to show data from 2 sheets if column data matches both

Stephanie Y

I have 2 sheets with very similar, if not identical, data except each sheet is for a different year.

I am looking to create a row report to show only the data if the category column matches on both sheets to compare the differences from this year to last. I already have a formula to remove the parent row from the report. I am looking to remove the categories that haven't been completed in both years (Analgesics, Aseptic Juice, etc). Any ideas?

2022

2021

Paul Newcome

You would need a helper column that contains a formula on each that will look at the other and flag rows that match your criteria. Basically something that says if the other sheet does contain the complete row and this sheet contains the complete row then flag.

You would then include this in your report filter.

Stephanie Y

Hi Paul.

Thanks for your response. I am still running into some issues. It may be due to the current helper column I have that is excluding parent rows from showing in my report. Any help would be welcome.

Paul Newcome

Based on your screenshots, this additional helper column should be included in the report filter in addition to your existing on that excludes parent rows. What issue are you running into exactly?

Stephanie Y

I was able to get it to work.

I changed the helper column to a check box and used the following formula:

=COUNTIFS({2021 Range 3}, Category1) to select the checkbox if the cell data in teh category column matched in both sheets.

I was able to filter my row report to show to show only the checked rows.

Thanks for your help.