Lookup problem..........!!!!!!

[KOLM#S]

Hi
I have a consolidated file that needs to look at a radiology file and select whether it is an A, B or C tariff, D tariffs are to be ignored. However, the radiology file can not be sorted so the CRN numbers are random and I need to know whether there is anyway of selecting the first instance in Rad(1), second in Rad(2) and the third in Rad(3)................

Not sure how clear this is ... even I'm having problems in understanding it and I know what the problem is!!!!!!!!!!!!!!!!

Consolidated File
Rad(1) Rad(2) Rad(3)
A B
C



Radiology

CRN Date X-Ray
123456 01/04/2006 A
789789 05/04/2006 C
123456 08/04/2006 B
123456 20/04/20006 D

Many Thanks
KOLM#s

 

[Andrew Poulsom]

Can you be a bit more clear on what you want where in the Consolidated File and why? Give cell references for:

Rad(1) Rad(2) Rad(3)
A B
C

 

[KOLM#S]

Hi Andrew
Many Thanks for your response......

AB2 needs look at the CRN in AA2 and then select the first instance of it in the radiology file, selecting the X-ray result A, B or C... if should ignore D at all times.
AC3 needs to select the second instance
AC4 the third instance......

I hope this makes thins a little clearer!?!



Consolidated
..............Col-AA....Col-AB...Col-AC...Col-AD
Row 1.....CRN........Rad(1)...Rad(2)....Rad(3)
Row 2 123456.........A..........B
Row 3 789789.........C
Row 4 345345


Radiology
..............Col-A......Col-B...........Col-C
Row 1......CRN Date X-Ray
Row 2.....123456..01/04/2006.......A
Row 3.....789789..05/04/2006.......C
Row 4.....123456..08/04/2006.......B
Row 5.....123456..20/04/2006.......D

Many Thanks again
KOLM#s

 

[Domenic]

Try the following formulas, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

AB2, copied down:

=SUM(IF($A$2:$A$5=$AA2,IF($C$2:$C$5<>"D",1)))

AC2, copied down and across:

=IF(COLUMNS($AC2:AC2)<=$AB2,INDEX($C$2:$C$5,SMALL(IF($A$2:$A$5=$AA2,IF($C$2:$C$5<>"D",ROW($C$2:$C$5)-ROW($C$2)+1)),COLUMNS($AC2:AC2))),"")

Hope this helps!

 

[KOLM#S]

Still a problem with second column???

Hi Domenic

many thanks

This works perfectly on the first column pulling through A & C: unfortunately it leaves the other columns blank, it does not pull the B through???

Consolidated
..............Col-AA....Col-AB...Col-AC...Col-AD
Row 1.....CRN........Rad(1)...Rad(2)....Rad(3)
Row 2 123456.........A ..........B
Row 3 789789.........C
Row 4 345345

Many Thanks
KOLM#s

 

[KOLM#S]

Still a problem with second column???

Hi Domenic

many thanks

This works perfectly on the first column pulling through A & C: unfortunately it leaves the other columns blank, it does not pull the B through???

Consolidated
..............Col-AA....Col-AB...Col-AC...Col-AD
Row 1.....CRN........Rad(1)...Rad(2)....Rad(3)
Row 2 123456.........A ..........B
Row 3 789789.........C
Row 4 345345

Many Thanks
KOLM#s

 

[Andrew Poulsom]

Domenic's formulas assume you insert a new column AB, to hold his helper count of CRN. Then copy the formula in AC2 (now Rad(1)) to AD2:AE2 and AC3:AE4.

And don't forget Ctrl+Shift+Enter after typing the formulas.

When done you can cut column AB and insert it to the right of your data if you want.

 

[KOLM#S]

fantastic...............

Many thanks Andrew

That works great....................

KOLM#s