VBA - to find the highest letter, mix with letters and numbers.

**TAllan** · 02-23-2021, 05:33 PM

Hello

I have tried to figure out how to do this but I can not manage myself.

When sales staff enter Quote in the system (SAP) we get an number example 1008307-A, if we do a change or an update to the same quote we get 1008307-B, same numbers but -B instead of A, every time they update the quote we get a new letter and when I try to run a report, I get them all in Excel but I would only like to see the "highest" Letter of the Quote.

I would have this in column E for example

1008382-A
1008382-B
1008382-C
1008485-A
1008485-B
1008623-A
1008623-B
1008951-A
1008951-B

SO I would just like to show the in bold.

**MarvinP** · 02-23-2021, 05:54 PM

Hi TAllan and welcome to the forum,

Here is an answer without needing VBA. It uses two helper columns and a Pivot table to get to your answer.
First, extract the number, without the letter to a new column. Then sort the data from Z to A. Now add the CountIf() formula helper column.
I finish off by creating a Pivot Table where the count is the columns and only shows the first (last letter added) orders. See it in the attached file.
Print Last Order letter added.xlsx

**TAllan** · 02-24-2021, 01:52 PM

HI MarvinP

Sorry but it did not work so good with a pivot table and I might not been so clear with my question. Sorry about that.

I attached an example of my Excel sheet and the numbers in RED is the once I would like to just keep the "highest" letter, for each one.

**JeteMc** · 02-27-2021, 05:36 PM

This proposal employs three helper columns and a pivot table.
The first helper column is populated using: =IF(ISNUMBER(LEFT(F3,1)+0),LEFT(F3,SEARCH("-",F3)-1)+0)
The second helper column is populated using: =IF(ISNUMBER(J3),SUM(J3*100,CODE(RIGHT(F3,1))+0))
The third helper column is populated using: =K3=AGGREGATE(14,6,K$3:K$256/(J$3:J$256=J3),1)
If this doesn't help then please explain why as well as providing some manually calculated results with which we may compare our proposed solutions.
Let us know if you have any questions.

**jindon** · 02-28-2021, 01:02 AM

Originally Posted by TAllan

When sales staff enter Quote in the system (SAP) we get an number example 1008307-A, if we do a change or an update to the same quote we get 1008307-B, same numbers but -B instead of A, every time they update the quote we get a new letter and when I try to run a report, I get them all in Excel but I would only like to see the "highest" Letter of the Quote.

VBA

Please Login or Register  to view this content.

**Bo_Ry** · 02-28-2021, 06:36 AM

Please try

Please Login or Register  to view this content.

I change font color to white
r.Font.ColorIndex = 2
you may change it as need.

**TAllan** · 03-02-2021, 03:58 PM

Thanks for all the help. It works perfect.

Now I will try to figure out how to delete the ones I don't want to see.

VBA - to find the highest letter, mix with letters and numbers.

LinkBack

Thread Tools

Rate This Thread

Display

VBA - to find the highest letter, mix with letters and numbers.

Re: VBA - to find the highest letter, mix with letters and numbers.

Re: VBA - to find the highest letter, mix with letters and numbers.

Re: VBA - to find the highest letter, mix with letters and numbers.

Re: VBA - to find the highest letter, mix with letters and numbers.

Re: VBA - to find the highest letter, mix with letters and numbers.

Re: VBA - to find the highest letter, mix with letters and numbers.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Can I find the lowest letter in a range of letters

MAX Function to obtain highest value of numbers with a letter prefix

[SOLVED] Find in each letter of the corresponding 8 letters in each group

Incrementing Letter / Numbers / Letters in a single cell

[SOLVED] Highest Value in Row, Including Letters and Numbers

Extracting Key words out of a cell with numbers, letters and letter and numbers together

Get the highest value when all values consist of letters AND numbers

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions