Need to match data on 2 sheets and merge matching

[innovativesmoking]

Ok my mom has a small business and she is trying to call all of her customers who have spent a certain amount of money and have not been back in a certain time period. The system wont put the data needed on the same report so she has 2 reports she has to cross reference, and that is very time consuming.

What I want to do is make a template she can put both reports into, it will check the first and last name (2 seperate cells) and when the match is found on the second sheet, it would pull the data from both sheets and merge it into one. Example:

Sheet 1

A B C D

first last phone amount spent




Sheet 2

A B C D

first last phone Last visit


Sheet 3 would have the info merged and would have all the data from both sheets, so row E would then have the data from row D on one of the other sheets. 5 rows rather than 4. In the real sheets there is first, last, street, city, state, zip, email, phone, amount spent on one report and all the same except for amount spent its last visit.

After the template is made I will then need it to have a if statement that if they spend over $100 and they have not been into the store in over 90 days it would highlight the name. This would tell her who her best customers were who have not been back in awhile and she is going to call them and offer them a free sample of a new product to come back in.

This is above my skill set and from the reading here I have done I think this is certainly possibly by many of you experts.

Thank You in advance!

Jason

[Caconz]

What you need to look at is probably the vlookup function to 'merge' the data into one sheet. If your customers have a customer ID or other unique number (maybe phone number) that is in column A then you can use that as your lookup value

Then it is really simple to see who has not purchased in the past 90 days
If you put the date ninety days ago in cell F1, Amount in column C, date in column D then the formula would be: =AND(C6>100,D6<$F$1)
You can then use conditional formatting to make the 'TRUE' results really stand out
Or you can filter the sheet using autofilter so that only the 'TRUE' results show

Hope this helps