Find out the same 3-4 symbols in cells and mark cell

[otpisani]

Hello,

I need to find the same 3-4 symbols or signs in 1 or 2 columns (but as one whole string), and mark them as duplicate in another column,
or just mark them with color, or maybe extract that same cells in other column.

example:

A
1 qw34-5-23789

2 zw34-59asdaz

3 zw98a-wrtqyy

I want to extract, mark with color or duplicate that cells (A1, A2) because they have the same "w34-5" symbols in one whole string, not (A3), because he don't contain 3-4 symbols in 1 string just 2 "zw"

Thanks, Robin.

 

[hiker95]

otpisani,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


Sample raw data:

Excel 2007
	A
1	qw34-5-23789
2	zw34-59asdaz
3	zw98a-wrtqyy
4
Sheet1

After the macro:

Excel 2007
	A
1	qw34-5-23789
2	zw34-59asdaz
3	zw98a-wrtqyy
4
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub FindSubstring()
' hiker95, 04/05/2014, ME769248
Dim c As Range
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  If InStr(c, "w34-5") Then
    c.Interior.Color = vbYellow
  End If
Next c
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FindSubstring macro.

 

[hiker95]

otpisani,

I am off on a fishing trip this weekend with my son, and, will be back on Monday, 04/07/2014.

If you need further assistance now, then:

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[otpisani]

Hello hiker95,

BUMP

Thanks for quick replay, but maybe I wasn't clear enough, that "w34-5", don't need to be exactly that string, but also some another randomly string, point is that need be 3-4 symbols in 1 string at once like 12-3, 21-3 or 32-1. Can I replace your code "w34-5", with regex like [A-Z0-9], don't know for special characters.

Thanks, Robin.

 

[pgc01]

Hi Robin

It's not clear to me.

I have some questions

1-
What happens, for ex. in this case

XXABC-1XX
YYABC-1YY
XXABC-2XX
YYABC-2YY
XXABC-3XX
YYABC-4YY

Codes 1 and 2 have ABC-1 common, codes 3 and 4 have ABC-2 common,Codes 5 and 6 have ABC-4 common.

Does this mean that you highlight all? or what do you want to do in this case

2 -
Or do you want to check just those that have the common characters with the first code?



Notice that in the worst case this may mean that you'd want to check all the codes against all the codes, a lot of checking!

Can you clarify?

 

[pgc01]

Other cases

XXABC-1YYABC-2ZZ
WWABC-1ZZ
VVABC-2ZZ

In this case codes 1 and 2 have ABC-1 common, codes 2 and 3 have ABC-2 common.

Also all codes have ABC- common

What to do?

 

[otpisani]

Hello pgc01,

BUMP

Thanks for replay and good question. Shortly on 1 post answer would be case 1, and on second post must agree with you that you are right, like to have option to compare 5 common symbols than, but not necessary don't have problem to compare case/s from your second post. I want to compare mine 2 vendors suppliers that have the same products, they all have internal codes and inside that, ciphers from manufacturer, and that code like to dig out.

Regards, Robin.