Excel 2007 : Match and Sort two columns with Unique ID

[gurp99]

Hello Everyone,

I am having a problem matching and sorting two columns. What I have are two columns with different names but the same unique id on the end of the filename for both columns. The unique ID is similar to (^CFC002153)

This is what it looks like:

Column A ------ --------------- ---- Column B
example file ^CFC002153 ---- helping you ^CFC002423
this is text ^CFC001547 ----- Excel Help ^CFC021445
new name ^CFC002423 ------ second ^CFC002153
text help ^CFC021445 -------- new text ^CFC001547

What I need is something like this

Column A -------------------- ---- Column B
example file ^CFC002153 ----- second ^CFC002153
this is text ^CFC001547 -- ---- new text ^CFC001547
new name ^CFC002423 -- ---- helping you ^CFC002423
text help ^CFC021445 ----- ---- Excel Help ^CFC021445


Any help please?

Thank You

[contaminated]

Hi
in C1 use this formula
'=INDEX($B$1:$B$4,MATCH(TRIM(RIGHT(A7,LEN(A7)-SEARCH("^",A7))),TRIM(RIGHT($B$1:$B$4,LEN($B$1:$B$4)-SEARCH("^",$B$1:$B$4))),0))

confirm w. Ctrl+shift+Enter
Then copy formula resukts and select column B. Reght click Paste special and choose Values . Pree OK. Thus you'l get rid of formulas ...
See file

[gurp99]

Thank you very much!