Help with Excel Problem

[weedram]

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

[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.

[willie_gunn]

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.

[willie_gunn]

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!

[weedram]

Brilliant, worked a treat.

Many thanks

Alan