match/index AGAIN - same formula, no longer working

invisigirl · May 23rd, 2002, 11:58 AM

I swear to God in Heaven that I didn't change a THING in the formulas, and now it's not working right. I'm going to try to recreate the issue. Be patient.

In Column B, I've got a Unit Code repeated as many times as there are different Diagnosis Codes in Column D. Therefore, Column B will look like this:

41900
41900
41900
41900
41900
41900

In Column C, I've got a Test Name, again, repeated as many times as there are variations in Column D:

Alpha Fetoprotein
Alpha Fetoprotein
Alpha Fetoprotein
Alpha Fetoprotein
Alpha Fetoprotein
Alpha Fetoprotein

In Column D, I have these different Diagnosis Codes:

235.3
235.4
571.40
571.41
571.49
789.1

And, finally, in Column E I have the Descriptions of what the codes mean:

Liver and biliary passages
Retroperitoneum and peritoneum
Chronic hepatitis, unspecified
Chronic persistent hepatitis
Other, chronic hepatitis
Hepatomegaly

Now, on my lookup sheet (for the end users), I have the following formulas:

In cell B8, based on the Unit Code typed in cell B6, is this formula, used to pull in the test name:

=IF(COUNTIF('CPT DATA'!$B:$B,B$6),INDEX('CPT DATA'!$C:$C,MATCH(B$6,'CPT DATA'!$B:$B,0)),"ABN not required for this procedure")

In cell B13, this formula searches the data sheet for the code in B6 and brings back the first matching Diagnosis Code:

=IF(COUNTIF('CPT DATA'!$B:$B,B$6),INDEX('CPT DATA'!$D:$D,MATCH(B$6,'CPT DATA'!$B:$B,0)),"")

In cell B14, this formula brings back the NEXT consecutive Diagnosis Code matching the Unit Code:

=IF(COUNTIF('CPT DATA'!$B:$B,B$6)>ROW()-D$5,INDEX('CPT DATA'!$D:$D,MATCH(B$6,OFFSET('CPT DATA'!$B$1,MATCH(B13,'CPT DATA'!$D:$D,0),0,$D$4,1),0)+MATCH(B13,'CPT DATA'!$D:$D,0)),"")

I think the formulas that pull the description up alongside the Diagnosis Code are fine. Also, there are formulas that determine the number of rows to search. Aladin gave me ALL of these (the angel!!).

The problem is that I've checked and rechecked these formulas to make sure they're the same as they were last week when they worked, and they appear to be, but the information being pulled in is always ONLY the FIRST unique Diagnosis Code (column D). Sometimes it repeats three lines down, sometimes 10...seems to be an arbitrary number of times, though the formula is copied down to almost infinity on the worksheet.

Why would this happen? The formula seems to be increasing one row number like I would think it should.

The only thing I can think of that has changed is that I have been inserting rows on the data sheet to make room for all the data I have to paste in there from another source (that's the only way to do that particular part of it). I've checked to make sure the cell formatting is staying the same, and it is.

I must sound like the biggest idiot, but I could really use some assistance. If there's anything I didn't explain well enough, let me know.

I really do have other talents. This is just not one of them.

Aladin Akyurek · May 23rd, 2002, 12:23 PM

I just attempted to replicate using the data you mention. Nothing unusual. The formulas retrieve the diagnoses and descriptions associated with 41900.

It might be the case that the additions you make do not have the same underlying format (that's the format which Excel sees as the rest of data.

If you want me to look at your WB, here is my e-mail address:

aladin_akyurek@yahoo.com

Rocky E · May 23rd, 2002, 12:24 PM

Well you got it to work once you will get it to work again. I tried to build a sheet, but you must have left something out or I don't understand. Anyway the first thing I would do is make a copy of what you have. Then disassemble your compound IF blah blah and make some simple diagnostic cells that work. Then once you get the bits working the way you want start pasting things back together. Basically tear it apart until it works. Then put it back together without breaking it.

invisigirl · May 23rd, 2002, 12:26 PM

Will do. My name is April, by the way, in case you think it's someone sending you a virus.

Brian from Maui · May 23rd, 2002, 12:28 PM

April,

Don't forget to send the coffee cake to Aladin

invisigirl · May 23rd, 2002, 12:31 PM

Thanks, Rocky. If Aladin is stumped (though that'd shock me!), I'll try it. The formulas themselves are pretty darn confusing to me, so I was hesitant to try to mess with them further.

Love your quote, by the way.

invisigirl · May 23rd, 2002, 12:32 PM

LOL, Brian! I'm better at pie, actually. Since you're from Maui, I can send you my recipe for banana-guava pie if you want.

May 23rd, 2002, 12:24 PM	#3
Rocky E Board Regular Join Date: Feb 2002 Location: North Alabama, USA Posts: 105	Well you got it to work once you will get it to work again. I tried to build a sheet, but you must have left something out or I don't understand. Anyway the first thing I would do is make a copy of what you have. Then disassemble your compound IF blah blah and make some simple diagnostic cells that work. Then once you get the bits working the way you want start pasting things back together. Basically tear it apart until it works. Then put it back together without breaking it. __________________ Hope this helps! Rocky <h6>"Be not the first by whom the New are try'd, Nor yet the last to lay the Old aside." Alexander Pope (1688-1744).</h6>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 23rd, 2002, 11:58 AM	#1
invisigirl Board Regular Join Date: Mar 2002 Location: Oregon Posts: 130	I swear to God in Heaven that I didn't change a THING in the formulas, and now it's not working right. I'm going to try to recreate the issue. Be patient. In Column B, I've got a Unit Code repeated as many times as there are different Diagnosis Codes in Column D. Therefore, Column B will look like this: 41900 41900 41900 41900 41900 41900 In Column C, I've got a Test Name, again, repeated as many times as there are variations in Column D: Alpha Fetoprotein Alpha Fetoprotein Alpha Fetoprotein Alpha Fetoprotein Alpha Fetoprotein Alpha Fetoprotein In Column D, I have these different Diagnosis Codes: 235.3 235.4 571.40 571.41 571.49 789.1 And, finally, in Column E I have the Descriptions of what the codes mean: Liver and biliary passages Retroperitoneum and peritoneum Chronic hepatitis, unspecified Chronic persistent hepatitis Other, chronic hepatitis Hepatomegaly Now, on my lookup sheet (for the end users), I have the following formulas: In cell B8, based on the Unit Code typed in cell B6, is this formula, used to pull in the test name: =IF(COUNTIF('CPT DATA'!$B:$B,B$6),INDEX('CPT DATA'!$C:$C,MATCH(B$6,'CPT DATA'!$B:$B,0)),"ABN not required for this procedure") In cell B13, this formula searches the data sheet for the code in B6 and brings back the first matching Diagnosis Code: =IF(COUNTIF('CPT DATA'!$B:$B,B$6),INDEX('CPT DATA'!$D:$D,MATCH(B$6,'CPT DATA'!$B:$B,0)),"") In cell B14, this formula brings back the NEXT consecutive Diagnosis Code matching the Unit Code: =IF(COUNTIF('CPT DATA'!$B:$B,B$6)>ROW()-D$5,INDEX('CPT DATA'!$D:$D,MATCH(B$6,OFFSET('CPT DATA'!$B$1,MATCH(B13,'CPT DATA'!$D:$D,0),0,$D$4,1),0)+MATCH(B13,'CPT DATA'!$D:$D,0)),"") I think the formulas that pull the description up alongside the Diagnosis Code are fine. Also, there are formulas that determine the number of rows to search. Aladin gave me ALL of these (the angel!!). The problem is that I've checked and rechecked these formulas to make sure they're the same as they were last week when they worked, and they appear to be, but the information being pulled in is always ONLY the FIRST unique Diagnosis Code (column D). Sometimes it repeats three lines down, sometimes 10...seems to be an arbitrary number of times, though the formula is copied down to almost infinity on the worksheet. Why would this happen? The formula seems to be increasing one row number like I would think it should. The only thing I can think of that has changed is that I have been inserting rows on the data sheet to make room for all the data I have to paste in there from another source (that's the only way to do that particular part of it). I've checked to make sure the cell formatting is staying the same, and it is. I must sound like the biggest idiot, but I could really use some assistance. If there's anything I didn't explain well enough, let me know. I really do have other talents. This is just not one of them.

May 23rd, 2002, 12:23 PM	#2
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,318	I just attempted to replicate using the data you mention. Nothing unusual. The formulas retrieve the diagnoses and descriptions associated with 41900. It might be the case that the additions you make do not have the same underlying format (that's the format which Excel sees as the rest of data. If you want me to look at your WB, here is my e-mail address: aladin_akyurek@yahoo.com

May 23rd, 2002, 12:26 PM	#4
invisigirl Board Regular Join Date: Mar 2002 Location: Oregon Posts: 130	Will do. My name is April, by the way, in case you think it's someone sending you a virus.

May 23rd, 2002, 12:28 PM	#5
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	April, Don't forget to send the coffee cake to Aladin

May 23rd, 2002, 12:31 PM	#6
invisigirl Board Regular Join Date: Mar 2002 Location: Oregon Posts: 130	Thanks, Rocky. If Aladin is stumped (though that'd shock me!), I'll try it. The formulas themselves are pretty darn confusing to me, so I was hesitant to try to mess with them further. Love your quote, by the way.

May 23rd, 2002, 12:32 PM	#7
invisigirl Board Regular Join Date: Mar 2002 Location: Oregon Posts: 130	LOL, Brian! I'm better at pie, actually. Since you're from Maui, I can send you my recipe for banana-guava pie if you want.