A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Find partial match from column A,B and fill partial match in C?



 
 
Thread Tools Display Modes
  #1  
Old October 17th, 2008, 10:10 PM posted to microsoft.public.excel.misc
Tacrier
external usenet poster
 
Posts: 42
Default 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  
Old October 18th, 2008, 01:01 AM posted to microsoft.public.excel.misc
Sheeloo[_3_]
external usenet poster
 
Posts: 1,713
Default 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  
Old October 22nd, 2008, 06:26 PM posted to microsoft.public.excel.misc
Tacrier
external usenet poster
 
Posts: 42
Default 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  
Old October 23rd, 2008, 05:16 AM posted to microsoft.public.excel.misc
Sheeloo[_3_]
external usenet poster
 
Posts: 1,713
Default 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  
Old October 24th, 2008, 11:24 PM posted to microsoft.public.excel.misc
Tacrier
external usenet poster
 
Posts: 42
Default 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

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

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:34 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.