Data Comparison

[Redgirl9487]

Hey,

I was wondering if someone could me please?

I have to summarise client data per month and compare that data for 2021 Vs 2022 So it reads something like image 1.

I have a large data dump, but I have split it into tab 1 which is the data for 2021 & tab 2 which is the data for 2022

Image 2 is an example of how the data table is.

Please could someone advise the best way to go about this?

I would appreciate anyone's help as I am a new excel user

Many thanks in advance.

 

[Legacy 143009]

It looks like 2 pivot tables will better suit your needs. You can filter the first one according to 2021 and the other according to 2022.

 

[FGaxha]

Try this: This will compare data between tabs in same workbook:

• n the worksheet where you want to highlight differences, select all used cells. For this, click the upper left cell of the used range, usually A1, and press Ctrl + Shift + End to extend the selection to the last used cell.
• On the Home tab, in the Styles group, click Conditional Formatting > New rule, and create a rule with the following formula:
  =A1<>Sheet2!A1
• It will highlighted the difference
  

 

[FGaxha]

Or: use this formula:
To compare two Excel worksheets for differences, just open a new empty sheet, enter the following formula in cell A1, and then copy it down and to the right by dragging the fill Handle:

=IF(Sheet1!A1 <> Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "")

 

[Redgirl9487]

Try this: This will compare data between tabs in same workbook:

• n the worksheet where you want to highlight differences, select all used cells. For this, click the upper left cell of the used range, usually A1, and press Ctrl + Shift + End to extend the selection to the last used cell.
• On the Home tab, in the Styles group, click Conditional Formatting > New rule, and create a rule with the following formula:
  =A1<>Sheet2!A1
• It will highlighted the difference

Thanks! Please could you confirm what rule type I would select please? Many thanks in advance.

 

[FGaxha]

See attached:
1- Use a formula to determine which cell to format
2- enter formula: =A1<>Sheet2!A1
3- Select the Pattern Color

Hope this will help?

 

[Redgirl9487]

See attached:
1- Use a formula to determine which cell to format
2- enter formula: =A1<>Sheet2!A1
3- Select the Pattern Color

Hope this will help?

Thanks. For some reason it does not seem to work. I have copied your formula into a new sheet however, no data information is being pulled from the sheets named 2021 and 2022.

 

[FGaxha]

You must select sheet1.
Select A1 on sheet 1 then press: Ctrl+Shift+End so it will select the entire data then use format conditions and the formula

 

[Redgirl9487]

It looks like 2 pivot tables will better suit your needs. You can filter the first one according to 2021 and the other according to 2022.

Thanks. Please could you advise how I would create a pivot table?

 

[FGaxha]

Follow the steps as per attachment below: