Help assigning a value based on another column

Question

Hello,

I am trying to find a quick way to assign a value for a column depending of the value of another. This picture is an example of what I am trying to do on a small scale. I need to know if there is a formula that I can use to sort through 27,000 lines of data rather than assigning them one by one.

I am using the phone number as a unique identifier and I want the ID# to increase by one every time the phone number changes.

Please Help!!

Answer 1

You can also use the if statement: IF(B3=B2,A2,A2+1).

You set the first cell value to 1. If the value of the second cell if the second column is equal to value of the first cell in the second column, then you pick the preview cell in the first column. Once you have the formula and double click on it to repeat for all the cells, you go at the end of the last cell and fix it value.

That will do it.

Below is an example of the output

1	fjs
2	fdds
3	dss
3	dss
4	pl
4	pl
5	m
6	kdd
6	kdd
6	kdd
7	lows
7	lows
7	lows
7	lows
8	qasd
9	cdsd
9	cdsd
9	cdsd
9	cdsd
10	fff
10	fff
10	fff
11	pl
12	a

Answer 2

There is a SUMPRODUCT() solution to this but I'm not sure how that is going to slow down your computer with 27K rows.

Use the following in A2,

=SUMPRODUCT(1/COUNTIF(B$2:B2,B$2:B2&""))

Fill of copy down as necessary.

That formula assumes that column B is sorted and there are no duplicates out of order. If there is a chance of that then a more complicated solution would have to be provided.

Answer 3

Hi,

Try this in cell B2 and copy down

=SUMPRODUCT(1/COUNTIF(B$2:B2,B$2:B2))

Hope this helps.