Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-04-2019, 02:01 PM
Aklouzek Aklouzek is offline Windows 10 Office 2013
Novice
 
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
  #2  
Old 01-04-2019, 03:01 PM
ProudLiberal's Avatar
ProudLiberal ProudLiberal is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Jun 2017
Posts: 12
ProudLiberal is on a distinguished road
Default

Are S and SC the only prefixes to the serial numbers?
Where are the departments designated?
Reply With Quote
  #3  
Old 01-04-2019, 09:24 PM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 942
xor is a jewel in the roughxor is a jewel in the roughxor is a jewel in the roughxor is a jewel in the rough
Default

Pictures aren't of much help. Better if you uploaded your workbook and gave examples of expected results.
Reply With Quote
  #4  
Old 01-05-2019, 01:45 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 349
ArviLaanemets is on a distinguished road
Default

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
Reply

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 sword.fish Excel 0 02-26-2010 02:09 PM


All times are GMT -7. The time now is 07:28 PM.


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