Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

reconcile data from samew workbook but in different sheet

  Asked By: Holly    Date: Dec 21    Category: MS Office    Views: 4779
  

i got problem


data stored in the same workbook but in different sheet

sheet 1 and sheet 2

how to reconcile data from sheet 1 and sheet 2 and the result will be
display on sheet 3

and if the data only on the sheet 1 but not on the sheet 2 the
message box will pop up.


Share: 

 

3 Answers Found

 
Answer #1    Answered By: Fabiano Ferrrari     Answered On: Dec 21

You're going to need to give us much more to go on, here. What does the
data look like? What columns is it in? Do all the columns need to be the
same? Do you want a message for each anomaly or just one listing all of
them? Are the data  similarly ordered on the two sheets? Can you sort
them as part of the analysis? How did the data arrive on the two sheets
- different sources?

 
Answer #2    Answered By: Kara Hicks     Answered On: Dec 21

ACCOUNT NO AMOUNT
37811266 500
37546031 200
37688111 300
37555693 400

sheet 1


ACCOUNT NO AMOUNT
37811266 500
37546031 200
37688111 100
37555693 500


sheet 2



the system will reconcile between sheet1 and sheet2
the output will be display on summary sheet(sheet 3)

and if the system found error(amount in sheet1 and sheet2 differ)
between sheet1 and sheet2 it will display on different color.








37811266 500
37546031 200
37688111 300
37555693 400
37688111 100
37555693 500



 
Answer #3    Answered By: Jens Brown     Answered On: Dec 21

Here is a suggestion, taking three steps. It uses conditional
formatting, which you may or may not be familiar with.

Wherever the cell contents of sheet1 and sheet2 don't agree, the word
"Error" will be displayed in red.
Wherever the cell contents of sheet1 and sheet2 do agree, the cell
value will be displayed normally.

Step 1.
In sheet3, cell a1, type the formula:
=IF(Sheet1!A9="","",IF(Sheet1!A9=Sheet2!A9,Sheet1!A9,"Error"))

Step 2.
Use conditional formatting:
Click on cell a1 of sheet3.
Click on "Format", then click on "Conditional Formatting"
In the Conditional Formatting window, choose the following:
"Cell Value Is", "Equal to", then in the 3rd box  type ="Error"
Click on the "Format" button
o For Font Style, choose Bold
o For Color, choose red
o Click the OK button
Click the OK button again

Step 3.
Copy and paste cell a1 into as many cells as you need.

Hope this helps. Of course, you may choose other format options, or
choose to display something other than the word "Error".

 
Didn't find what you were looking for? Find more on reconcile data from samew workbook but in different sheet Or get search suggestion and latest updates.




Tagged: