Similar to countif

[JonHFL]

Hi,



I have a current dataset that is in excel however has become to large. Currently in that data set I have a key associated with each record for lookup purposes however that key is not exclusive. For instance multiple records will have the same key. For lookups I have used the formula =countif($B:B2,B2)&B2. The result as I copy this formula down to the next row is each new occurrence of the same key will have an increasing value associated with it. This allows me to set a vlookup to pull the 1st record the 2nd and so forth.

I am going to pull all of the data into access and need to be able to recreate this key so I can put it back in excel as values and then perform the lookups. Because of limited use of Access in my corporation I can not do all of the work in Access.

Any Ideas?

Thank you...

[NTC]

well in general terms; if you have multiple records sharing the ID 123.....the database has no way to know which record is "first" vs "second" etc.... so if you want to have a separate field and put in 1,2,3 as you go....then you will always be able to implement this subsort ok...

not completely sure what you operational mode is.....if the shared ID is manually put in...and these records are entered in order you probably could rely on an autonumber field for the subsort if you don't want to manually enter the 1,2,3

[JonHFL]

I will try and explain it better. I am only a beginner in Access so I will do my best.

I have multiple records that each have a Name, Company Code, Year1, Year2, Year3. Each year could have a value ranging from 0 up. However if say Year 2 is 0 then Year 1 will be 0 as well. So I can do an IIF statement to determine what Year has the forst value greater than 0. That is the first part of the key I create. I then in a query add a column that combined the calculated year and the Company Code. This key will attach itself to multiple records which will still have different values and Names.

The plan was then to attach a number to each record with the same key. What I will do with this data in excel is use a vlookup to pull in each row of a certain key so the vlookup will need the occurrence number to distinguish records.

I think what I might try doing is assinging the key and then sorting and then bringing the data into excel in the groups of a single key.

Thank you.