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

KOLM#S

Board Regular
Joined
Jun 2, 2006
Messages
63
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 :confused:
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Jun 2, 2006
Messages
63
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 :oops:
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,756
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

Board Regular
Joined
Jun 2, 2006
Messages
63

ADVERTISEMENT

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 :confused:
 

KOLM#S

Board Regular
Joined
Jun 2, 2006
Messages
63
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 :confused:
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Jun 2, 2006
Messages
63
fantastic...............

Many thanks Andrew

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

KOLM#s
:biggrin:
 

Forum statistics

Threads
1,144,370
Messages
5,723,964
Members
422,529
Latest member
mbilal429

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top