Excel Getting Line Count per instance of a record 1

[MaffewW]

Hi Folks

I am trying to sort out some data to load into our database server, I have tried a SQL solution but am struggling, wonder if anyone can help with Excel?

We have an excel sheet containing multiple records per customer, one for each phone number.

it looks something like this:

AccountNumber Phonenumber
000123 01273153456
000123 01273153457
000123 01273153458
000756 01546123456
000756 01546123457

What I need to try and do is add a column to give the instance of each accountnumber ie

InstanceNumber AccountNumber Phonenumber
1 000123 01273153456
2 000123 01273153457
3 000123 01273153458
1 000756 01546123456
2 000756 01546123457

can this be done?

all the best

Matt

Brighton, UK

 

[xlbo]

very easily !!
in a new column - lets say you insert a new col A so that your Account Numbers are in col B
Then, in A2 (assuming header in A1) enter

=if(A2<>A1,1,A1+1)

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[xlbo]

Sorry - should point out that this will only work if the data is sorted by account number

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[gizmo1973]

You could try =FREQUENCY(a4:a11,b4) but this will display as 3,2,1 not 1,2,3
Asuuming your data is in column A and your new column is B

Regards, Phil.

Full Member of Shareholders United.
Show your true support here:

http://www.shareholdersunited.org/join.php

"Shares not Shirts

 

[MaffewW]

thanks guys howver I cant get ever method to work. Apologies Geoff, I have noticed that some of the account number are non numeric, if I cant get the frequency function to work I will remove the letters and reappend them though thats gonna get messy. Theres approx 13k records

If I type
=FREQUENCY(A2:A12988,B2)
into B2 I get a complaint about circular reference.


A B
CC00005012 =FREQUENCY(A2:A12988,B2)
CC00005013
CC00005014
CC00005016
CC00005022
CC00005031
CC00005033
CC00005037
CC00005040
CC00005040
CC00005040

cheers


Matt

Brighton, UK

 

[xlbo]

Matt - numeric or not - shouldn't matter - all the formula is doing is checking for a CHANGE in account number. At that point, it sets the record count to 1. After that, it just increments by 1. As I said though, the data would need to be sorted by account number. What column are your account numbers in and what row do they start in ??

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[KenWright]

Geoff's method should do it for with you with a slight tweak.

Assuming your data was all in Col A in say A1:A1000, in B1 put the following and copy down:-

=COUNTIF($A$1:A1,A1)

This simply creates a different range in each formula so the formula only takes into account the data that has gone before, and each instance of your data will then be incremented correctly.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[xlbo]

hmmm - was thinking of posting something similar Ken but a COUNTIF() on ~13k records is gonna take a while to calc

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[MaffewW]

thanks chaps. Geoff, i couldnt get your initial method to quite work, though the countif worked.

It did kill my machine for about 50 seconds though!

Incidentally I had someone come back with a SQL Solution, which I will probably use as the spreadsheet is already in a table, though I thought I was going to need to start again

http://www.tek-tips.com/viewthread.cfm?qid=960238

here if anyone uses SQL

cheers

Matt

Brighton, UK

 

[xlbo]

D'OH - just noticed my mistake (which I'm sure Ken alluded to earlier)
Tweak to the formula gives:
=if(B2<>B1,1,A1+1)

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[KenWright]

LOL - Given that it a was one shot deal just to create the upload, I figured a minute or so of calc time wasn't going to kill you

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------