Excel formatting formula help

Hi Everyone!

excelforumsample.xlsx

I have a data set that I need help with. My data set has multiple iterations of the same number.

As you'll find in the attachment for example:

BA19, BA19-01, BA19-02 are all the same but I only need the latest iteration, in this case BA19-02.

Ideally, I'd like a formula that just removes the other iterations (BA19 and BA19-01), but highlighting them works for now. Basically, if BA19 has BA19-01 check if there's is BA19-02 check if there is BA19-03, if false, else highlight BA19-02. Keep in mind not all items start with BA. I have over 7000 unique items.

Some items won't have suffixes, so I need the formula to leaves the ones that don't be left alone. Also, the iterations sometimes go up to a -07.

Is there a way to do this? Have a done a good job at explaining this? What else can I provide?

Thank you,
Wilson

ChemistB,

Thank you for the quick response. It worked just as you said. The only thing I was hoping it would do is only highlight the biggest iteration. For example: sometimes I have -01, -02,...-07.
Your formula does this:
a
a-01
a-02
a-03
a-04
a-05

I would like only:
a
a-01
a-02
a-03
a-04
a-05

Is this possible?

Almost ChemistB, man I really need to learn this so I don't bug you so much.

Only thing that's missing is it needs to grab all the items with the last iteration.

In this it did highlight the last iteration, but only one instance.

Example:

a-05
a-05
a-05
a-05

It needs to:

a-04
a-05
a-05
a-05

Thanks again ChemistB!

popipipo,

Thank you. This doesn't work for me. If you look in my file that I shared, you might get a better idea. I don't know if there's a better way to explain it.

ChemistB is pretty close to cracking the formula. The formula by ChemistB highlighted the biggest iteration, but only highlighted one instance, I need it to highlight all the biggest iteration of the items.

BA19
BA19-01
BA19-01
BA19-02
BA19-02
BA19-03
BA19-03
BA19-04
...
BA19-07
BA19-07
BA19-07
BA19-07

I know I can formatted it myself, I'm just trying to make it obvious.

They are...

Right I'm up to -07, but I imagine I will continue to have iterations. Like -08,-09...etc...

ChemistB,

Can you run that formula on column B of excelforumsample.xlsx to see if you're getting the same issue I am? Your example is exactly what I need, but it doesn't work with the real data.

I even went ahead and substituted column A in HighlightMultiplees.xlsx with excelforumsample.xlsx and I get the same issue.

Thank you,

Originally Posted by ChemistB

Here it is. I put the formula in Column T (again, you can hide that column). When you tried it, did you enter it with CNTRL SHFT ENTER? If you can't get it working, upload one that's not working and I'll tell you what's wrong.

ChemistB,

Thank you once again, you've spend some serious time doing this and it's helped my process of analyzing my data so much simpler. I left you a reputation comment.

I was wondering if you know of any resources that you can send over to me so I can learn the basics of formulas like this. I like posting here, but sometimes it's nice knowing more than just vlookup. hahaha.

ChemistB,

It worked well on the sample data but when I paste it to the full data set it doesn't catch the last item in the set of items specified. Could this be because I have over 1000 items. Total equals to 36,000+ rows.

Also I don't know if it could be set to look up 50 rows and down 50 rows, due to the fact that there are so many rows it's taking a while to calculate.

Thank you,

EXCELFORUM_11262013.xlsx

I've only attached 100 items, again there are over 36K lines, if that makes any difference.