compare two cells

[roycettu]

Column A....................................Column B
6858 3/4 in hcs hex die.................die 1-7/16" 3/4-10nc
20729 hex die 9/16-18nf................die 1-7/16" 9/16-18nf
5/8 in hcs hex die.........................die 1-7/16" 5/8-18nf
hex die 3/4-16nf............................2x4-8 SPF


I have a spreadsheet with 17,000 lines. I want to a function that will look in A1 and see if any words in that cell match any cells in B1.



=if("any words in A1"="anywords in B1","similar","no-match")

Something along those lines

[mrice]

In this case, what do you define as a word?

Is it any characters (including numbers) between two spaces or at the start/end of the string?

Or do you want to exclude substrings which contain numbers?

[roycettu]

In this case, what do you define as a word?

Is it any characters (including numbers) between two spaces or at the start/end of the string?

Or do you want to exclude substrings which contain numbers?

I want to compare anything. Numbers/Letters or combo. These would be defined withing a space on both sides of the word.

Example: 6858 3/4 in hcs hex die

"6858" , "3/4" , "in" , "hcs" , "hex" , "die" are all words

[zbor]

You can:

Insert one more column in A (for result)
Switch (former) A and B column (now B and C) so B became C and viceversa.
Select C column and Data -> Text to columns -> Delimited and use space for delimiter
in A column write: =SUMPRODUCT(--(C1:L1<>""), COUNTIF(B:B;"*"&C1:L1&"*")) and pull down.

If result is greater then 0 you have match.
I left number so you can hoe many matches you get.

[zbor]

But if you want your output use:

=if(SUMPRODUCT(--(C1:L1<>""), COUNTIF(B:B,"*"&C1:L1&"*"))>0,"similar","no-match")

[NBVC]

This formula is C2 might work for what you want...

Please Login or Register  to view this content.

In your case the word "die" exists in all the column A entries, and therefore they will all have matches in column B

[roycettu]

This formula is C2 might work for what you want...

Please Login or Register  to view this content.

In your case the word "die" exists in all the column A entries, and therefore they will all have matches in column B

Sorry, should of been more clearly, I am only comparing the two cells within the row.

a1 to b1
a2 to b2 etc.

[NBVC]

A slight change only:

Please Login or Register  to view this content.

[roycettu]

This formula is C2 might work for what you want...

Please Login or Register  to view this content.

In your case the word "die" exists in all the column A entries, and therefore they will all have matches in column B

I tinkered with this and couldn't get it to work

Example

WAND BRASS 12 CURVED".................. WAND BRASS 12" CURVED

This yields a "no match"

[NBVC]

I don't. See attached.

Note that the first 3 matched, but Curved" didn't due to the " at the end of the word.

[roycettu]

You can:

Insert one more column in A (for result)
Switch (former) A and B column (now B and C) so B became C and viceversa.
Select C column and Data -> Text to columns -> Delimited and use space for delimiter
in A column write: =SUMPRODUCT(--(C1:L1<>""), COUNTIF(B:B;"*"&C1:L1&"*")) and pull down.

If result is greater then 0 you have match.
I left number so you can hoe many matches you get.

I can't get this to give good readings either.

691....... MOWER BLADE 20"MURRAY......... BLACK PVC BOOT 7-SZ


This one yielded a 691 in your formula. Dosn't seem right

[roycettu]

I don't. See attached.

Note that the first 3 matched, but Curved" didn't due to the " at the end of the word.

Not sure what the diff was but this is working now once I took the formula from your spreadsheet and pasted it into note pad and re-copied/pasted back into excel.

I think this will lighten the workload a bit.

Thanks

[zbor]

I can't get this to give good readings either.

691....... MOWER BLADE 20"MURRAY......... BLACK PVC BOOT 7-SZ


This one yielded a 691 in your formula. Dosn't seem right

That was before I am only comparing the two cells within the row. So it's 691 match in ALL columns.

[Camps and Vans]

I have had a look at this and mine is slightly different, I have 2 cells each containing text each piece of text is seperated by a comma, for example in 1 cell I have :
(cell 1)No Dogs, BBQ, Day Fees, Fees, Fire Place, Toilets, 17UPS,
In another cell I have :
(cell 2)Dogs, Big Rigs, Boat Ramp, Camp Kitchen, Picnic Tables, Pool, Public Phone, 26UPS,

Now what I would like to do is compare the text in each cell and list what is different between cell 2 & cell 1

Thanks, I hope you can help me.
Greg

[zbor]

Please read and adhere to these simple rules!

2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.

[Camps and Vans]

Sorry, I have started a new post