Right function inside a Match function - highlight based on criteria

[Brooklyn23]

Hi all,

In a nutshell, I need to apply conditional formatting to a row (or multiple cells within a row if easier) using a match function, but only looking at the last 7 characters of each string (lookup value and lookup array)

I have an ID column in data set 1, and an ID column in data set 2. If any of the ID's in ID column data set 2 appear in ID column data set 1, then I want to highlight in data set 1 the row that the match is found. I can put these data sets on the same sheet, different tabs, or even different workbooks, whatever makes it easier.

Challenges: The ID's could have letters inside, and could have a leading zero, so they must be changed to text. For a given ID match, the leading zero may only be present in one of the instances.

I'm close, but I'm not solving the leading zero problem. I'm having difficulties combining a right function with the lookup array (Inside the MATCH function).

This is the conditional formatting formula that seems close to working:
MATCH(RIGHT($D3,7),$E$6:$E$50,0)
Obviously something needs to also indicate to take the RIGHT,7 for the lookup_array as well. I'm not sure if RIGHT is even working properly when within the MATCH function though.

Please help. Thank you.

[zbor]

If you working with numbers try to convert value into number before matching:

Please Login or Register  to view this content.

[Brooklyn23]

As mentioned, there could be letters or leading zeros, so for all intents and purposes, I'm dealing with text.

[zbor]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[oeldere]

Please take the time and read the forumrules, expecialy the ones about crossposting.

http://www.mrexcel.com/forum/excel-q...el-2007-a.html

[zbor]

Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please in the future include links to any and all cross-posts in any other forums (not just this site).

[Brooklyn23]

Hello, I will be back on tonight and supply all the information asked for. Thank you for helping me.

Linkback URL
http://www.mrexcel.com/forum/excel-q...el-2007-a.html

[Brooklyn23]

Excel problems 2.JPG

Hi all, above is an image of the problem. I thank you in advance.

reminder, crosspost link is http://www.mrexcel.com/forum/excel-q...ml#post4171624

[zbor]

Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping.

[Brooklyn23]

My apologies on earlier etiquette issues. I'm a newbie, but have now consolidated everything to this forum.
I've noted as such and closed out the other forums.

I have attached an Excel spreadsheet which I believe explains the problem very well.

In addition, there is a second part to the problem (tab 2 of spreadsheet with explanation), which I had originally thought was not related.
Creating the conditional formatting to resolve both at once with likely a consolidated IF statement will be the true art here.

[Brooklyn23]

Just some quick background on the 2nd part of the problem. I had originally thought it was the following question:
If value in D is highlighted, then value in E=D; but since this highlighting is due to conditional formatting, the solution will all be within a conditional formatting (I think). Please see a post below which I believe is helpful in tying parts 1 & 2 together once part 1 is resolved:

Posted by Rudi, edited by me to pertain to part 2 of our current problem:

(in order to highlight and perform the part 2 task): You can use the same conditional expression from your conditional formatting within the IF() statement. There is no need to test if the cell is highlighted.

For example:
Assume the conditional formatting is set up like this in the D column to test value in C5 with value in H5 (a second data set):
C/F: C5=H5, shade Yellow

Your IF() function:
In column E, type an IF function: =IF(C1=H1,D1,"")

As you can see, the same C/F condition is just slotted into the IF() functions logical test.

[oeldere]

In the green cells you see if the length of the cells contains 7 characters.

You could filter on the NON blanc cells.

See the attached file.