lfnetwork.com mark read register faq members calendar

 08-11-2010, 03:28 AM #1 Aristotélēsticus @Aristotélēsticus Forumite     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. you may: quote & reply,
 08-11-2010, 03:57 AM #2 Astrotoy7 @Astrotoy7 A Face from The Past     Join Date: Apr 2002 Posts: 10,283 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 you may: quote & reply,
08-11-2010, 06:09 AM   #3
jonathan7
@jonathan7
Exiled Jedi...

Status: Moderator
Join Date: Jun 2006
Location: Ivory Tower
Posts: 3,680

Quote:
 Originally Posted by Aristotélēsticus 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
 08-11-2010, 07:14 AM #4 Samuel Dravis @Samuel Dravis     Join Date: May 2002 Posts: 4,980 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. you may: quote & reply,
 08-12-2010, 02:48 AM #5 Aristotélēsticus @Aristotélēsticus Forumite     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. you may: quote & reply,
 08-12-2010, 08:26 PM #6 Samuel Dravis @Samuel Dravis     Join Date: May 2002 Posts: 4,980 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. Last edited by Samuel Dravis; 08-13-2010 at 10:44 PM. you may: quote & reply,
08-15-2010, 03:55 AM   #7
Aristotélēsticus
@Aristotélēsticus
Forumite

Join Date: Jan 2005
Location: in the Source
Posts: 605
Quote:
 Originally Posted by Samuel Dravis 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.

08-15-2010, 08:53 AM   #8
Samuel Dravis
@Samuel Dravis

Join Date: May 2002
Posts: 4,980
Quote:
 Originally Posted by Aristotélēsticus 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?

Last edited by Samuel Dravis; 08-15-2010 at 09:01 AM.
08-15-2010, 12:07 PM   #9
Astrotoy7
@Astrotoy7
A Face from The Past

Join Date: Apr 2002
Posts: 10,283

Quote:
 Originally Posted by Samuel Dravis As for the download, try this. Not sure why the GoogleDocs one isn't working for you.

*when signed into my google account: works fine

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

mtfbwya

Asinus asinum fricat
 08-19-2010, 03:24 AM #10 Aristotélēsticus @Aristotélēsticus Forumite     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. you may: quote & reply,
 LucasForums > I need urgent help in Excel please

 Thread Tools Display Modes Linear Mode

 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 Rules
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home -------------------- Network     Star Wars Gamer     Knights of the Old Republic     Mixnmojo.com     Monkey Island Discussion     Brutal Legend Discussion     Grim Fandango Discussion     Psychonauts Discussion at Razputin's Domain     Sam And Max.Net     The Dig Museum     Full Throttle Discussion     IndyJones.net     Fracture     Forum Help & Feedback Center     LucasForums Archive         LEGO Star Wars series         Star Wars         Star Wars: Battlefront series         Star Wars: Classic Gaming         Star Wars: Empire At War         Star Wars: The Force Unleashed series         Star Wars: Galaxies         Star Wars: Jedi Knight series         Star Wars: The Old Republic         Star Wars: Republic Commando         Star Wars: Rogue Squadron series         Star Wars: X-Wing series