Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

LinkBack Thread Tools Display Modes
Old 01-04-2019, 02:01 PM
Aklouzek Aklouzek is offline Windows 10 Office 2013
Join Date: Jan 2019
Posts: 1
Aklouzek is on a distinguished road
Default Excel Formula for Finding the Last "S" number used

Good Afternoon!

I am trying to pull information from several tabs onto a cover sheet. The information i am trying to pull is the last or highest "S" which is a serial number that has both Letters and Numbers in it. Can anyone help to point me in the right direction? I have attached a couple snips to give you an idea of what i am looking to try and do. I need to be able to see what the last/highest "S" or serial number was used for each department at a glance, pulling the information from different tabs to the cover page that I am building.
Attached Images
File Type: jpg Cover Page.JPG (46.7 KB, 12 views)
File Type: jpg Serial Number.JPG (86.3 KB, 10 views)
Reply With Quote
Old 01-04-2019, 03:01 PM
ProudLiberal's Avatar
ProudLiberal ProudLiberal is offline Windows 7 64bit Office 2010 64bit
Join Date: Jun 2017
Posts: 14
ProudLiberal is on a distinguished road

Are S and SC the only prefixes to the serial numbers?
Where are the departments designated?
Reply With Quote
Old 01-04-2019, 09:24 PM
xor xor is offline Windows 10 Office 2016
Join Date: Oct 2015
Posts: 1,019
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light

Pictures aren't of much help. Better if you uploaded your workbook and gave examples of expected results.
Reply With Quote
Old 01-05-2019, 01:45 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Join Date: May 2017
Posts: 446
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough

The easiest way is to have a couple of helper columns as rightmost ones in your table (I see you have defined your table as Table, so formulas will expand automatically whenever you add new entries). Later it will be best you hide those helper columns. And in case Table formulas are used, this will be easier when you don't use spaces in column names.

To continue from here, some additional info is needed:

1. What do you mean with 'last S' ? Is it in your example [No.] = S1459, or [No.] = SC1532? I.e. are you grouping data by 1st character, or by string part of [No.];
2. With 'last' you mean lowest position for given group, or biggest numeric part after string part (when later, then you get same result with any sorting order in applied to your Table);
3. How many characters can string part of [No.] maximally have.
Reply With Quote

Thread Tools
Display Modes

Similar Threads
Thread Thread Starter Forum Replies Last Post
Spreadsheet with "CR" & "DB" in amount & number columns. bfisher Excel 4 09-28-2015 07:03 AM
Finding more than one word using "find" or using a vba code capitala Word VBA 1 02-03-2014 06:45 PM
Mailing: how to make the "page number" in Word is the same as "row number" in excel w Jamal NUMAN Word 1 09-03-2011 11:37 AM
Excel 07 formating 17 cells "Need Formula" Raner Excel 2 05-30-2010 02:07 PM
"Microsoft Excel Application" missing in the "Component Services" on win08 Excel 0 02-26-2010 02:09 PM

All times are GMT -7. The time now is 08:37 PM.

Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc. is not affiliated with Microsoft