compare columns

[rvik]

hai,

i have data in two columns.

Column b contains the master data of jobs for the day.
Column a contains the data of the jobs , with the extension signifyin
the person's id who has completed the job. ( eg. b2, b4)

COL A COL B

a100 b2 a100
a101 b2 a101
a103 b4 a102
a104 b2 a103
a105 b4 a104
a105


I would like to compare both the columns and in col C i would like t
have the person's id who has completed the job against the job number.

eg.

COL A COL B COL C

a100 b2 a100 b2
a101 b2 a101 b2
a103 b4 a102
a104 b2 a103 b4
a105 b4 a104 b2
a105 b4



I do this for about 500 records a day, manually. Any help to sort thi
out would be highly thanked

 

[Aladin Akyurek]

In C1 enter & copy down:

=TRIM(SUBSTITUTE(INDEX($A$1:$A$6,MATCH(B1&"*",$A$1:$A$6,0)),B1,""))

Or, if you would prefer...

=IF((B1<>"")*ISNUMBER(MATCH(B1&"*",$A$1:$A$6,0)),TRIM(SUBSTITUTE(INDEX($A$1:
$A$6,MATCH(B1&"*",$A$1:$A$6,0)),B1,"")),"")

 

[Frank Kabel]

Hi
try the following in C1
=RIGHT(INDEX($A$1:$A$100,IF(ISNUMBER(FIND(B1,$A$1:$A$100)),ROW($A$1:$A$
100))),2)
enter this as array formula (CTRL+SHIFT+ENTER) and copy down