Help with Cell fornula or format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fundman - 14 Dec 2007 20:51 GMT

We use Office Excel 2003- We need to insert data from preset column B in
Spreadsheet 1 to "new column B" in Spreadsheet 2 (downloaded data in Col A,
but new Col B created by us). Thus, the formula you advise for cells in Sheet
2 column B, need to produce the result x=Medium, y=Small and Z=large, based
on naming criteria match.

Sheet 1 Sheet 2
Col A Col B Col A Col B
FundA Large Fund B x
FundB Medium Fund C y
FundC Small Fund A z

Reply to this Message

Luke M - 14 Dec 2007 21:42 GMT

=IF('Sheet1'!B1="Large","x",IF('Sheet1'!B1="Medium","y",IF('Sheet1'!B1="Small","z","Some other entry was present")))

Then you can just copy and paste down.

Signature

Best Regards,

Luke M

> We use Office Excel 2003- We need to insert data from preset column B in
> Spreadsheet 1 to "new column B" in Spreadsheet 2 (downloaded data in Col A,
[quoted text clipped - 7 lines]
> FundB Medium Fund C y
> FundC Small Fund A z

Reply to this Message

Fundman - 15 Dec 2007 14:57 GMT

Unfortunately, this did not totally answer the question.

The formula for Sheet2 Col B Rows1-3, needs to look at 2 variables. First,
it has to recognize the "Cell-Fund B in Sheet2 Col A". It then has to look to
match this text to a cell in "Sheet1 Col A" (in this case, the row 2). Once
it finds that match, it needs to insert the result "medium" in Sheet2,
ColumnB, Row 1. Once you suggest the formula, we confirm that it would be
copied and pasted down.

We look forward to your new suggested solution...Thanks again Luke in advance

> =IF('Sheet1'!B1="Large","x",IF('Sheet1'!B1="Medium","y",IF('Sheet1'!B1="Small","z","Some other entry was present")))
>
[quoted text clipped - 11 lines]
> > FundB Medium Fund C y
> > FundC Small Fund A z

Reply to this Message

Max - 16 Dec 2007 08:26 GMT

Think a simple vlookup should do work fine

Source data assumed in Sheet1, cols A & B

In Sheet2,
Place in B1:
=IF(A1="","",VLOOKUP(A1,Sheet1!A:B,2,0))
Copy down as far as required

If the above vlookup doesn't match/return 100%, that means you've got a data
consistency issue in the fund names in Sheet1/2's col A - ie they don't
exactly match

If so, you could try this fuzzy, heavier duty index/match ..
Place instead in B1, array-enter the formula by pressing CTRL+SHIFT+ENTER:
=IF(A1="","",INDEX(Sheet1!$B$1:$B$100,MATCH(TRUE,ISNUMBER(SEARCH(A1,Sheet1!$A$1:$A$100)),0)))
Copy down as far as required. Adapt the ranges to suit.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Unfortunately, this did not totally answer the question.
>
[quoted text clipped - 4 lines]
> ColumnB, Row 1. Once you suggest the formula, we confirm that it would be
> copied and pasted down.

Reply to this Message

Fundman - 16 Dec 2007 16:10 GMT

Max-

When I inserted the formula you suggested I got FALSE each time. Perhaps I
don't understand what needs to be input in the quotes for the first part of
the argument A1="",""

However, when I move the sheet 1 legend (columns a and b or a total of 6
cells) into bottom of Sheet 2 and just use the function
VLOOKUP ($E39,$E39:$F41,2,0) it seems to
produce the correct text in column b for each of the 3 arguments. ..Any final
thoughts? Thanks again..

> Think a simple vlookup should do work fine
>
[quoted text clipped - 21 lines]
> > ColumnB, Row 1. Once you suggest the formula, we confirm that it would be
> > copied and pasted down.

Reply to this Message

Max - 16 Dec 2007 22:19 GMT

I'm not sure what's happening over there ..

Perhaps a working sample
to illustrate the earlier suggestion would be good?:

http://cjoint.com/?mqxdgOOOpw
Vlookup_example.xls

The suggested expression:

> > =IF(A1="","",VLOOKUP(A1,Sheet1!A:B,2,0))

presumes that your source data/table is in Sheet1's col A and B
and your lookup values in Sheet2 starts in A1 down

The front IF part of the expression: =IF(A1="","", ..
is simply to check whether the lookup col A is "blank"
If it is, then the expression will return neat looking blanks: ""
So the formula could be copied down beyond existing data in col A (beyond
the last data row in col A) to cater for future data entries

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Max-
>
[quoted text clipped - 7 lines]
> produce the correct text in column b for each of the 3 arguments. ..Any final
> thoughts? Thanks again..

Reply to this Message

Fundman - 17 Dec 2007 00:06 GMT

Works now...Thanks Max

> I'm not sure what's happening over there ..
>
[quoted text clipped - 25 lines]
> > produce the correct text in column b for each of the 3 arguments. ..Any final
> > thoughts? Thanks again..

Reply to this Message

Max - 17 Dec 2007 00:58 GMT

Glad that cleared it up for you.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Works now...Thanks Max

Reply to this Message

Rate this thread: