Excel Formula Help 1

[Texanite]

I have a spread sheet with two tabs one called FY and another called CC. In the CC spreadsheet column A has a series of two digit numbers from 01 through 04 (with the leading zero) and column C has a series of numbers with no leading zeros; the numbers can be up to 4 digits. In tab FY column H has the same numbers as Column C in the CC tab. What I am needing to do is find the match from Column I in FY and Column C in CC and then have it take the informtaion in column A (cc tab) and combine it with the number it matches and put the result in Column H in the FY tab. The end result numbers in Column H should look like this:

02-0034
04-2458
02-0004
03-0123 etc.

I know this can be done but don't have the foggiest where to start. Appreciate any help you can give.

Thanks,
Texanite

 

[dyarwood]

If your data was in cell a1 of sheet 1 and a1 of sheet 2 and you want the answer into a cell, insert this formula to get it. You should be able to revise it to get what you want.

Sheet 1 A1 = 01
Sheet 2 A1 = 1827
Use the formula

=Sheet1!A1 & "-" & Sheet2!A1

This will give

Sheet 3 A1 = 01-1827

dyarwood

 

[Texanite]

Thanks for the help. But although it's true I can copy the first two digit numbers, that doesn't address the last 4 digits I need as a end result (see example in original post). True, I can copy the two digit number How do you achieve that?

Thanks,
Texanite

 

[Texanite]

Dyan, the formula you gave me does not match the numbers. What is in sheet 1 A1 does not equal what is in sheet 2 A1. I must find it in list on sheet 2.

Thanks,
Texanite

 

[KenWright]

Do you mean Col H or Col I in FY?

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

Assuming your data is actually in Col I in FY starting in cell I1, then in H1 put the following formula and copy down as far as necessary:-

=INDEX(CC!$A$1:$A$40,MATCH(FY!I1,CC!$C$1:$C$40,0))&"-"&TEXT(I1,"0000&quot

This assumes that your data in tab CC is in the ranges A1:A40. Just adjust them to suit. Likewise the I1 in the formula assumes that that is the start of your data in FY - Again just change to suit.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Texanite]

Thanks, Ken but that didn't work either. I played with the ranges but it still wouldn't work.

Texantie

 

[KenWright]

It works fine honest. The only reason why I could think it might not, is that your data in one range is text and in the other numeric. Are the data in the cells in the two ranges on each of the sheets (ie Cols C and I) exactly the same, ie you can see a number (any of them) in both ranges, and if in any cell you put a formula such as =Num1=Num2 (Use the mouse to point to each number) you get TRUE returned?

More than happy to take a look at the sheet if you have one you can send out, or I can send you a dummy workbook that shows you how it should work.

ken.wright at ntlworld.com

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Texanite]

Ken, I think maybe I have not explained this too well. The following are the columns and what I want to do with it.

FY Column I

4260
24
4
350
80

CC Column A
02
04
06
02
03

CC Column C
4
24
80
350
4260

The FY Column I has 550 Rows (some rows are blank)
The CC Columm C I am trying to match to FY Column I has
435 rows.

The result I want in FY Column H is the combination of the information FY Column I with what's in CC column A, i.e.,

02.0004
04.0024
03.0080
02.0350
04.4260

Hope this helps explain it a little better. If not, I'll be glad to send you the spreadsheet so you can see what I'm talking about.

Thanks,
Texanite

 

[KenWright]

OK, that looks exactly as I surmised, and I would have expected you to simply have to adjust the ranges to suit:-

Assuming all your ranges start in row 1, then in H1:-

=INDEX(CC!$A$1:$A$435,MATCH(FY!I1,CC!$C$1:$C$435,0))&"-"&TEXT(I1,"0000&quot

and paste down. If you didn't want error messages where there are blanks you would use:-

=IF(ISNA(MATCH(FY!I1,CC!$C$1:$C$435,0)),"",INDEX(CC!$A$1:$A$40,MATCH(FY!I1,CC!$C$1:$C$40,0))&"-"&TEXT(I1,"0000&quot)

If this doesn't do it then send it on down and I'll take a look.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Texanite]

I keep getting an error message that "File Not Found". These are all in the same file, just different tabs.

Thanks,
Texanite

 

[KenWright]

Send me the file, it's going to be easier and I have to take the kids up soon.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

LOL - You'll kick yourself now - So close!!!

The reason it prompted you for another file was because you kind of forgot to give us the real tab names. I had assumed they were what you said, and in your formula you had adjusted them for the real names (sort of) but not put any spaces in. A name without a space does not equal a name with a space. The problem you would have had though, is that you would not have been able to put any spaces in with out single quotes round the tab reference, and unless you know to do that you would struggle. Also, you had forgotten to adjust the I4 reference in the TEXT(I4,.... bit of the formula to I14 the same as the others you changed..

So, first - KILL THE MERGED CELLS!!!!!! - They are an abomination that never cause anything but trouble!!! So, after having unmerged cells H14 and H15, in cell H14 put the following formula and copy down as far as you need:-

=IF(ISNA(MATCH('FY 04 Hierarchy'!I14,'Oracle CC Sort'!$C$11:$C$435,0)),"",INDEX('Oracle CC Sort'!$A$11:$A$435,MATCH('FY 04 Hierarchy'!I14,'Oracle CC Sort'!$C$11:$C$435,0))&"-"&TEXT(I14,"0000&quot)

This should sort it. I've sent the file back anyway all fixed.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------