Ok I am looking for a little assistance in figuring out a little riddle for Excel.

[leeland]

Ok I am looking for a little assistance in figuring out a little riddle for Excel.

I have a co-worker who brought me a spreadsheet with 6000 entries in it...in this sort of format

Drug Name                                        Drug Code
Drug1                                           00001
Drug2                                           11111
Drug2                                           11112
Drug3                                           13131
Drug3                                           12121
Drug3                                           12122
Drug4                                           55555

Now the point of the above is this...

The Drug name can be the same but it can have a variety of codes.

I need to figure out a way to create a third row...that gives them a sequence number.

Drug Name Drug Code DrugSeq
Drug1 00001 1
Drug2 11111 1
Drug2 11112 2
Drug3 13131 1
Drug3 12121 2
Drug3 12122 3
Drug4 55555 1



So that I can upload the excel spreadsheet into SQL or Excell and move all the duplicate rows up on the same line something like this


Drug Name DrugCode1 DrugCode2 DrugCode3 DrugCode4


Any help with this would be greatly apprecaited


Thanks in advance,


Leeland

 

[pontifex]

Text

 

[QED]

If drug name is in the A column, and your sequence code is in Column C, then use something like this for your sequence column:

=IF(A3 = A2, C2 + 1, 1)

 

[rhatsaruck]

Learn how to use PivotTables

 

[leeland]

Originally posted by: MathMan

If drug name is in the A column, and your sequence code is in Column C, then use something like this for your sequence column:

=IF(A3 = A2, C2 + 1, 1)

That doesn't seem to work...I am getting a circular reference error...otherwise it looks like it would work from a logical standpoint.

Any way around the circular reference ?

 

[QED]

Originally posted by: leeland

Originally posted by: MathMan

If drug name is in the A column, and your sequence code is in Column C, then use something like this for your sequence column:

=IF(A3 = A2, C2 + 1, 1)

That doesn't seem to work...I am getting a circular reference error...otherwise it looks like it would work from a logical standpoint.

Any way around the circular reference ?

It is working fine here... for the next few rows, did you update the row references?


Column C3 is "= IF(A3 = A2, C2 + 1, 1)"
Column C4 is "= IF(A4 = A3, C3 + 1, 1)"
Column C5 is "= IF(A5 = A4, C4 + 1, 1)"


Once you have 3 rows filled out with the pattern, you should be able to highlight C3,C4,C5 and extend the pattern for all of your rows...

 

[Garion]

Originally posted by: leeland

Originally posted by: MathMan

If drug name is in the A column, and your sequence code is in Column C, then use something like this for your sequence column:

=IF(A3 = A2, C2 + 1, 1)

That doesn't seem to work...I am getting a circular reference error...otherwise it looks like it would work from a logical standpoint.

Any way around the circular reference ?

I think you've inverted a couple of the rows. Assuming you've started your data in row A, try this:

In C2, put the following: =IF(A1 = A2, C1 + 1, 1)

In C1, put a number 1. This is the "Seed" for the forumla to start it off - Your first sequence # for the first row is always going to be 1.

Copy the formula down and it should do what you want.

- G

 

[leeland]

Originally posted by: Garion

Originally posted by: leeland

Originally posted by: MathMan

If drug name is in the A column, and your sequence code is in Column C, then use something like this for your sequence column:

=IF(A3 = A2, C2 + 1, 1)

That doesn't seem to work...I am getting a circular reference error...otherwise it looks like it would work from a logical standpoint.

Any way around the circular reference ?

I think you've inverted a couple of the rows. Assuming you've started your data in row A, try this:

In C2, put the following: =IF(A1 = A2, C1 + 1, 1)

In C1, put a number 1. This is the "Seed" for the forumla to start it off - Your first sequence # for the first row is always going to be 1.

Copy the formula down and it should do what you want.

- G

You are exactly right...I figured this out shortly after I wrote my first reply...I did have it in the wrong cell...I had column headers which messed it up...

it works perfect and is going to be what I need to upload it into SQL to merge the two spreadsheets into one table...

Thanks so much guys for the tips and help

Leeland