lfnetwork.com mark read register faq members calendar

Thread: I need urgent help in Excel please
Thread Tools Display Modes
Post a new thread. Add a reply to this thread. Indicate all threads in this forum as read. Subscribe to this forum. RSS feed: this forum RSS feed: all forums
Old 08-11-2010, 03:28 AM   #1
Aristotélēsticus
Forumite
 
Aristotélēsticus's Avatar
 
Join Date: Jan 2005
Location: in the Source
Posts: 605
I need urgent help in Excel please

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.
Aristotélēsticus is offline   you may: quote & reply,
Old 08-11-2010, 03:57 AM   #2
Astrotoy7
A Face from The Past
 
Astrotoy7's Avatar
 
Join Date: Apr 2002
Posts: 10,284
Notable contributor Helpful! Folder extraordinaire LFN Staff Member 
what an unusual post!

Excel is not my strong suit at all... if anyone around here has no answers, head over here:

http://www.excelforum.com/excel-general/

good luck!

mtfbwya


Asinus asinum fricat
Astrotoy7 is offline   you may: quote & reply,
Old 08-11-2010, 06:09 AM   #3
jonathan7
Exiled Jedi...
 
jonathan7's Avatar
 
Status: Moderator
Join Date: Jun 2006
Location: Ivory Tower
Posts: 3,676
Contest winner - Modding LFN Staff Member Veteran Modder Helpful! 
Quote:
Originally Posted by Aristotélēsticus View Post
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/ex...001118958.aspx



"Love is the only reality and it is not a mere sentiment. It is the ultimate truth that lies at the heart of creation." - Rabindranath Tagore

"Many a doctrine is like a window pane. We see truth through it but it divides us from truth." - Kahlil Gibran
jonathan7 is offline   you may: quote & reply,
Old 08-11-2010, 07:14 AM   #4
Samuel Dravis
 
Samuel Dravis's Avatar
 
Join Date: May 2002
Posts: 4,973
You probably want to use vLookUp somewhere in there. I can't write something for you right now since I'm going to work, but I'll look at it later.


"Words are deeds." - Wittgenstein
Samuel Dravis is offline   you may: quote & reply,
Old 08-12-2010, 02:48 AM   #5
Aristotélēsticus
Forumite
 
Aristotélēsticus's Avatar
 
Join Date: Jan 2005
Location: in the Source
Posts: 605
Thank you guys for your help.

Still didn't get what I want from the sources you gave me.

But thanks for trying. I appreciate it.
Aristotélēsticus is offline   you may: quote & reply,
Old 08-12-2010, 08:26 PM   #6
Samuel Dravis
 
Samuel Dravis's Avatar
 
Join Date: May 2002
Posts: 4,973
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
Code:
=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.


"Words are deeds." - Wittgenstein

Last edited by Samuel Dravis; 08-13-2010 at 10:44 PM.
Samuel Dravis is offline   you may: quote & reply,
Old 08-15-2010, 03:55 AM   #7
Aristotélēsticus
Forumite
 
Aristotélēsticus's Avatar
 
Join Date: Jan 2005
Location: in the Source
Posts: 605
Quote:
Originally Posted by Samuel Dravis View Post
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
Code:
=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.
Aristotélēsticus is offline   you may: quote & reply,
Old 08-15-2010, 08:53 AM   #8
Samuel Dravis
 
Samuel Dravis's Avatar
 
Join Date: May 2002
Posts: 4,973
Quote:
Originally Posted by Aristotélēsticus View Post
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.


"Words are deeds." - Wittgenstein

Last edited by Samuel Dravis; 08-15-2010 at 09:01 AM.
Samuel Dravis is offline   you may: quote & reply,
Old 08-15-2010, 12:07 PM   #9
Astrotoy7
A Face from The Past
 
Astrotoy7's Avatar
 
Join Date: Apr 2002
Posts: 10,284
Notable contributor Helpful! Folder extraordinaire LFN Staff Member 
Quote:
Originally Posted by Samuel Dravis View Post
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
>> 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

mtfbwya


Asinus asinum fricat
Astrotoy7 is offline   you may: quote & reply,
Old 08-19-2010, 03:24 AM   #10
Aristotélēsticus
Forumite
 
Aristotélēsticus's Avatar
 
Join Date: Jan 2005
Location: in the Source
Posts: 605
Astrotoy7's conclusion was correct. It is my fault after all so apologies.

Thank you for your efforts Samuel Dravis, and thank you all guys for your help, but judging from the dynamic nature of buyer_id, seller_id data, and the new requirements, guess a macro will become a must sooner or later.
Aristotélēsticus is offline   you may: quote & reply,
Post a new thread. Add a reply to this thread. Indicate all threads in this forum as read. Subscribe to this forum. RSS feed: this forum RSS feed: all forums
Go Back   LucasForums > Network > Knights of the Old Republic > Community > Ahto Spaceport Cantina > I need urgent help in Excel please

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 03:00 AM.

LFNetwork, LLC ©2002-2011 - All rights reserved.
Powered by vBulletin®
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.