Search and fill the cell with certain content found in a different worksheet tab

[crossing365]

Hello,

I have and excel with hundreds of payments made by clients.  The excel doesn't have client address but only their name and their payment values. I need to generate invoices from every excel line which should contain :  Our company name, Client Name Surname, client address, service that we offer and payment value.   The problem is that the excel in question doesn't have client address because its exported from paypal. We have an excel with clients name/surname and address but we need somehow to copy address for each client on the new excel tab cell where there is the paypal exported excel. So we need to automatically search the name and if found to copy the address to the proper cell.

example:

excel with missing address from clients

A2 : contain name and surname in the same cell , B2 contains payment value.

excel with all clients name and addresses:

A2 contain name , B2 contain surnname , C2: client address

We need to somehow to search all excel with clients only for partial string for surname from exported excel with missing address , i.e. from A2 but only for the name and if found, copy C2 content into a new cell D3 on the excel with missing address. I need this to be done automatically on all lines in order to have the correct address for all the customers of exported paypal excel.

I found this but doesn't help me as I need to search for name found in cell and not my own string.

=IF(ISNUMBER(SEARCH("text",A1)),B1,"")

Thank you !

[#2]

What you are facing is far from an ideal situation. You could help us help you by giving a couple specific examples (maybe not real names, but mockups or representations of how name and surname appear in that first sheet (the one you get from PayPal), and a mockup or representation of how the corresponding two fields might appear in the other sheet.)  Do you have cases where the same customer appears multiple times on one or both of those two spreadsheets? How many rows of data are you getting from PayPal?

The problem you're going to have is that there are undoubtedly inconsistencies in spelling, as well as similar names, maybe even identical. Far better would be if you have customer IDs rather than relying solely on names.

What you'll need to be using is some version or other of VLOOKUP or one of the other lookup functions. I'll be happy to provide some examples of how that might work, but would prefer to work with a bit more information from you--as requested above--first.

[#3]

Hello @mathetes ,

Do you have cases where the same customer appears multiple times on one or both of those two spreadsheets?   Yes

How many rows of data are you getting from PayPal? Around 1000

I can see paypal file have a transaction ID column. Transaction ID can be found also on my exported transactions file together with client address. The problem comes with other payment methods like card payment and bitpay payments, for this cases I have adddress, city, and state but its missing Country. ON this 2 payment types Bitpay and credit card I can only use email as search criteria. Maybe we can use both search criteria (email and transaction number) , if any of this is found , then copy content from address cell (or country cell for bitpay and credicard) to another respective cell from other worksheet tab with the same client. If we only use email search will not work because some paypal clients are using different account emails on their paypal.

I shared sample excel where red text column must be filled with client info from the 4th worksheet tab here

If posssible... :

1. I also need some help to remove time from date column on credit card tab, for instance first cell has 6/5/2018 14:54
content ,while I only need 6/5/2018.

2. I also need to automatically generate a contract for all clients using their respective name and full address extracted from the resulted excel. Each client must have a single contract with the date of the contract to match the order date found for that user first dated order found in excel (the oldest payment date for that client). The contract will be the same for all users (same service description), only the name , client address and contract nr/date will be different. All this contracts name and numbers ideally must be generated and a 'contracts' folder and listed on a excel column and I can simply select the column and I can print them all .   Contract sample (can be in excel)

3. I need to generate invoices for each excel line as that is a paid order. Each invoice must have a default text with what I did to the customer(same exact text for all clients) + contract number and date token from point 2. Each client must have a single contract number/date on all invoices - the one generated point 2. The invoices can be stored in a invoices folder and be accessed on an excel invoice column created on the first shared example excel ( invoices and contract number can start from 1).   Invoice sample where red text must be filled with correct data.

Thank you !

[#4]

I won't be able to look at this till later today or tomorrow. Thank you for the sample file.

[#5]

Any news @mathetes ?

[#6]

@crossing365 

Sorry for the delay. Here's a quick example of a simple VLOOKUP solution. I named the range of data with the PayPal data, although that's not a necessary part of the formula. You do need to refer to the array of data and using a name is one way. 

There are other ways to pull data, and I highly recommend you familiarize yourself with some of them by going through this resource.  In particular, become familiar with VLOOKUP, XLOOKUP, INDEX and MATCH. But become familiar with the range of methods.

[#7]

Thank you so much for your help, i will go deeper on those resources!