NEED TO ADJUST FORMULA

[floridagunner]

Hello I need some help adjusting a formula.

I have a worksheet named (FA_Split_Master_July1007). Which has this information:

(code)
A B C
12345 TOM
12345 KYLE
12345 DAN
12345 ALEX
67899 TAYLOR
67899 KURT
88425 MIKE
(/code)

In another sheet I have this Information:

(code)
A P
12345
12345
12345
12345
12345
12345
12345
12345
67899
67899
67899
67899
(/code)

I applying this formula to bring in the corresponding names:

(code)=INDEX(FA_Split_Master_July1007!$A$1:$C$57670,SMALL(IF(FA_Split_Master_July1007!$A$1:$C$57670=$A2,ROW(FA_Split_Master_July1007!$A$1:$C$5767)),COUNTIF($A$2:A2,A2)),3)(/code)

However this is the result that I get back:
(code)
A P
12345 TOM
12345 KYLE
12345 DAN
12345 ALEX
12345 #NUM!
12345 #NUM!
12345 #NUM!
12345 #NUM!
67899 TAYLOR
67899 KURT
67899 #NUM!
67899 #NUM!
(/code)

What I need the formula to do is this:

(code)
A P
12345 TOM
12345 KYLE
12345 DAN
12345 ALEX
12345 TOM
12345 KYLE
12345 DAN
12345 ALEX
67899 TAYLOR
67899 KURT
67899 TAYLOR
67899 KURT
(/code)

Does anybody know what I could change in my formula to make it work?

Thanks

 

[barry houdini]

Perhaps try

=INDEX(FA_Split_Master_July1007!$A$1:$C$57670,SMALL(IF(FA_Split_Master_July1007!$A$1:$C$57670=$A2,ROW(FA_Split_Master_July1007!$A$1:$C$5767)),MOD(COUNTIF($A$2:A2,A2)-1,COUNTIF(FA_Split_Master_July1007!$A$1:$A$57670,A2))+1),3)

 

[floridagunner]

Hello Barry thanks for the formula but I cant seem to work out the concept behind your adjustment.

Its not working in my spreadsheet but I am thinking maybe it only needs a couple of Adjustments.

Did you try it? Did it work on your sheet?

 

[floridagunner]

hold on there Barry I think I was wrong. I think it may actually be working after all.

Let me check more closely and I will confirm with you.

THANKS

 

[floridagunner]

This is pretty strange but, whats happening is that when I get to row 757 the formula does not work anymore.

Even though it bought back correctly all the previous 756 names.

Thanks

 

[barry houdini]

I can't think of anything obvious that would cause that. What's in A757 and what result do you get with the formula on that row and subsequent rows?

Is the formula exactly the same as the one I posted?

 

[floridagunner]

Ok I found the mistake.

One of the range references was $A$1:$C$5767.

It should have been $A$1:$C$57670

THANKS AGAIN BARRY. I WAS STRUGGLING WITH THIS FORMULA FOR THE LAST 7 HOURS OR SO.

THANKS

 

[floridagunner]

OH AND BY THE WAY YES IT HAS BOUGHT BACK ALL THE NAMES NOW SO ITS WORKING PERFECTLY.

THANKS AGAIN