0

here is the best way I can describe my problem I am having, I tried a few things and I can't seem to hit the nail on the head of what exactly I want. Here are my columns. The asset in column B is comprised of column F and G

A: Location

B: Asset in one system

F: Asset in another system

G: Asset in another system

For example, what is in Column B is 132-10943 and what is in Column F is 132 and what is in Column G is 10943.

Does that make sense? Column B merges both F and G together in one cell. Now I want to be able to highlight duplicate values based on what is in Column G (the 10943) so when I use conditional formatting, the cell with 10943 will be highlighted and so forth. Column G is the unique number in the series, but I do not know how to write it. Logically, I can see it, but I cannot transmit it to paper, which thankfully I am not a programmer, I am just trying to save me hours of work.

Thanks for the help!

Edit: I should add that using the default Duplicate Value conditional formatting won't work because the B and G cells have different values in it, but cell B will always contain a number that is from cell G, so how do I modify that?

2
  • Does column B contains a formula that combines F and G, or is it data that has been entered? May 11, 2015 at 22:37
  • No, that number has just been manually entered over a few years and now I need to actually compare the two.
    – David
    May 12, 2015 at 15:09

1 Answer 1

0

There are several approaches that could work. Decomposing the value in column B into its component parts would let you perform comparisons against columns F and G. Shifting the conditional formatting to column B would allow you to set up conditions for (no match, match F, match G, match both F and G).

Picking new columns and adding the formulas:

=LEFT(B2,SEARCH("-",B2)-1)

and

=RIGHT(B2,LEN(B2)-SEARCH("-",B2))

will split column B into two parts separated by the first hyphen provided that there is a hyphen.

Similarly a conditional format for column B could use these formula fragments to check for the number of matches in columns F and G.

2
  • Look at that, I learned something new in Excel. This does work perfectly, but out of curiosity, is there a way to do an IF statement for this, such as If column B has value that matches column I then = true (highlight)? I appreciate the help, that was exactly what I was looking for.
    – David
    May 13, 2015 at 4:07
  • Are you looking for a conditional formatting formula, and if so which cell would you like to have highlighted? Can you clarify which value is in column I, is it the formula that is extracting the right hand side of column B? May 13, 2015 at 4:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .