Compare two columns and post matches to third
Compare two columns and post matches to third
I have done very little with Calc and I only need to do this operation.
I have two columns of the names of drawings.
In column A are all the drawings I am concerned about.
In column B are some drawings that are also in A but not all.
I only want the drawings that are in both columns and have that post to col c
Is there an easy way to do this?
Many thanks!
I have two columns of the names of drawings.
In column A are all the drawings I am concerned about.
In column B are some drawings that are also in A but not all.
I only want the drawings that are in both columns and have that post to col c
Is there an easy way to do this?
Many thanks!
Rick, open office 4.1.5, Windows 10
Re: easy way to compare two columns and post matches to thir
I remember that questions to the same (or very similar) effect were often posted in this forum and also in https://ask.libreoffice.org/en . However, the wording allows so many basically different variants that it may be difficult to find old threads offering solutions. (Your wording is clear in a pleasant way.) That's one of the fundamental troubles with forums.
This is concerning a kind of filtering, however, and you will find the fundamentals in the respective Calc tutorial by MrProgrammer: viewtopic.php?f=75&t=38056.
I don't feel sure what's "easy" enough under your judgement.
You may study the attached example containg a little collection of solutions I also posted earlyer. However, I did not include soultions relying on the OFFSET() function in this case.
The solutions were designed in LibO V 6.2.0.3 and tested also with AOO V 4.1.5.
If you don't permit macro execution, the solution demonstrated in the third sheet cannot work.
This is concerning a kind of filtering, however, and you will find the fundamentals in the respective Calc tutorial by MrProgrammer: viewtopic.php?f=75&t=38056.
I don't feel sure what's "easy" enough under your judgement.
You may study the attached example containg a little collection of solutions I also posted earlyer. However, I did not include soultions relying on the OFFSET() function in this case.
The solutions were designed in LibO V 6.2.0.3 and tested also with AOO V 4.1.5.
If you don't permit macro execution, the solution demonstrated in the third sheet cannot work.
- Attachments
-
- aoo97412DataColumnsIntersectionKindOf_1.ods
- (88.8 KiB) Downloaded 390 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: easy way to compare two columns and post matches to thir
This is a native database task. Trivial to do with database programs.
With Calc you have to adjust two settings:
menu:Tools>Options>Calc>Calculation
Comparison operators ... match whole cell = ON (don't match "abc" when searching for "a", "ab" or "bc", "b", "bc" or c)
Regular expressions in formulas = OFF (unless you know exactly why you want them)
Both settings are stored for the current document only.
=ISNUMBER(MATCH(value ; column ; 0)) returns True if value occurs in column, False otherwise.
=COUNTIF(column ; value) returns how often value occurs in column.
With Calc you have to adjust two settings:
menu:Tools>Options>Calc>Calculation
Comparison operators ... match whole cell = ON (don't match "abc" when searching for "a", "ab" or "bc", "b", "bc" or c)
Regular expressions in formulas = OFF (unless you know exactly why you want them)
Both settings are stored for the current document only.
=ISNUMBER(MATCH(value ; column ; 0)) returns True if value occurs in column, False otherwise.
=COUNTIF(column ; value) returns how often value occurs in column.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- MrProgrammer
- Moderator
- Posts: 4908
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: easy way to compare two columns and post matches to thir
You did not attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself) so I will make these assumptions:wilmath wrote:I only want the drawings that are in both columns and have that post to col c
• there are 19 unique drawings in A1:A19
• there are 52 drawings in B1:B52
In C1 enter formula =IF(SUMPRODUCT($B$1:$B$52=A1);A1;""). Fill the formula down to C19. Select column C (click the C above C1). Edit → Copy; Edit → Paste Special → ☐Paste All → ☑Text → ☐Formulas → OK; Data → Sort → Current selection → OK.
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: easy way to compare two columns and post matches to thir
Thanks everyone for the help. I have attached the spreadsheet and I'm trying the solutions offered.
- Attachments
-
- Bloomjam comparing lists.ods
- (15.44 KiB) Downloaded 302 times
Rick, open office 4.1.5, Windows 10
Re: easy way to compare two columns and post matches to thir
Without additional explanations the attachment will not be very helpful. What about the numbers? What about "suffixes" like "ff" or even "fff"? What about probable differences in the case (uoper/lower).
Checking for identical contents of the cells only one item is to be listed as a result. Reducing the requirements to "Cellcontent in column B is occurring as a substring of any content in column A" we get 6 matching items ...
Checking for identical contents of the cells only one item is to be listed as a result. Reducing the requirements to "Cellcontent in column B is occurring as a substring of any content in column A" we get 6 matching items ...
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: easy way to compare two columns and post matches to thir
The entries are exact names of the drawings. The numbers and suffixes like ff or fff are part of the name they are not different file types. The case changes shouldn't matter. If a drawing is in both columns the case should be consistent.
Rick, open office 4.1.5, Windows 10
Re: easy way to compare two columns and post matches to thir
I tried Mr.Programmers solution but only got as far as the paste/special.
Am I supposed to copy the contents of column C and then paste/special that right back on column C?
Thanks everyone for the help and I apologize for my ignorance. I will start doing the tutorials, but I would like to process these drawings as quick as possible.
Am I supposed to copy the contents of column C and then paste/special that right back on column C?
Thanks everyone for the help and I apologize for my ignorance. I will start doing the tutorials, but I would like to process these drawings as quick as possible.
Rick, open office 4.1.5, Windows 10
Re: easy way to compare two columns and post matches to thir
Regarding the given (incomplete) explanations "Squares" is the only match.
However, the questioner wouldn't have asked this question if it was that simle to get the result by mostly 10 minutes of considerate inspection of his sheet by eye. So: How is this going on? There must be something else.
I cannot but express my opinion that names of the exemplified kind will have some typos in them if entered via the keyboard. ...
However, the questioner wouldn't have asked this question if it was that simle to get the result by mostly 10 minutes of considerate inspection of his sheet by eye. So: How is this going on? There must be something else.
I cannot but express my opinion that names of the exemplified kind will have some typos in them if entered via the keyboard. ...
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Compare two columns and post matches to third
Sort each column into alphabetical order. It is now easy visually to compare them side-by-side. Perhaps delete the ones in the right column which are not in the left column.
In your example file copy the contents of a cell in the right column and use Find and replace to search for it in the left column.
If necessary, add two extra columns and number each row in each column so that you can undo the sort by sorting on the numbers.
See Calc functions similar to database functions in the Calc Guide - it has things like VLOOKUP, COUNTIF and SUMIF
Showing that a problem has been solved helps others searching so, if your problem is now solved, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.
In your example file copy the contents of a cell in the right column and use Find and replace to search for it in the left column.
If necessary, add two extra columns and number each row in each column so that you can undo the sort by sorting on the numbers.
See Calc functions similar to database functions in the Calc Guide - it has things like VLOOKUP, COUNTIF and SUMIF
VLOOKUP
Search for a specific value across the rows in the first column of an array. Returns the value from a different column in the same row.
Do a google search with find common words in two lists - it gets 700 million hits.The COUNTIF and SUMIF functions calculate their values based on search criteria.
The search criteria can be a number, expression, text string, or even a regular expression. The search criteria can be contained in a referenced cell or it can be included directly in the function call.
The COUNTIF function counts the number of cells in a range that match specified criteria. The first argument to COUNTIF specifies the range to search and second argument is the search criteria. Table 18 illustrates different search criteria using the COUNTIF function referencing the data shown in Table 23.
The first two arguments for SUMIF serve the same purpose as the arguments for COUNTIF; the range that contains the cells to search and the search criteria. The third and final argument for SUMIF specifies the range to sum. For each cell in the search range that matches the search criteria, the corresponding cell in the sum range is added into the sum.
Showing that a problem has been solved helps others searching so, if your problem is now solved, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.
Last edited by John_Ha on Mon Mar 18, 2019 6:46 pm, edited 1 time in total.
LO 6.4.4.2, Windows 10 Home 64 bit
See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.
Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.
Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
- MrProgrammer
- Moderator
- Posts: 4908
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Compare two columns and post matches to third
Yes. Since Formulas is unchecked in the Paste Special dialog, the procedure converts formulas to their values so the sort will move the matches to the top of column C and the empty cells to the bottom.wilmath wrote:Am I supposed to copy the contents of column C and then paste/special that right back on column C?
The Ten Concepts tutorial is not long and it should answer many questions that beginners have about spreadsheets. I hope you will find that studying it is time well spent.wilmath wrote:I will start doing the tutorials, but I would like to process these drawings as quick as possible.
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Last edited by MrProgrammer on Mon Mar 18, 2019 4:45 pm, edited 1 time in total.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Compare two columns and post matches to third
Results of the COUNTIF and MATCH formula ordered descending by the COUNTIF results with global option "search ... match whole cell" = OFF in order to match any value that occurs within values of the other list (column A).
Another glitch is that the 3X12 entry has a trailing space which does not make a difference with this limited data set but may make a difference when "3X12" occurs at the end of a string in the other list (column A). In this case "3X12 " with trailing space would not be part of "23xyz 3X12"
Code: Select all
VALUE COUNTIF ISNUMBER(MATCH)
-------------------------------------------------------
Squares 12 TRUE
Three 3 TRUE
Over and Under 2 TRUE
Curvy Long Bed 1 TRUE
Double Mirror 1 TRUE
ftwisty 1 TRUE
Repeating Plaid 1 TRUE
3X12 0 FALSE
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice