+ Reply to Thread
Results 1 to 34 of 34

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
    505

    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
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    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
    505

    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
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    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




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    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
    505

    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
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    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
    505

    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
    505

    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 2019
    Posts
    17,425

    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
    505

    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 2019
    Posts
    17,425

    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
    505

    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 2019
    Posts
    17,425

    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
    505

    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 2019
    Posts
    17,425

    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
    505

    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 2019
    Posts
    17,425

    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
    505

    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 2019
    Posts
    17,425

    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.

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

    Re: How to assign a number for item tracking?

    In order to make the cycle continue on, could the formula be reverted through a LOOKUP function, that it would continue to 1020 instead of 1026? Then for the second one because 1040 is already in use, revert the opposite way to the cycle continues from the 1047?

  22. #22
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: How to assign a number for item tracking?

    I don't understand the question.
    Could you please manually show us the results that you would like the formula to produce?

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

    Re: How to assign a number for item tracking?

    Flight # Start End Duration
    1020 350a 1022a 6h32
    1021 420a 1052a 6h32
    1010 450a 142p 8h52
    1022 505a 1202p 6h57
    1011 520a 152p 8h32
    1012 535a 209p 8h34
    1023 550a 1222p 6h32
    1013 610a 326p 9h16
    1014 620a 305p 8h45
    1015 640a 347p 9h07
    1016 700a 419p 9h19
    1017 740a 440p 9h00
    1024 950a 251p 5h01
    1018 1010a 713p 9h03
    1019 1040a 748p 9h08
    1025 1100a 333p 4h33
    1020 1090 1150a 902p 9h12
    1030 1204p 447p 4h43
    1031 1211p 706p 6h55
    1032 1232p 727p 6h55
    1040 1246p 1152p 11h06
    1041 114p 1207a 10h53
    1042 128p 1016p 8h48
    1033 135p 621p 4h46
    1034 142p 635p 4h53
    1043 156p 1040p 8h44
    1044 238p 102a 10h24
    1045 252p 1137p 8h45
    1035 259p 946p 6h47
    1036 320p 802p 4h42
    1037 334p 814p 4h40
    1046 355p 1222a 8h27
    1038 409p 838p 4h29
    1039 430p 703p 2h33
    1048 1091 437p 1107p 6h30
    1047 458p 132a 8h34

    Being a number is still needed for 1150a-902p and the duration is greater than 8 hours, I would need the cycle to continue to it would be assigned 1020 (from 1019) instead of the 1090.

    Similar to the above sentence, but different for the next piece. 437p - 1107p being it can not be assigned 1040 after 1039, 1040 is already being used. I would need it to be picked up from the other cycle, so it would become 1048, after 1047.

  24. #24
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: How to assign a number for item tracking?

    Comparing the values in post #23 to those in the attached file, which is from post #19 with the formula from post #20 applied:
    1. Flight #1020 is already used in cell B5.
    2. I don't understand how the values of 1090 and 1091 were placed in rows 21 and 39, so I can not offer help on how to replace them.

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

    Re: How to assign a number for item tracking?

    I applied this to actual data and it worked for all except 1 flight. So, thank you.

    I would need to add two more option of selections for Weekday highlighted in Green.

    This example may give a better visual, if its helpful.

    1) 44366 should be 44340, because it is greater than 7 hours, the last number used for that category is the 44339.

    2) 44387 and 44388 should be swapped because the current 44388 takes precedent being its greater than 7 hours and not all of 80's have been used yet. However, the current 44387 technically should be 44380, but that is already in use for its proper category. Which then the next option would be the 44388.

    Now if there were more flights added to this list, the next numbers assigned would be used is the 90's (44390-44399) regardless of the flight duration. Which now you might ask, well why wasn't 44366 simply renumbered to 44390? In this particular case, its because the flight is before 12 Noon.
    Attached Files Attached Files

  26. #26
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: How to assign a number for item tracking?

    As close as I have come is the following which would be pasted into cell B5 and copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In the attached file the formula is placed in column R so that the results can be compared to the values in column B.
    Let us know if you have any questions.

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

    Re: How to assign a number for item tracking?

    Thank you very much. That was very helpful, it corrected 2 out of the 3 misrepresentations. 44387 became 44390 and not 44388.

    Currently, I am working to figure out the 0 for greater then 14 hours. I changed the time for the first input 44360 from 1022a to 650p for it to change to 44301 instead of the 44330.

  28. #28
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: How to assign a number for item tracking?

    I had no luck getting cell B39 to display 44388, so I am in hopes that 44390 will work instead.
    It seems to me that since row 5 is the first flight of over 14 hours, that it would be 44300, which can be accomplished by making the following modification to the formula in F5 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

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

    Re: How to assign a number for item tracking?

    No worries, I appreciate you.

    Moving on to Saturdays, can this be yielded?

  30. #30
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: How to assign a number for item tracking?

    The following modification to the formula in cells F5 and down produces unique results in column B, and seems to follow the logic used for assigning flight #'s to weekdays and Sundays:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

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

    Re: How to assign a number for item tracking?

    Thank you, the PM is correct for Saturday. But for some reason, the AM portion doesn't change to the 44390-44396 instead of 44320-44326, as it picks up correctly later with the 44397.

    Also, I had a thought. Would a subtraction problem work to change the 44400 to 44300?

  32. #32
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: How to assign a number for item tracking?

    I was under the impression that Saturday AM flights of under 7 hrs. should be 4439... and if over 7 hrs. then 4431... similar to the cases for weekday and Sunday flights.
    Here is a copy of the file with the formula applied.
    Let us know if you have any questions.

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

    Re: How to assign a number for item tracking?

    Ah I see, no problem. Weekday, Saturday, and Sunday are all separate, that is why when building the formula I keep getting confused. However, though Saturday and Sunday it doesn't matter about the duration. For Saturday, it should be 44310-44319 then 44390-44396, then all of the PM's as how you already have it.

  34. #34
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: How to assign a number for item tracking?

    Replace the formula in cell F5 with the following and then 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.

+ 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. VBA code help for item tracking.
    By [email protected] 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