Page 1 of 1

Compare two columns

PostPosted: Thu Dec 13, 2018 10:34 am
by dawidek990
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
The B1 cell is ARTISTIC WAY WHITE

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?

Re: Compare two columns

PostPosted: Thu Dec 13, 2018 11:38 am
by FJCC
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.

Re: Compare two columns

PostPosted: Thu Dec 13, 2018 11:42 am
by Lupp
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.