ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Compare worksheets (https://www.excelbanter.com/excel-discussion-misc-queries/236000-compare-worksheets.html)

Lise

Compare worksheets
 
Good morning

I have three worksheets with the same amount of columns across each and the
same column headings - These columns show Paragraph numbers etc then list any
changes made to the paragragh - I want to:

*be able to compare all three worksheets and if a line is the same in all
three
*copy the specific line from each into a fourth worksheet

Sorry have tried to detail clearly - Can this be done?

Thanks as always


CLR

Compare worksheets
 
Use this in A1 and to copy around on sheet4

=IF(AND(Sheet1!A1=Sheet2!A1, Sheet1!A1=Sheet3!A1),Sheet1!A1,"NoMatch")


Vaya con Dios,
Chuck, CABGx3



"Lise" wrote in message
...
Good morning

I have three worksheets with the same amount of columns across each and
the
same column headings - These columns show Paragraph numbers etc then list
any
changes made to the paragragh - I want to:

*be able to compare all three worksheets and if a line is the same in all
three
*copy the specific line from each into a fourth worksheet

Sorry have tried to detail clearly - Can this be done?

Thanks as always




Lise

Compare worksheets
 
Chuck thanks so much this is exactly what I was after - however works so well
it now leads to me to one more question.

Below is a snippet of what I get once I follow your suggestion - what do I
need to add to the formula to have Column A return the actual wording used on
the actual sheet ie sheet 1 name - sheet two is a different name and so on so
in other words instead of no Match in that particular column I want the
actual word in which ever sheet to show

Hope this is clear enough :-)


A B C
NoMatch NoMatch 4.1

--
Thanks

Lise


"CLR" wrote:

Use this in A1 and to copy around on sheet4

=IF(AND(Sheet1!A1=Sheet2!A1, Sheet1!A1=Sheet3!A1),Sheet1!A1,"NoMatch")


Vaya con Dios,
Chuck, CABGx3



"Lise" wrote in message
...
Good morning

I have three worksheets with the same amount of columns across each and
the
same column headings - These columns show Paragraph numbers etc then list
any
changes made to the paragragh - I want to:

*be able to compare all three worksheets and if a line is the same in all
three
*copy the specific line from each into a fourth worksheet

Sorry have tried to detail clearly - Can this be done?

Thanks as always





Lise

Compare worksheets
 
Sorry Chuck I've had another look and feel I should explain a little better.

Once I action your suggestion I get columns A through to D C and D are the
main requirements - when they match (on all sheets) I want A and B to show
"Change on all" rather than "No Match" is this possible or am I making it too
hard?

also when I drag your suggestion down on sheet 4 its not picking up data ie
on the three sheets 4.1 is stated on line 2 column C but on sheet 4 it shows
as 0

NoMatch NoMatch 4.1 General requirements 0 0 0
0 0 0 Para 1: NoMatch 0 0


--
Thanks for your patience

Lise


"Lise" wrote:

Chuck thanks so much this is exactly what I was after - however works so well
it now leads to me to one more question.

Below is a snippet of what I get once I follow your suggestion - what do I
need to add to the formula to have Column A return the actual wording used on
the actual sheet ie sheet 1 name - sheet two is a different name and so on so
in other words instead of no Match in that particular column I want the
actual word in which ever sheet to show

Hope this is clear enough :-)


A B C
NoMatch NoMatch 4.1

--
Thanks

Lise


"CLR" wrote:

Use this in A1 and to copy around on sheet4

=IF(AND(Sheet1!A1=Sheet2!A1, Sheet1!A1=Sheet3!A1),Sheet1!A1,"NoMatch")


Vaya con Dios,
Chuck, CABGx3



"Lise" wrote in message
...
Good morning

I have three worksheets with the same amount of columns across each and
the
same column headings - These columns show Paragraph numbers etc then list
any
changes made to the paragragh - I want to:

*be able to compare all three worksheets and if a line is the same in all
three
*copy the specific line from each into a fourth worksheet

Sorry have tried to detail clearly - Can this be done?

Thanks as always





Lise

Compare worksheets
 
I have had a fiddle and think I'm almost there - stuck on one part of formula
though.
Formula is =IF(AND('[ISO9001Mapping]ISO 9001 Mapping'!C3='[AS4801Mapping]AS
4801 Mapping'!C3,'[ISO9001Mapping]ISO 9001 Mapping'!C3='[ISO14001Mapping]ISO
14001 Mapping'!C3),'[ISO9001Mapping]ISO 9001 Mapping'!B3,'[AS4801Mapping]AS
4801 Mapping'!B3,'[ISO14001Mapping]ISO 14001 Mapping'!B3)

Outcome wanted is that cell b3 from each of the stated workbooks copy into
the one cell below each other - so would look like

cell 1 cell 2
one line answer b3 sheet 1 answer
b3 sheet 2 answer
b3 sheet 3 answer

Look forward to hearing your thoughts

Lise


"Lise" wrote:

Sorry Chuck I've had another look and feel I should explain a little better.

Once I action your suggestion I get columns A through to D C and D are the
main requirements - when they match (on all sheets) I want A and B to show
"Change on all" rather than "No Match" is this possible or am I making it too
hard?

also when I drag your suggestion down on sheet 4 its not picking up data ie
on the three sheets 4.1 is stated on line 2 column C but on sheet 4 it shows
as 0

NoMatch NoMatch 4.1 General requirements 0 0 0
0 0 0 Para 1: NoMatch 0 0


--
Thanks for your patience

Lise


"Lise" wrote:

Chuck thanks so much this is exactly what I was after - however works so well
it now leads to me to one more question.

Below is a snippet of what I get once I follow your suggestion - what do I
need to add to the formula to have Column A return the actual wording used on
the actual sheet ie sheet 1 name - sheet two is a different name and so on so
in other words instead of no Match in that particular column I want the
actual word in which ever sheet to show

Hope this is clear enough :-)


A B C
NoMatch NoMatch 4.1

--
Thanks

Lise


"CLR" wrote:

Use this in A1 and to copy around on sheet4

=IF(AND(Sheet1!A1=Sheet2!A1, Sheet1!A1=Sheet3!A1),Sheet1!A1,"NoMatch")


Vaya con Dios,
Chuck, CABGx3



"Lise" wrote in message
...
Good morning

I have three worksheets with the same amount of columns across each and
the
same column headings - These columns show Paragraph numbers etc then list
any
changes made to the paragragh - I want to:

*be able to compare all three worksheets and if a line is the same in all
three
*copy the specific line from each into a fourth worksheet

Sorry have tried to detail clearly - Can this be done?

Thanks as always






All times are GMT +1. The time now is 06:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com