+ Reply to Thread
Results 1 to 20 of 20

How to assign a number for item tracking?

  1. #1
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    180

    How to assign a number for item tracking?

    Hi guys.

    I have a list of items, in this example the partiuclar item is #10. Keeping track of purchases and items already shipped. I would like to assign a number as an identifier.

    In column B, has a list of times the item was ordered. I would like to assign each order with the numbering scheme in column J, because there is no limit to the number of purchases and digits only go up to 9. I would like the number 8 to be assigned when additional identifiers are needed. Is there a formula to assign these numbers?
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,074

    Re: How to assign a number for item tracking?

    What are you going to do when you have more than 20 orders within the same window - you run out of numbers (1089)

  3. #3
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    180

    Re: How to assign a number for item tracking?

    It would continue down to 5 if needed. Though realistically that will not happen, which is why I chose 8 in this sample workbook just to distinguish. I have other identifiers for the items as well. I was just seeking assistance with the numericals.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    28,735

    Re: How to assign a number for item tracking?

    In your real sheet, are the times in column B actually times, or (as in your sample) text that looks like a time?
    Glenn



  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    28,735

    Re: How to assign a number for item tracking?

    No reply... So I have assumed that it is TEXT that looks like a time, as per sample sheet.

    One way, with a helper column:

    =VLOOKUP(C$4:C4,$H$2:$I$4,2,TRUE)

    The Serial No is then generated using:

    =$E$2&IF(COUNTIF(D$4:D4,D4)-1>9,8,D4)&MOD(COUNTIF(D$4:D4,D4)-1,10)
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    180

    Re: How to assign a number for item tracking?

    Hi Glenn - I was away from the computer for a little over an hour. Sorry I didn't see your posts. The times are actual times formatted as [h]:mm.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    28,735

    Re: How to assign a number for item tracking?

    Hi. It doesn't make any difference to the formula... just on the need to make a real time out of text.

    Next time, please ensure that you post representative data.... times, not text posing as a time!!
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    180

    Re: How to assign a number for item tracking?

    Understood and thank you. Will work with some items and see how it works out.

  9. #9
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    180

    Re: How to assign a number for item tracking?

    I had an idea about something, the easiest way I could describe it is by using airplane flights as an example. Is there a way to have add a criteria if an item is in the same time frame but different category?
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,501

    Re: How to assign a number for item tracking?

    Try pasting the following into cell F4 and then dragging the fill handle down to cell F7:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  11. #11
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    180

    Re: How to assign a number for item tracking?

    Hello, is there a way to combine the formulas in column E and F so it actually assigns the unique value when counting?
    Attached Files Attached Files

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,501

    Re: How to assign a number for item tracking?

    This doesn't combine E:F however if the following is pasted into cell G4 and then copied down it does return the expected results:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  13. #13
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    180

    Re: How to assign a number for item tracking?

    Is there a way to modify the "Saturday" formula, so the formula yields the results manually entered in column A?
    Attached Files Attached Files
    Last edited by Dexter2; 02-22-2021 at 06:49 PM.

  14. #14
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,501

    Re: How to assign a number for item tracking?

    This proposal employs a helper column (G) which may be moved and/or hidden for aesthetic purposes.
    Paste the following into cell G5 and double click the fill handle to copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Paste the following into cell B5 and double click the fill handle to copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  15. #15
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    180

    Re: How to assign a number for item tracking?

    =IF(OR(MID(F5,3,1)+0=9,COUNTIFS(F$5:F5,F5)>10),10&M$7,SUM(--F5,COUNTIFS(F$5:F5,F5)-1)) I changed it to $B$1 so it changes automatically when I change the flight/item.

    =IF(ISNUMBER(G5),G5,IF(COUNTIFS(G$5:G5,G5)-1<=9,SUM(G5&COUNTIFS(G$5:G5,G5)-1,0),SUM(1000,COUNTIFS(G$5:G5,G5)-11)))

    Is there a way to have it display like $B$1"00"?

    Also. I forgot to mention, it seems to be funky for afternoon times, though all of the formulas are consistent if you have a three digit number.
    Attached Files Attached Files
    Last edited by Dexter2; 02-23-2021 at 03:06 PM.

  16. #16
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,501

    Re: How to assign a number for item tracking?

    For B5 and down try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I don't understand what the last statement means, perhaps an example would provide better clarification.
    Let us know if you have any questions.

  17. #17
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    180

    Re: How to assign a number for item tracking?

    That solved it! Thanks.

    The example for the last statement is on the second sheet in the above post workbook.

  18. #18
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,501

    Re: How to assign a number for item tracking?

    Try the following in cells G5 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The formula from post #16 seems to then produce the correct sequence in column B, although I don't what value should occupy cell B33.
    Let us know if you have any questions.

  19. #19
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    180

    Re: How to assign a number for item tracking?

    Saturday is complete!

    For Weekday, is there a way to tweak the formulas for just Weekday so the sequence continues to 1020 and then 1048 as shown?
    Attached Files Attached Files

  20. #20
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,501

    Re: How to assign a number for item tracking?

    Not exactly the values listed in post #19, however see if the following pasted into cell G5 and copied down, produces acceptable results:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula to assign an Item number for values in another column
    By Motox in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-22-2019, 12:26 AM
  2. Form for tracking item updates
    By fbiasi in forum Excel General
    Replies: 1
    Last Post: 06-29-2016, 10:20 AM
  3. [SOLVED] Item Database: Automatically assign number to items(store products)
    By AEvans190 in forum Excel General
    Replies: 18
    Last Post: 10-22-2013, 04:27 PM
  4. [SOLVED] How to use VBA to assign a reference number for each item?
    By jasonlewis in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 07-25-2013, 02:42 AM
  5. [SOLVED] Creating a Tracking Item Spreadsheet by Customer
    By DanielRay in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-28-2013, 07:23 PM
  6. Excel 2008 : Assign Vendor to Item Number
    By DADADO in forum Excel General
    Replies: 2
    Last Post: 07-21-2012, 03:04 AM
  7. [SOLVED] VBA code help for item tracking.
    By russell.estes@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-24-2005, 11:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1