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

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.

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.

Quote:

---

Originally Posted by Groundrush

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!

Brilliant, worked a treat.

Many thanks

Alan