custom formula in Excel MSOffice 365

[carmonarm]

Hello to everyone and anyone that can bless me with their knowledge,

I am proficient in Excel, but I think I may have hit a brick wall on this one.

I know SQL can do this better but I don't have access to that and or VISIO.

I need a formula that is a combination of SUMPRODUCT and COUNTIF and or whatever other formula I may have missed or am currently ignorant to.

I need to find cells in one column, row by row, that have ANY words that match in the other column.
(only two columns are comparing each other on this spreadsheet)

The data in each column comes from 2 different sources and they are basically different descriptions to medical products.

column "A" is what the buyer is looking for (and they don't use EXACT wording) and column "B" has the actual product description per our catalog system.

As long as even ONE word matches, we're okay with that and consider it a match.

If NONE of the words match then the record can be highlighted using conditional formatting and deleted.

The issue with most standard SUMPRODUCT and or COUNTIF formulas, I must give it a range of words or exact words, numbers, characters, etc. in order for it to properly query.

In this situation, I just want column "A" cell to query/bounce off of column "B" cell and tell us if at least 1 (one) word/term matches, row by row. (each row is a total different product) the entire sheet was already been scrubbed for basic duplicates, anomalies, etc.

I can try to send a snip it sample for reference.

Thank you

 

[FDibbins]

Without some sample data, this is a start with my best guess...

	A​	B​	C​
1​	aa	xx	9​
2​	bb	yy
3​	cc	zz
4​	dd	aa
5​	ee	bb
6​	aa	xx
7​	bb	yy
8​	cc	zz
9​	dd	aa
10​	ee	bb
11​	aa	xx
12​	bb	yy
13​	cc	zz
14​	dd	aa
15​	ee	bb

C1=SUM(COUNTIF(A1:A15,B1:B15))/2
ARRAY entered using CTRL SHIFT ENTER

 

[carmonarm]

Thank you for responding...I'm going to try that, I think I understand it...I can't seem to attached a snap shot of the sampled data, but here's a look typed out...

column A column B column C results
ABRASIVE,DENTAL,PUMICE,FLOUR,2GM CUP ABRASIVE,DENTAL,PUMICE,FLOUR,2GM CUP MATCH
ABUTMENT,FIXED(SPINE) FORCEPS,ENDO,HOT BX,RADIAL JAW 4,2.2MM X 240CM,POLYPECTOMY NO MATCH
ACAPELLA, W/MOUTHPIECE SYSTEM,PEP THERAPY,OSC,W/MOUTHPIECE,VIBRAPEP,OPEP,T-PIECE MATCH
ACCESS CATH, TWIN PASS DUAL CAP/HOOD,SURGEON,UNIV,KAYCEL FABRIC,BLU,UNISEX,DISP NO MATCH

 

[carmonarm]

Sorry that didn't look so good...

column A column B

ABRASIVE,DENTAL,PUMICE,FLOUR,2GM CUP	ABRASIVE,DENTAL,PUMICE,FLOUR,2GM CUP (MATCH)
ABUTMENT,FIXED(SPINE)	FORCEPS,ENDO,HOT BX,RADIAL JAW 4,2.2MM X 240CM,POLYPECTOMY (NO MATCH
ACAPELLA, W/MOUTHPIECE	SYSTEM,PEP THERAPY,OSC,W/MOUTHPIECE,VIBRAPEP,OPEP,T-PIECE (MATCH)
ACCESS CATH, TWIN PASS DUAL	CAP/HOOD,SURGEON,UNIV,KAYCEL FABRIC,BLU,UNISEX,DISP
ACCESSORY,SURGICAL POWER TOOL,HANDPIECE CORD,ELECTRIC,REMB	ACCESSORY,SURGICAL POWER TOOL,HANDPIECE CORD,ELECTRIC,REMB
ACID CHECK SOLN S2030	CABLE,PULSE OXIMETER,LNCS TO GE CABLE,L 10FT

<colgroup><col><col></colgroup><tbody>
</tbody>

I tried your formula, I still didn't get it to work...keep in min I"m comparing cell to cell in one row for only 2 columns, not ht range, I only want to compare each cell in each column for each row and then copy the formula all the way down...

 

[FDibbins]

aakhh OK, so you need to test A1 with B1, A2 wit B2 etc, NOT A1 with the whole range of B?


What do you want to happen when a match is found? I would probably do this with a helper column if I wanted to count the matches (=--A1=B1), or with Conditional Formatting if I just wanted to highlight the matches