If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Find partial match from column A,B and fill partial match in C?
Hi there!
I have Spreadsheet 1 and Spreadsheet 2 with the same information but Spreadsheet 2 is missing the 'Vendor Name' information that I need. Spreadsheet 1 may also not contain the same number of rows that are in Spreadsheet 2 but for now its' the only spreadsheet I have to look for matching Vendor Names. I want to use Spreadsheet 1 to find the Vendor Name based on looking at it's related columns: Last Name, First Name and Invoice Number and Cheque Amount and fill Spreadsheet 1 column C with the missing information. Spreadsheet 1: A B C D 1 Last Name, First Name Invoice Number Vendor Name Cheque Amount 2 Ander, Joe 10000 Smith Enterprises 3500.00 3 Doe, Fawn 22 Lavender Corp. 1100.00 4 Johnson, Andrew D90304-1 Lavender Corp. 200.00 4 Taylor, Tyler 0000040 Taylor & Sons 60.00 5 Smith, John 100004 Smith Enterprises 540.00 Spreadsheet 2 A B C D 1 Last Name, First Name Invoice Number Vendor Name Cheque Amount 2 Taylor, Tyler 0000040 60.00 3 Smith, John 10000 3500.00 3 Lavender, Lila D90304-1 200.00 4 Lavender, Lila E90405-1 75.00 I have sorted both spreadsheets by column A. The problem that I think might heed any simple solution is that a vendor name can have different names related to it from column A with different cheque amounts in column D, that is why I want to look at column A,B,D for a match to fill in C. I am not familiar with using macros so if there is a formula I could use, any help is greatly greatly appreciated. |
#2
|
|||
|
|||
Find partial match from column A,B and fill partial match in C?
One way is to insert a col in sheet 1 just before Col C and enter this in the
new Col C =A1&B1&E1 and copy down till end of your data set Go to Sheet2 and enter this in Col C =VLOOKUP(A1&B1&D1,Sheet1!C,2,false) and copy down This way you will get the vendor names from Sheet 1 where Col A, B and C match with Col A, B and D in Sheet2... You will get #N/A when no match is found. You can Copy and PASTE SPECIAL|VALUE Col C in Sheet 2 when you are happy with the results... -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Tacrier" wrote: Hi there! I have Spreadsheet 1 and Spreadsheet 2 with the same information but Spreadsheet 2 is missing the 'Vendor Name' information that I need. Spreadsheet 1 may also not contain the same number of rows that are in Spreadsheet 2 but for now its' the only spreadsheet I have to look for matching Vendor Names. I want to use Spreadsheet 1 to find the Vendor Name based on looking at it's related columns: Last Name, First Name and Invoice Number and Cheque Amount and fill Spreadsheet 1 column C with the missing information. Spreadsheet 1: A B C D 1 Last Name, First Name Invoice Number Vendor Name Cheque Amount 2 Ander, Joe 10000 Smith Enterprises 3500.00 3 Doe, Fawn 22 Lavender Corp. 1100.00 4 Johnson, Andrew D90304-1 Lavender Corp. 200.00 4 Taylor, Tyler 0000040 Taylor & Sons 60.00 5 Smith, John 100004 Smith Enterprises 540.00 Spreadsheet 2 A B C D 1 Last Name, First Name Invoice Number Vendor Name Cheque Amount 2 Taylor, Tyler 0000040 60.00 3 Smith, John 10000 3500.00 3 Lavender, Lila D90304-1 200.00 4 Lavender, Lila E90405-1 75.00 I have sorted both spreadsheets by column A. The problem that I think might heed any simple solution is that a vendor name can have different names related to it from column A with different cheque amounts in column D, that is why I want to look at column A,B,D for a match to fill in C. I am not familiar with using macros so if there is a formula I could use, any help is greatly greatly appreciated. |
#3
|
|||
|
|||
Find partial match from column A,B and fill partial match in C
I am having trouble with the formula you suggested.
To make it a little easier, I have reformatted my information so that sheet 1 called 'Working Copy', and sheet 2 called 'Vendors' both have one col. that combines the Last Name,First Name col., Cheque # col. and $ Amount Col. using your 'A1&B1&E1' example. This new column is J2:J5480 on the 'Working Copy' and C4:C4436 on 'Vendors'. I would like to: 1. find a match between the J and C columns then 2. input the vendor name from 'Vendors' column H into 'Working Copy' column K Hoping my explanation of what I want to do is not too confusing. I have spent the last two days off and on trying to do this. ....Help? "Sheeloo" wrote: One way is to insert a col in sheet 1 just before Col C and enter this in the new Col C =A1&B1&E1 and copy down till end of your data set Go to Sheet2 and enter this in Col C =VLOOKUP(A1&B1&D1,Sheet1!C,2,false) and copy down This way you will get the vendor names from Sheet 1 where Col A, B and C match with Col A, B and D in Sheet2... You will get #N/A when no match is found. You can Copy and PASTE SPECIAL|VALUE Col C in Sheet 2 when you are happy with the results... -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Tacrier" wrote: Hi there! I have Spreadsheet 1 and Spreadsheet 2 with the same information but Spreadsheet 2 is missing the 'Vendor Name' information that I need. Spreadsheet 1 may also not contain the same number of rows that are in Spreadsheet 2 but for now its' the only spreadsheet I have to look for matching Vendor Names. I want to use Spreadsheet 1 to find the Vendor Name based on looking at it's related columns: Last Name, First Name and Invoice Number and Cheque Amount and fill Spreadsheet 1 column C with the missing information. Spreadsheet 1: A B C D 1 Last Name, First Name Invoice Number Vendor Name Cheque Amount 2 Ander, Joe 10000 Smith Enterprises 3500.00 3 Doe, Fawn 22 Lavender Corp. 1100.00 4 Johnson, Andrew D90304-1 Lavender Corp. 200.00 4 Taylor, Tyler 0000040 Taylor & Sons 60.00 5 Smith, John 100004 Smith Enterprises 540.00 Spreadsheet 2 A B C D 1 Last Name, First Name Invoice Number Vendor Name Cheque Amount 2 Taylor, Tyler 0000040 60.00 3 Smith, John 10000 3500.00 3 Lavender, Lila D90304-1 200.00 4 Lavender, Lila E90405-1 75.00 I have sorted both spreadsheets by column A. The problem that I think might heed any simple solution is that a vendor name can have different names related to it from column A with different cheque amounts in column D, that is why I want to look at column A,B,D for a match to fill in C. I am not familiar with using macros so if there is a formula I could use, any help is greatly greatly appreciated. |
#4
|
|||
|
|||
Find partial match from column A,B and fill partial match in C
Column with Name (your col J on Vendors) has to be on the right of Col H
(which has the combined data). To get this copy Col H and PASTE SPECIAL|Values in Col I then use the following formula in K2 of Working Copy =VLOOKUP(J2,Vendors!H2:I5480,2,false) and copy it down to the end of your data set... If it still does not work then can you send the file to me to get my id add @hotmail.com to to_sheeloo "Tacrier" wrote: I am having trouble with the formula you suggested. To make it a little easier, I have reformatted my information so that sheet 1 called 'Working Copy', and sheet 2 called 'Vendors' both have one col. that combines the Last Name,First Name col., Cheque # col. and $ Amount Col. using your 'A1&B1&E1' example. This new column is J2:J5480 on the 'Working Copy' and C4:C4436 on 'Vendors'. I would like to: 1. find a match between the J and C columns then 2. input the vendor name from 'Vendors' column H into 'Working Copy' column K Hoping my explanation of what I want to do is not too confusing. I have spent the last two days off and on trying to do this. ...Help? "Sheeloo" wrote: One way is to insert a col in sheet 1 just before Col C and enter this in the new Col C =A1&B1&E1 and copy down till end of your data set Go to Sheet2 and enter this in Col C =VLOOKUP(A1&B1&D1,Sheet1!C,2,false) and copy down This way you will get the vendor names from Sheet 1 where Col A, B and C match with Col A, B and D in Sheet2... You will get #N/A when no match is found. You can Copy and PASTE SPECIAL|VALUE Col C in Sheet 2 when you are happy with the results... -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Tacrier" wrote: Hi there! I have Spreadsheet 1 and Spreadsheet 2 with the same information but Spreadsheet 2 is missing the 'Vendor Name' information that I need. Spreadsheet 1 may also not contain the same number of rows that are in Spreadsheet 2 but for now its' the only spreadsheet I have to look for matching Vendor Names. I want to use Spreadsheet 1 to find the Vendor Name based on looking at it's related columns: Last Name, First Name and Invoice Number and Cheque Amount and fill Spreadsheet 1 column C with the missing information. Spreadsheet 1: A B C D 1 Last Name, First Name Invoice Number Vendor Name Cheque Amount 2 Ander, Joe 10000 Smith Enterprises 3500.00 3 Doe, Fawn 22 Lavender Corp. 1100.00 4 Johnson, Andrew D90304-1 Lavender Corp. 200.00 4 Taylor, Tyler 0000040 Taylor & Sons 60.00 5 Smith, John 100004 Smith Enterprises 540.00 Spreadsheet 2 A B C D 1 Last Name, First Name Invoice Number Vendor Name Cheque Amount 2 Taylor, Tyler 0000040 60.00 3 Smith, John 10000 3500.00 3 Lavender, Lila D90304-1 200.00 4 Lavender, Lila E90405-1 75.00 I have sorted both spreadsheets by column A. The problem that I think might heed any simple solution is that a vendor name can have different names related to it from column A with different cheque amounts in column D, that is why I want to look at column A,B,D for a match to fill in C. I am not familiar with using macros so if there is a formula I could use, any help is greatly greatly appreciated. |
#5
|
|||
|
|||
Find partial match from column A,B and fill partial match in C
Thank you that worked great!!
"Sheeloo" wrote: Column with Name (your col J on Vendors) has to be on the right of Col H (which has the combined data). To get this copy Col H and PASTE SPECIAL|Values in Col I then use the following formula in K2 of Working Copy =VLOOKUP(J2,Vendors!H2:I5480,2,false) and copy it down to the end of your data set... If it still does not work then can you send the file to me to get my id add @hotmail.com to to_sheeloo "Tacrier" wrote: I am having trouble with the formula you suggested. To make it a little easier, I have reformatted my information so that sheet 1 called 'Working Copy', and sheet 2 called 'Vendors' both have one col. that combines the Last Name,First Name col., Cheque # col. and $ Amount Col. using your 'A1&B1&E1' example. This new column is J2:J5480 on the 'Working Copy' and C4:C4436 on 'Vendors'. I would like to: 1. find a match between the J and C columns then 2. input the vendor name from 'Vendors' column H into 'Working Copy' column K Hoping my explanation of what I want to do is not too confusing. I have spent the last two days off and on trying to do this. ...Help? "Sheeloo" wrote: One way is to insert a col in sheet 1 just before Col C and enter this in the new Col C =A1&B1&E1 and copy down till end of your data set Go to Sheet2 and enter this in Col C =VLOOKUP(A1&B1&D1,Sheet1!C,2,false) and copy down This way you will get the vendor names from Sheet 1 where Col A, B and C match with Col A, B and D in Sheet2... You will get #N/A when no match is found. You can Copy and PASTE SPECIAL|VALUE Col C in Sheet 2 when you are happy with the results... -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Tacrier" wrote: Hi there! I have Spreadsheet 1 and Spreadsheet 2 with the same information but Spreadsheet 2 is missing the 'Vendor Name' information that I need. Spreadsheet 1 may also not contain the same number of rows that are in Spreadsheet 2 but for now its' the only spreadsheet I have to look for matching Vendor Names. I want to use Spreadsheet 1 to find the Vendor Name based on looking at it's related columns: Last Name, First Name and Invoice Number and Cheque Amount and fill Spreadsheet 1 column C with the missing information. Spreadsheet 1: A B C D 1 Last Name, First Name Invoice Number Vendor Name Cheque Amount 2 Ander, Joe 10000 Smith Enterprises 3500.00 3 Doe, Fawn 22 Lavender Corp. 1100.00 4 Johnson, Andrew D90304-1 Lavender Corp. 200.00 4 Taylor, Tyler 0000040 Taylor & Sons 60.00 5 Smith, John 100004 Smith Enterprises 540.00 Spreadsheet 2 A B C D 1 Last Name, First Name Invoice Number Vendor Name Cheque Amount 2 Taylor, Tyler 0000040 60.00 3 Smith, John 10000 3500.00 3 Lavender, Lila D90304-1 200.00 4 Lavender, Lila E90405-1 75.00 I have sorted both spreadsheets by column A. The problem that I think might heed any simple solution is that a vendor name can have different names related to it from column A with different cheque amounts in column D, that is why I want to look at column A,B,D for a match to fill in C. I am not familiar with using macros so if there is a formula I could use, any help is greatly greatly appreciated. |
Thread Tools | |
Display Modes | |
|
|