Excel Query, logical function

ebayissues

hi guys,

I am having trouble with excel I have 2 excel files with one about 150 rows and eight columns and the other about 250 rows and same number of columns.

Let say the 150 rows are the list of 150 schools in Ireland and the columns A - G have the headings from 1998 - 2008 number of teachers for these 150 schools for this period. This is Excel sheet A.

The other excel sheet B has these same information but the information is a bit broken down e.g for instance the names of the school are in irish, and the data set can be split in 2 or 3 figures.

E.g for a school on the 15th row the number of teachers is 100. And in the other excel sheet the number of teacher is split 80 male and 20 female. but the name of the school is still in English but it will have the name of the school twice.

Lets say in this hypothetical scenario that excel file B is disjointed due to being sent to the principal of the 150 schools. Some inputted the total number of male and female teachers and some decided to split them male and female.

What I'm trying to do is to match a school from excel file A to excel file B is an automated manner.

SO I though of IF statement.

would it be possible to see if the IF statement .

e.g row 15, if the name matches from 1 - 150 in B show where applicable.

Can anyone tell me how to do something like this ?

Thoie

ebayissues wrote: »

What I'm trying to do is to match a school from excel file A to excel file B is an automated manner.

SO I though of IF statement.

would it be possible to see if the IF statement .

e.g row 15, if the name matches from 1 - 150 in B show where applicable.

Can anyone tell me how to do something like this ?

I think you want a vlookup - google it, it's very handy.

For simplicity, let's say column D is the column you're interested in sheet B, and column A holds the school name that you want to match.

First off, in both sheets, sort the school names alphabetically.
I'll call sheet A, cell A1 "A.A1", and sheet B, cell A1 as "B.A1" for simplicity.

In A.D2 put the formula
=vlookup(A2, B.$A$1:B.$D$500, 4,0)
then copy that down the full column.

That command says look up what's in A2 (the school name) and compare it to the range A1:D500 on sheet B. If it finds a match, take the values in the 4th column (A,B,C,D - D is the 4th column). Make sure that the school names are an exact match (0 for an exact match, 1 for a fluffy match). The reason for keeping the $ signs around the lookup range is to make sure that's static when you copy the command down.

If the information isn't confidential, stick it up here and I can do an example for you if you need more help.

ebayissues

Thanks for that.

Come to think of it by boss uses vlookup but its not very effective. Maybe its because don't know how to use yes.


Yes the information is very confidential. I work in a financial company. So what I said above is an hypotheictal example mirroring the type of work I do.

I'll try to put a example up.

ebayissues

Here's an example.

In the first sheet we have 8 institutions with a combined hypothetical number of teachers sacked.

The second is also the same but splits it into male and femle but not specifying thats its msale and female. its known by the data entry twice. If you adfd up the figures of those inputed twice they add up to that in sheet A.

Thoie

Snap - I was doing an example at the same time (attached). If your boss isn't finding vlookup very effective, (s)he's probably not using it properly - you can show them the correct way tomorrow, and win praise and sweeties.

I'll take a look at your example now, but the first thing I will say is that for your own sanity, try and include everything in one file (for example, by using the copy worksheet command) - otherwise if someone gets one file and not the other, they'll end up with warnings about broken links and external data.

Thoie

Here's your book 1, with details of what I did on the "Instructions" tab.

You don't want to overwrite your existing formula with the vlookup, or, in the case of NUIG in your example, you'll lose the existing number that you have there. You could use an if statement in combination with the vlookup, but that's impractical as you'd have to have the original number in every statement, making it more manual. If you have the time and energy (I'm about to cook dinner), take a look at the INDEX formula as well. Combining the INDEX and VLOOKUPs, you wouldn't have to change the column references, you could, instead, use index to figure out what the column number in the vlookup should be. Not really worth your time if this is a one off task, but useful if you're going to do this frequently.

Is that all fabulously clear now?

ebayissues

Hi,

This is not a once. I've looked at index and it might help but will explore it inot.

Aplogies if I've misled you but the data of the rows are completely uncorrelated to each other.

I work in a fund admin firm where we have to do reconciliations of cashflow, updating credit ratings,calculate the NAV.

For my query there are about 500 assets that's from row 1 - 510 and in column b - j there might be price, rating by moody's standard and poor, there's also sentiments whether buy or hold etc.

Being the case I cant do the sum of NAV in column b or the sum of the share price in column c.

Am I making my point clear ? lol

Thoie

I'm not getting my head around that. I think the best thing to do would be send me an NDA, then hire me for 24 hours at the bargain price of €240,000 (ex VAT).

How would you manually reconcile shareprice, sentiments etc if they've been split onto different rows?

Say your spreadsheet has Trinity bank, and I, the owner of the bank, have sent you back to rows of data, one for Trinity Student Bank, and another for Trinity Staff Bank. Trinity Student Bank has a share price of 1300 and a sentiment of "sell". Trinity Staff Bank has a share price of 679 and a sentiment of "buy". If you were doing this manually, what would you do with those?

If you're planning on replacing your single "Trinity Bank" with two rows (to match what I've sent you), you can use the MATCH function to see if "Trinity Staff Bank" exists in your original list, and then filter for the ones that don't match and copy them over?

namenotavailablE

I'm not 100% clear on what you're trying to achieve but maybe this idea would help: would it be useful on sheet 2 to apply a conditional format that would automatically highlight all names which are duplicated? So- using your school example- you'd have a conditional format that highlights St Peter and Maynooth, since these schools are both entered twice? You can highlight them by changing the colour of the cell, the colour of the text etc.

If so, let me know.

ebayissues

namenotavailabl wrote: »

I'm not 100% clear on what you're trying to achieve but maybe this idea would help: would it be useful on sheet 2 to apply a conditional format that would automatically highlight all names which are duplicated? So- using your school example- you'd have a conditional format that highlights St Peter and Maynooth, since these schools are both entered twice? You can highlight them by changing the colour of the cell, the colour of the text etc.

If so, let me know.

Hi

Thanks for replying.

This could work nut some of the assets could be named different e.g. westeros could be west. But we can use west as a phrase of recongntion.

Thoie

namenotavailabl wrote: »

I'm not 100% clear on what you're trying to achieve but maybe this idea would help: would it be useful on sheet 2 to apply a conditional format that would automatically highlight all names which are duplicated? So- using your school example- you'd have a conditional format that highlights St Peter and Maynooth, since these schools are both entered twice? You can highlight them by changing the colour of the cell, the colour of the text etc.

If so, let me know.

If you're using Excel 2010, the conditional formatting button already has a handy "Highlight duplicates" button, so no faffing required anymore.

ebayissues

Thoie wrote: »

I'm not getting my head around that. I think the best thing to do would be send me an NDA, then hire me for 24 hours at the bargain price of €240,000 (ex VAT).

How would you manually reconcile shareprice, sentiments etc if they've been split onto different rows?

Say your spreadsheet has Trinity bank, and I, the owner of the bank, have sent you back to rows of data, one for Trinity Student Bank, and another for Trinity Staff Bank. Trinity Student Bank has a share price of 1300 and a sentiment of "sell". Trinity Staff Bank has a share price of 679 and a sentiment of "buy". If you were doing this manually, what would you do with those?

If you're planning on replacing your single "Trinity Bank" with two rows (to match what I've sent you), you can use the MATCH function to see if "Trinity Staff Bank" exists in your original list, and then filter for the ones that don't match and copy them over?

Its not abut reconciliation its more of updating what the client has to that of ours.

I have uploaded a excel file from a poster in the I&M forum this is a typical spreadsheet of what I deal with everyday.

Take for example prime active capital could be called a different name on sheet be. lets say Prime active. but some of the values e.g market cap and symbol and date will match on both sheets.

I'd like to figure out a system where I could match those automatically or point me in the direction of those that match.

namenotavailablE

OK- so if you are using Excel 2010 or greater, you can apply the 'Highlight cells' rules to your sheet 2 dataset using- in your example- the text 'West' as the key text.
It might not be perfect as it would automatically highlight Western Digital and Westeros and West- which isn't quite what you might intend but it might be a start if the data is in a bit of a mess.

ebayissues

At home I have excel 2007 but at work it might be 2010. I'm not sure.

namenotavailablE

You can check if 2007 has the same capability- are you familiar with 'conditional formatting'?

Thoie

ebayissues wrote: »

Take for example prime active capital could be called a different name on sheet be. lets say Prime active. but some of the values e.g market cap and symbol and date will match on both sheets.

I know this is only an example sheet, but if both of yours have the ticker code on them, that would be the easiest way to link them, as that has to be unique for each asset.

If you know for definite that the market cap, symbol and date will always match, then you can combine those into a key. In this case, I've inserted a column between A and B, called it "Key", and put in the formula
=C35&G35&H35

For Prime Active, that's given me a key of PACC0.97528341358
(PACC & the unrounded 1.0 & the raw date format of 25 March 2013). Put another key on the second sheet with the same info, and then you can vlookup on that. For the fields that are left as N/A you could try a different key.

ebayissues

Thoie wrote: »

I know this is only an example sheet, but if both of yours have the ticker code on them, that would be the easiest way to link them, as that has to be unique for each asset.

If you know for definite that the market cap, symbol and date will always match, then you can combine those into a key. In this case, I've inserted a column between A and B, called it "Key", and put in the formula
=C35&G35&H35.

Sorry I dont this.