closing tag is in template navbar
timefactors watches



TZ-UK Fundraiser
Results 1 to 5 of 5

Thread: Help with Excel Problem

  1. #1
    Craftsman
    Join Date
    Feb 2008
    Location
    Scotland
    Posts
    355

    Help with Excel Problem

    Hi

    I am hoping one of the Excel experts on the forum can help me with a solution I need in Excel.

    I have a table of data, sales invoices, which I need to assign line numbers to for each detail line. So the range starts from 1 at every change in invoice number. There are thousands of lines, so not practical to do manually.

    My starting point would look like this:

    A B
    Invoice Number Line
    20004924
    20004924
    20004924
    20004924
    20004925
    20004926
    20004926
    20004926
    20004927
    20004927
    20004927
    20004927
    20004927
    20004927
    20004927


    And I am looking to achieve:

    A B
    Invoice Number Line
    20004924 1
    20004924 2
    20004924 3
    20004924 4
    20004925 1
    20004926 1
    20004926 2
    20004926 3
    20004927 1
    20004927 2
    20004927 3
    20004927 4
    20004927 5
    20004927 6
    20004927 7

    Can anyone advise what function I can use to achieve this and how to do it.

    Many thanks.

    Alan

  2. #2
    Assuming your data starts in cell A2 and your numbering starts in cell B2 you could enter into cell B2 the line "=IF(A2=A1,B1+1,1)", then press return. Finally double click the bottom RHS of cell B2 to fill the B column with the same formula.

  3. #3
    You might also want to take a look at the COUNTIFS function.

    This is a formula I use: "=IF(COUNTIFS($N$2:$N2,N2,S$2:$S2,S2)=1,1,0)"

    Although in my case the formula would come up with these results:

    20004924 1
    20004924 0
    20004924 0
    20004924 0
    20004925 1
    20004926 1
    20004926 0
    20004926 0
    20004927 1
    20004927 0
    20004927 0
    20004927 0
    20004927 0
    20004927 0
    20004927 0

    In my case, Column N contains a high-level product name and Column S contains an order number, and what I am trying to do is count the number of unique order numbers for products placed by customers. Since each product can be made up of a number of options (held in Column O) I only want to count the same high-level product again if it was ordered again using a different order number. I am doing this using COUNTIFS rather than copying the unique occurrences to another location because I am then creating a Pivot Table against the entire dataset and I want to use the number of unique orders within the resulting Output.

    You would need to change the FALSE value from 0 to the value in the cell above +1.

  4. #4
    Quote Originally Posted by Groundrush View Post
    Assuming your data starts in cell A2 and your numbering starts in cell B2 you could enter into cell B2 the line "=IF(A2=A1,B1+1,1)", then press return. Finally double click the bottom RHS of cell B2 to fill the B column with the same formula.
    Much easier!

  5. #5
    Craftsman
    Join Date
    Feb 2008
    Location
    Scotland
    Posts
    355
    Brilliant, worked a treat.

    Many thanks

    Alan

Posting Permissions

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