Compare two columns and post matches to third

Discuss the spreadsheet application

Compare two columns and post matches to third

Postby wilmath » Sun Mar 17, 2019 3:15 am

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!
Rick, open office 4.1.5, Windows 10
wilmath
 
Posts: 5
Joined: Tue Apr 17, 2018 9:53 pm

Re: easy way to compare two columns and post matches to thir

Postby Lupp » Sun Mar 17, 2019 2:47 pm

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.
Attachments
aoo97412DataColumnsIntersectionKindOf_1.ods
(88.8 KiB) Downloaded 7 times
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2339
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: easy way to compare two columns and post matches to thir

Postby Villeroy » Sun Mar 17, 2019 6:28 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26395
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: easy way to compare two columns and post matches to thir

Postby MrProgrammer » Sun Mar 17, 2019 7:00 pm

wilmath wrote:I only want the drawings that are in both columns and have that post to col c
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:
• 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.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3614
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: easy way to compare two columns and post matches to thir

Postby wilmath » Sun Mar 17, 2019 9:23 pm

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 13 times
Rick, open office 4.1.5, Windows 10
wilmath
 
Posts: 5
Joined: Tue Apr 17, 2018 9:53 pm

Re: easy way to compare two columns and post matches to thir

Postby Lupp » Sun Mar 17, 2019 9:41 pm

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 ...
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2339
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: easy way to compare two columns and post matches to thir

Postby wilmath » Sun Mar 17, 2019 10:07 pm

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
wilmath
 
Posts: 5
Joined: Tue Apr 17, 2018 9:53 pm

Re: easy way to compare two columns and post matches to thir

Postby wilmath » Sun Mar 17, 2019 10:12 pm

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.
Rick, open office 4.1.5, Windows 10
wilmath
 
Posts: 5
Joined: Tue Apr 17, 2018 9:53 pm

Re: easy way to compare two columns and post matches to thir

Postby Lupp » Sun Mar 17, 2019 10:39 pm

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. ...
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2339
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Compare two columns and post matches to third

Postby John_Ha » Mon Mar 18, 2019 2:44 pm

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

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.


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.


Clipboard01.gif
COUNTIF finds occurrences of items in col B in col A

Do a google search with find common words in two lists - it gets 700 million hits.

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.
AOO 4.1.6, Windows 7 Home 64 bit

See the Writer Manual, the Writer FAQ, the Writer Tutorials and the Writer guide.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 6460
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Compare two columns and post matches to third

Postby MrProgrammer » Mon Mar 18, 2019 3:14 pm

wilmath wrote:Am I supposed to copy the contents of column C and then paste/special that right back on column C?
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:I will start doing the tutorials, but I would like to process these drawings as quick as possible.
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.

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.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3614
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Compare two columns and post matches to third

Postby Villeroy » Mon Mar 18, 2019 3:58 pm

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).

Code: Select all   Expand viewCollapse view
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

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"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26395
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: Exabot [Bot], FJCC, Google [Bot], RusselB and 18 guests