How to find if 2 columns (or rows) in LibreOffice Calc are identical?
I have two long columns of data in LibreOffice Calc, something like below:
Jimmy | Jimmy
Bunk | Bunk
Lester | Lester
Prez | Prez
...
These columns are most likely identical, that is, they contain exactly the same entries. However it is possible that for some reason they are not, for example if one of the columns was re-ordered. I want to compare them to see if they are indeed identical.
How do I compare two columns (or two rows) in Calc and see if they are exactly the same?
worksheet-function libreoffice-calc
asked Mar 7 '16 at 11:28
Mirzhan IrkegulovMirzhan Irkegulov
774829
add a comment |
I have two long columns of data in LibreOffice Calc, something like below:
Jimmy | Jimmy
Bunk | Bunk
Lester | Lester
Prez | Prez
...
These columns are most likely identical, that is, they contain exactly the same entries. However it is possible that for some reason they are not, for example if one of the columns was re-ordered. I want to compare them to see if they are indeed identical.
How do I compare two columns (or two rows) in Calc and see if they are exactly the same?
worksheet-function libreoffice-calc
asked Mar 7 '16 at 11:28
Mirzhan IrkegulovMirzhan Irkegulov
774829
add a comment |
I have two long columns of data in LibreOffice Calc, something like below:
Jimmy | Jimmy
Bunk | Bunk
Lester | Lester
Prez | Prez
...
These columns are most likely identical, that is, they contain exactly the same entries. However it is possible that for some reason they are not, for example if one of the columns was re-ordered. I want to compare them to see if they are indeed identical.
How do I compare two columns (or two rows) in Calc and see if they are exactly the same?
worksheet-function libreoffice-calc
asked Mar 7 '16 at 11:28
Mirzhan IrkegulovMirzhan Irkegulov
774829
I have two long columns of data in LibreOffice Calc, something like below:
Jimmy | Jimmy
Bunk | Bunk
Lester | Lester
Prez | Prez
...
These columns are most likely identical, that is, they contain exactly the same entries. However it is possible that for some reason they are not, for example if one of the columns was re-ordered. I want to compare them to see if they are indeed identical.
How do I compare two columns (or two rows) in Calc and see if they are exactly the same?
worksheet-function libreoffice-calc
worksheet-function libreoffice-calc
asked Mar 7 '16 at 11:28
Mirzhan IrkegulovMirzhan Irkegulov
774829
asked Mar 7 '16 at 11:28
Mirzhan IrkegulovMirzhan Irkegulov
774829
asked Mar 7 '16 at 11:28
Mirzhan IrkegulovMirzhan Irkegulov
774829
asked Mar 7 '16 at 11:28
Mirzhan IrkegulovMirzhan Irkegulov
774829
asked Mar 7 '16 at 11:28
Mirzhan IrkegulovMirzhan Irkegulov
774829
774829
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Suppose Jimmy is in cells A1 and B1. In cell C3 use function EXACT, so the formula of cell C3 will be:
=EXACT(A1,B1)
Apply the same formula for every cell in column C. In Calc you can grab the little black square in the cell and drag it, so the same formula is applied for multiple cells, but the cells of column A and B are changed accordingly.
Column C now shows TRUE if and only if cells in corresponding columns to the left are both identical.
Then for every value of column C apply function AND. For example if there are 100 entries in column C, your cell would look like:
=AND(C1:C100)
The last cell shows TRUE if and only if all cells in column C are TRUE.
If there's a simpler method to compare two columns or two rows in Calc, don't hesitate to post it as an answer.
answered Mar 7 '16 at 11:28
Mirzhan IrkegulovMirzhan Irkegulov
774829
Instead ofEXACTyou can simply enter=A1=B1. One possibility similar to your solution is to use an Array Formula, although I do not necessarily recommend it.
– Jim K
Mar 8 '16 at 2:16
add a comment |
LibreOffice Base can compare lists more effectively. However some setup is required. Try these steps:
- Insert a new row in Calc. Set contents of cell A1 to
C1and cell A2 toC2.
File -> New -> Database.Connect to an existing database, selectingSpreadsheetfrom the list.- Press
Next>>and specify the location of the spreadsheet. PressFinishand save the database. - Under
Tables, "Sheet1" should now appear (or whatever your sheet is named). When you double-click it, the data should be displayed. - Go to
Queriesand chooseCreate Query in SQL View. Enter the following query:
SELECT C1,C2 FROM "Sheet1" WHERE C1 <> C2
Now save and view the query. It will display all rows which are different.
To reduce this to a single value, use COUNT(*) instead.
answered Mar 8 '16 at 2:09
Jim KJim K
2,3411410
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1049683%2fhow-to-find-if-2-columns-or-rows-in-libreoffice-calc-are-identical%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Suppose Jimmy is in cells A1 and B1. In cell C3 use function EXACT, so the formula of cell C3 will be:
=EXACT(A1,B1)
Apply the same formula for every cell in column C. In Calc you can grab the little black square in the cell and drag it, so the same formula is applied for multiple cells, but the cells of column A and B are changed accordingly.
Column C now shows TRUE if and only if cells in corresponding columns to the left are both identical.
Then for every value of column C apply function AND. For example if there are 100 entries in column C, your cell would look like:
=AND(C1:C100)
The last cell shows TRUE if and only if all cells in column C are TRUE.
If there's a simpler method to compare two columns or two rows in Calc, don't hesitate to post it as an answer.
answered Mar 7 '16 at 11:28
Mirzhan IrkegulovMirzhan Irkegulov
774829
Instead ofEXACTyou can simply enter=A1=B1. One possibility similar to your solution is to use an Array Formula, although I do not necessarily recommend it.
– Jim K
Mar 8 '16 at 2:16
add a comment |
Suppose Jimmy is in cells A1 and B1. In cell C3 use function EXACT, so the formula of cell C3 will be:
=EXACT(A1,B1)
Apply the same formula for every cell in column C. In Calc you can grab the little black square in the cell and drag it, so the same formula is applied for multiple cells, but the cells of column A and B are changed accordingly.
Column C now shows TRUE if and only if cells in corresponding columns to the left are both identical.
Then for every value of column C apply function AND. For example if there are 100 entries in column C, your cell would look like:
=AND(C1:C100)
The last cell shows TRUE if and only if all cells in column C are TRUE.
If there's a simpler method to compare two columns or two rows in Calc, don't hesitate to post it as an answer.
answered Mar 7 '16 at 11:28
Mirzhan IrkegulovMirzhan Irkegulov
774829
Instead ofEXACTyou can simply enter=A1=B1. One possibility similar to your solution is to use an Array Formula, although I do not necessarily recommend it.
– Jim K
Mar 8 '16 at 2:16
add a comment |
Suppose Jimmy is in cells A1 and B1. In cell C3 use function EXACT, so the formula of cell C3 will be:
=EXACT(A1,B1)
Apply the same formula for every cell in column C. In Calc you can grab the little black square in the cell and drag it, so the same formula is applied for multiple cells, but the cells of column A and B are changed accordingly.
Column C now shows TRUE if and only if cells in corresponding columns to the left are both identical.
Then for every value of column C apply function AND. For example if there are 100 entries in column C, your cell would look like:
=AND(C1:C100)
The last cell shows TRUE if and only if all cells in column C are TRUE.
If there's a simpler method to compare two columns or two rows in Calc, don't hesitate to post it as an answer.
answered Mar 7 '16 at 11:28
Mirzhan IrkegulovMirzhan Irkegulov
774829
Suppose Jimmy is in cells A1 and B1. In cell C3 use function EXACT, so the formula of cell C3 will be:
=EXACT(A1,B1)
Apply the same formula for every cell in column C. In Calc you can grab the little black square in the cell and drag it, so the same formula is applied for multiple cells, but the cells of column A and B are changed accordingly.
Column C now shows TRUE if and only if cells in corresponding columns to the left are both identical.
Then for every value of column C apply function AND. For example if there are 100 entries in column C, your cell would look like:
=AND(C1:C100)
The last cell shows TRUE if and only if all cells in column C are TRUE.
If there's a simpler method to compare two columns or two rows in Calc, don't hesitate to post it as an answer.
answered Mar 7 '16 at 11:28
Mirzhan IrkegulovMirzhan Irkegulov
774829
answered Mar 7 '16 at 11:28
Mirzhan IrkegulovMirzhan Irkegulov
774829
answered Mar 7 '16 at 11:28
Mirzhan IrkegulovMirzhan Irkegulov
774829
answered Mar 7 '16 at 11:28
Mirzhan IrkegulovMirzhan Irkegulov
774829
774829
Instead ofEXACTyou can simply enter=A1=B1. One possibility similar to your solution is to use an Array Formula, although I do not necessarily recommend it.
– Jim K
Mar 8 '16 at 2:16
add a comment |
Instead ofEXACTyou can simply enter=A1=B1. One possibility similar to your solution is to use an Array Formula, although I do not necessarily recommend it.
– Jim K
Mar 8 '16 at 2:16
Instead of
EXACT you can simply enter =A1=B1. One possibility similar to your solution is to use an Array Formula, although I do not necessarily recommend it.– Jim K
Mar 8 '16 at 2:16
Instead of
EXACT you can simply enter =A1=B1. One possibility similar to your solution is to use an Array Formula, although I do not necessarily recommend it.– Jim K
Mar 8 '16 at 2:16
add a comment |
LibreOffice Base can compare lists more effectively. However some setup is required. Try these steps:
- Insert a new row in Calc. Set contents of cell A1 to
C1and cell A2 toC2.
File -> New -> Database.Connect to an existing database, selectingSpreadsheetfrom the list.- Press
Next>>and specify the location of the spreadsheet. PressFinishand save the database. - Under
Tables, "Sheet1" should now appear (or whatever your sheet is named). When you double-click it, the data should be displayed. - Go to
Queriesand chooseCreate Query in SQL View. Enter the following query:
SELECT C1,C2 FROM "Sheet1" WHERE C1 <> C2
Now save and view the query. It will display all rows which are different.
To reduce this to a single value, use COUNT(*) instead.
answered Mar 8 '16 at 2:09
Jim KJim K
2,3411410
add a comment |
LibreOffice Base can compare lists more effectively. However some setup is required. Try these steps:
- Insert a new row in Calc. Set contents of cell A1 to
C1and cell A2 toC2.
File -> New -> Database.Connect to an existing database, selectingSpreadsheetfrom the list.- Press
Next>>and specify the location of the spreadsheet. PressFinishand save the database. - Under
Tables, "Sheet1" should now appear (or whatever your sheet is named). When you double-click it, the data should be displayed. - Go to
Queriesand chooseCreate Query in SQL View. Enter the following query:
SELECT C1,C2 FROM "Sheet1" WHERE C1 <> C2
Now save and view the query. It will display all rows which are different.
To reduce this to a single value, use COUNT(*) instead.
answered Mar 8 '16 at 2:09
Jim KJim K
2,3411410
add a comment |
LibreOffice Base can compare lists more effectively. However some setup is required. Try these steps:
- Insert a new row in Calc. Set contents of cell A1 to
C1and cell A2 toC2.
File -> New -> Database.Connect to an existing database, selectingSpreadsheetfrom the list.- Press
Next>>and specify the location of the spreadsheet. PressFinishand save the database. - Under
Tables, "Sheet1" should now appear (or whatever your sheet is named). When you double-click it, the data should be displayed. - Go to
Queriesand chooseCreate Query in SQL View. Enter the following query:
SELECT C1,C2 FROM "Sheet1" WHERE C1 <> C2
Now save and view the query. It will display all rows which are different.
To reduce this to a single value, use COUNT(*) instead.
answered Mar 8 '16 at 2:09
Jim KJim K
2,3411410
LibreOffice Base can compare lists more effectively. However some setup is required. Try these steps:
- Insert a new row in Calc. Set contents of cell A1 to
C1and cell A2 toC2.
File -> New -> Database.Connect to an existing database, selectingSpreadsheetfrom the list.- Press
Next>>and specify the location of the spreadsheet. PressFinishand save the database. - Under
Tables, "Sheet1" should now appear (or whatever your sheet is named). When you double-click it, the data should be displayed. - Go to
Queriesand chooseCreate Query in SQL View. Enter the following query:
SELECT C1,C2 FROM "Sheet1" WHERE C1 <> C2
Now save and view the query. It will display all rows which are different.
To reduce this to a single value, use COUNT(*) instead.
answered Mar 8 '16 at 2:09
Jim KJim K
2,3411410
answered Mar 8 '16 at 2:09
Jim KJim K
2,3411410
answered Mar 8 '16 at 2:09
Jim KJim K
2,3411410
answered Mar 8 '16 at 2:09
Jim KJim K
2,3411410
2,3411410
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1049683%2fhow-to-find-if-2-columns-or-rows-in-libreoffice-calc-are-identical%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
