Compare two columns and post matches to third

[wilmath]

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!

[Lupp]

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.

[Villeroy]

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.

[MrProgrammer]

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

[wilmath]

Thanks everyone for the help. I have attached the spreadsheet and I'm trying the solutions offered.

[Lupp]

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

[wilmath]

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.

[wilmath]

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.

[Lupp]

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

[John_Ha]

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.

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.

[MrProgrammer]

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.

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.

[Villeroy]

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

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"