Compare two columns

Discuss the spreadsheet application

Compare two columns

Postby dawidek990 » Thu Dec 13, 2018 10:34 am

Hello, I need to compare two columns.
I have my product list, and I need to compare it with another product list to check if I have it at my place.

for example:
Somewhere in the column "A" will be a product ARTISTIC WAY WHITE STRUCTURE 20x40

Here should pop out that it occurs.
Names may vary slightly, the beginning is rather the same.

I need something like "Check if in Column A is the first 15 characters from cell B1"

Then drag it synchronously to B2, B3, B4 etc ...

It can be done?
OpenOffice 4.1.6 Windows 10
Posts: 33
Joined: Thu Dec 13, 2018 10:27 am

Re: Compare two columns

Postby FJCC » Thu Dec 13, 2018 11:38 am

Try a formula like
Code: Select all   Expand viewCollapse view
=COUNTIF($A$1:$A$100;LEFT(B1;15) & ".*")

Make sure that you go to the menu Tools -> Options, expand the Calc list on the left, choose Calculate and select Enable Regular Expressions in Formulas.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Posts: 7229
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Compare two columns

Postby Lupp » Thu Dec 13, 2018 11:42 am

Welcome to the forum!

To do exactly as you described (except the last line and the sloppy "vary slightly") is simple but doesn't make sense, imo.

-1- Last line: You describd column B as containing data. It cannot contain the formulae at the same time.

-2- Doubts in sense: What about the specific number 15? That looks strange. What about the effect of typos (or other minor differences)? What use in knowing the "is in" without the where? What is "Names may vary slightly, the beginning is rather the same." supposed to mean? How should it be compatible with the 15-characters-condition?

-3- Questions to get it clear: Will the content of column B always be at the start of a matching content in column A? Must the comparison be case sensitive? Should leading/trailing/aditionally_inserted spaces be ignored? If the position of a match shall be reurned: Only the first occurrence? One occurrence at maximum assured? And again: What is "Names may vary slightly, the beginning is rather the same." supposed to mean?
How do you intend to determine the length of the used part of column A?
Since a solution using RegEx may be needed, it's substantial to know if you can assure the search string not to contain any of the special characters listed here:
Code: Select all   Expand viewCollapse view

Please attach an example sheet containing specific examples covering all the relevant cases in columns A and B and the expected results in column C as an .ods.
Without having this further considerations might simply be wasted time.

And please: Consider your question thoroughly before posting it. If you edit it while a helper is already on the way to find a solution, he has to rework his answer expectably as often as you edit your question. Use the preview function, consider if the drafted post tells what you wanted as precisely as possible, edit if needed, and post finally.
Further explanations/modifications to your question should either be
- explicitly marked as done by editing or
- given in an additional post in the thread.
User avatar
Posts: 2523
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Return to Calc

Who is online

Users browsing this forum: No registered users and 19 guests