how to compare two lists with duplicates(or more)

[uprety100]

suppose the data are as follows
a

1. a 1S.no
2. b 2
3. c 3
4. a 4
5. a 5
6. e 6
7. a 7

b

1. a 1S.no
2. b 2
3. c 3
4. d 4
5. a 5
6. a 6

1. when i vlookup(second, first table,2,false)... i.e i want to check from which s.no, has the data been extracted.... when it looks for 'a' in table a, it tells that all are from s.no1.... for a in sno. 5 and s.no 6 (in table b also), it tells it is from sno1...i.e it compares all the 'a's in b with the first 'a' only in table a

1. what i want is

1. when looking a in s.no5 intable b it should tell it is from s.no 4(table a)

1. and when looking s.no.6(table ),,,, it should tell it is from s.no5

1. in other words... it should match first a and first a... second a and second a and third a and third a and not...... first second and third a (table b) with the first a in table a only.

1. how to do this?

 

[energman58]

Sorry can you be a bit clearer - in your example what value do you want to return?

Are you looking at the position in the list of each letter or are you saying that you want to return the serial number in the first list of the occurrance of the matched letter in the second list so you match the first "a" with the first "a" and the second with the second and so on? So in your second list the answers should be

1 (serial no of first a in first list)
2 (serial number of first b in first list)
3 (serial number of first c in first list)
? (serial number of first d in first list)
4 (serial number of second a in first list)

 

[uprety100]

thanks for replying
yes the result is like what u have written..
the result i want after i execute the command is...

b

1. 1a a from sno1
2. 2b b from sno2
3. 3c c from sno3
4. 4d #na#
5. 5a a from sno4 i.e second a from table a
6. 6a a from sno5 i.e third a from table a

(this is just a sample there are infact morethan 1000 rows of data)

thanks again.
.

Sorry can you be a bit clearer - in your example what value do you want to return?

Are you looking at the position in the list of each letter or are you saying that you want to return the serial number in the first list of the occurrance of the matched letter in the second list so you match the first "a" with the first "a" and the second with the second and so on? So in your second list the answers should be

1 (serial no of first a in first list)
2 (serial number of first b in first list)
3 (serial number of first c in first list)
? (serial number of first d in first list)
4 (serial number of second a in first list)

 

[energman58]

So your two lists are in the same order but the second list has some extra rows in it at various places and you want to return the number from the first list or N/A or something for the "new" values?

 

[uprety100]

yes thats true

So your two lists are in the same order but the second list has some extra rows in it at various places and you want to return the number from the first list or N/A or something for the "new" values?

 

[uprety100]

when will i get the reply??

yes thats true