NEED TO ADJUST FORMULA

floridagunner

Board Regular
Joined
Jul 20, 2007
Messages
60
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Board Regular
Joined
Jul 20, 2007
Messages
60
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

Board Regular
Joined
Jul 20, 2007
Messages
60
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

Board Regular
Joined
Jul 20, 2007
Messages
60

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Board Regular
Joined
Jul 20, 2007
Messages
60
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

Board Regular
Joined
Jul 20, 2007
Messages
60
OH AND BY THE WAY YES IT HAS BOUGHT BACK ALL THE NAMES NOW SO ITS WORKING PERFECTLY.

THANKS AGAIN
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,160
Messages
5,768,530
Members
425,480
Latest member
br400821

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