+ Reply to Thread
Results 1 to 11 of 11

Line Item Sequence No

  1. #1
    Registered User
    Join Date
    03-03-2012
    Location
    colombo
    MS-Off Ver
    Excel 2003
    Posts
    31

    Line Item Sequence No

    Hi Guys,

    One of the document i have received it should put a line item sequence Number(Please check the attachment) on each line item as a 10,20,30 etc...by now i entered it manually.please tell me if there have any formula how to do that. because i have more than one thousand data has to put a line sequence numbers in daily basis.

    Thank you
    dan
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Line Item Sequence No

    As long as the sheet is sorted so that all same invoice numbers are together (even if not in order).
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If they could be mixed, then
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Which over thousands of rows will be much slower to calculate.

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,038

    Re: Line Item Sequence No

    in C3
    =COUNTIF(B$3:B3,B3)*10
    and copy down
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,332

    Re: Line Item Sequence No

    c3=COUNTIF(B3:B$3,B3)*10
    Please Login or Register  to view this content.
    Try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  5. #5
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,038

    Re: Line Item Sequence No

    Quote Originally Posted by nflsales View Post
    c3=COUNTIF(B3:B$3,B3)*10
    Please Login or Register  to view this content.
    Try this and copy towards down
    Shouldn't that be B$3:B3 (I've not seen it written that way before) ?

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Line Item Sequence No

    Quote Originally Posted by Special-K View Post
    Shouldn't that be B$3:B3 (I've not seen it written that way before) ?
    No different to using Z100:A1 in a formula, excel will 'correct' it without errors.

  7. #7
    Registered User
    Join Date
    03-03-2012
    Location
    colombo
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Line Item Sequence No

    Hi guys

    It doesn't work for me.when i tried in my excel it gave me result as #NAME?
    Please help me.

    Thank you
    Dan

  8. #8
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Line Item Sequence No

    Did you try the alternative that I suggested as well?

    Which language is your PC set up for?

    The suggestions provided might need translating if not English.

  9. #9
    Registered User
    Join Date
    03-03-2012
    Location
    colombo
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Line Item Sequence No

    Hi Jason,

    Actually C3 column is the result column that i want.it wasn't not in the original sheet.i really want sequence number for the one invoice line.it like this

    15-10-00001 = 10
    15-10-00001 = 20
    15-10-00002 = 10 (This should comes again 10 Number Format)
    15-10-00002 = 20
    15-10-00002 = 30
    15-10-00002 = 40
    15-10-00003 = 10

    Like this.
    i thing you are clear about what i mean sir,

    Thank you,
    Dan

  10. #10
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Line Item Sequence No

    In your sample file, the formula goes in C3.

    =IF(B3=B2,C2+10,10)

    B3 refers to the invoice number in the same row, B2 refers to the heading, "Ref No" and C2 refers to the heading "S_ORD_ITEM".

    As you copy it down, everything moves with the formula so the first part always looks at the same row as the formula, the second and third parts look 1 row above the formula.

  11. #11
    Registered User
    Join Date
    03-03-2012
    Location
    colombo
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Line Item Sequence No

    Thanks Jason it works for me.

+ 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. [SOLVED] Formula needed - line item of unique item matching criteria
    By NS4Excel in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 04-18-2015, 06:32 PM
  2. How to sort the data in correct sequence of date and item code?
    By Zecond in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-30-2013, 09:16 PM
  3. Add Sequence as a calculated field or item in pivot table
    By roymunoz03 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 06-04-2013, 03:09 AM
  4. Line Item Formula
    By bbarry404 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-29-2013, 10:40 AM
  5. Combining multiple line items into one line item based on column.
    By mguz018 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-20-2012, 07:22 PM
  6. Generate each item "n" times in sequence
    By Macemann in forum Excel General
    Replies: 2
    Last Post: 10-08-2010, 08:06 PM
  7. line item extractor
    By bholabhala in forum Excel General
    Replies: 29
    Last Post: 09-18-2010, 11:59 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