Jump to content

Home

I need urgent help in Excel please


Recommended Posts

I’m working on this excel file for professional purposes where I need to create a function that runs through two different columns in a Table A, and compare it with cells in a column in Table B, then returns data from another column in Table B.

 

If you’re starting to feel somehow dizzy, I will try to put it in a simple way.

 

What do we already have?

 

1. Table A that has a buyer_id column, and a seller_id column.

2. Table B that has a client_id column and a client_name column.

3. Table C, where the function should be inserted to return the client’s name.

 

What is needed to be done?

 

Insert a function in Table C that does the following:

1. Check the buyer_id and seller_id in table A.

2. Check client_id in Table B.

3. Find matches between client_id and the other two columns seller_id, buyer_id, if there are ones.

4. If a match is found, then return the corresponding client_name from Table B.

5. Else, return FALSE.

 

I have came up with a certain formula of “ifs” and “ors”, but the problem was that this formula compares only cells belong to the same row, i.e. cell A1 in Table A with cell A1 in Table B. where it is highly likely for cell A1 in Table A to match cell A16 in Table B.

 

Is there anyone in here who can lend me a hand in this please?

 

Thanks in advance.

Link to comment
Share on other sites

I’m working on this excel file for professional purposes where I need to create a function that runs through two different columns in a Table A, and compare it with cells in a column in Table B, then returns data from another column in Table B.

 

If you’re starting to feel somehow dizzy, I will try to put it in a simple way.

 

What do we already have?

 

1. Table A that has a buyer_id column, and a seller_id column.

2. Table B that has a client_id column and a client_name column.

3. Table C, where the function should be inserted to return the client’s name.

 

What is needed to be done?

 

Insert a function in Table C that does the following:

1. Check the buyer_id and seller_id in table A.

2. Check client_id in Table B.

3. Find matches between client_id and the other two columns seller_id, buyer_id, if there are ones.

4. If a match is found, then return the corresponding client_name from Table B.

5. Else, return FALSE.

 

I have came up with a certain formula of “ifs” and “ors”, but the problem was that this formula compares only cells belong to the same row, i.e. cell A1 in Table A with cell A1 in Table B. where it is highly likely for cell A1 in Table A to match cell A16 in Table B.

 

Is there anyone in here who can lend me a hand in this please?

 

Thanks in advance.

 

Easiest way to do it would be to write a macro and then run it, especially if your going to be running this every day/wee/month to avoid having to keep doing forumla's - There are a lot of guides to using macro's here's one - http://office.microsoft.com/en-us/excel-help/introduction-to-custom-macros-in-excel-HA001118958.aspx

Link to comment
Share on other sites

I messed around with it a bit. This should be more or less what you need.

 

edit: accidentally didn't make the formula quite right: it was supposed to be

=IF(OR(IF(ISERROR(VLOOKUP(client_id,buyer_id,1,FALSE)=client_id), FALSE, TRUE), IF(ISERROR(VLOOKUP(client_id,seller_id,1,FALSE)=client_id), FALSE, TRUE)),client_name, FALSE)

 

I fixed the download also.

Link to comment
Share on other sites

I messed around with it a bit. This should be more or less what you need.

 

edit: accidentally didn't make the formula quite right: it was supposed to be

=IF(OR(IF(ISERROR(VLOOKUP(client_id,buyer_id,1,FALSE)=client_id), FALSE, TRUE), IF(ISERROR(VLOOKUP(client_id,seller_id,1,FALSE)=client_id), FALSE, TRUE)),client_name, FALSE)

 

I fixed the download also.

 

I have revised your formula to fit my worksheet but I did not get what I want. I've already tried a similar formula but the problem is still as stated above.

 

the problem was that this formula compares only cells belong to the same row, i.e. cell A1 in Table A with cell A1 in Table B. where it is highly likely for cell A1 in Table A to match cell A16 in Table B.

 

And I cannot download the file.

 

Thanks for your help mate.

Link to comment
Share on other sites

I have revised your formula to fit my worksheet but I did not get what I want. I've already tried a similar formula but the problem is still as stated above.

 

the problem was that this formula compares only cells belong to the same row, i.e. cell A1 in Table A with cell A1 in Table B. where it is highly likely for cell A1 in Table A to match cell A16 in Table B.

 

And I cannot download the file.

 

Thanks for your help mate.

I'm not sure I understand the problem. My formula gets the client_id and checks it against the entire columns seller_id and buyer_id, one after the other, not just one row ("buyer_id" in the formula is the name of column A1 on Sheet1, and similarly with the others). If it finds a match in either column, the OR returns TRUE and the top level IF will put the client_name on the same row as the client_id. You can test this behavior in the example sheet. What exactly is it doing wrong?

 

As for the download, try this. Not sure why the GoogleDocs one isn't working for you.

Link to comment
Share on other sites

As for the download, try this. Not sure why the GoogleDocs one isn't working for you.

 

Just out of curiousity > I tried downloading this too - results:

 

*when signed into my google account: works fine

 

*when not signed into my google account - you get this

errorGdocs.jpg >> the download link wont work either, tested on FF and IE8

 

hence, to get around the error, simply sign into your google/gmail account, if you have one :p

 

mtfbwya

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...