Auto generate information from another sheet and more...

[sin2190]

Hey guys, so I'm kind of in need of a genius.
So I just got promoted to DM and theres just so much tedious work that needs to get done.
So basically I'm trying to create some type of template that will allow me to cross match IMEIs from Phones with a rebate report.
I think this can be done with a few functions and maybe 2 VBAs. Still a noobie but I know some.


So I have two sheet in one workbook.
1 Is a List of phone IMEIs
2 is the Rebate list.

So what I'd like to accomplish is.......
(When I write Sh1D for example it means Sheet 1 Cell D)

1. Find IMEI in (Sh1C) in IMEI (Sh2H)

• If Found, put "Paid" in Sh1D; Transaction Date from (Sh2K) in Transaction Date(Sh1E) ; Qualification Status from (Sh2N) in Qualification Status in (Sh1G)
• If not found, put "Nothing Received" in (Sh1D)

2. Also if an IMEI is Found during first search, Search Sheet 2 for a duplicate transaction using the Phone Number(Sh2G) in the same row of that IMEI. It is only required that I search a 30 days span from that transaction, if it isn't possible to narrow down the search for only 30 days that's fine. The rule is 1 record per 30 days before that is considered a duplicate, after that is fine.

• If one more is found put that row's Transaction Date (Sh2K) in Duplicate's Transaction Date in (Sh1J); Qualification Status from (Sh2n) in Qualification Status (Sh1K) and "Duplicate" in (Sh1I)
• If there are an additional 2 or more found within 30 days put "ISSUE!" in Action 2(Sh1I){That rarely happens so I could figure it out on my own}
• If there aren't any duplicates put "Valid" in Action 2(Sh1I)

I also have a Sample of how everything looks and the goal. I can send it to you, just show me how, because I cant add attachments for some reason.


So I'm thinking that the first step might require 1 VBA and the 2nd, another.
If anyone could help I'd REALLY appreciate it.

 

[pbornemeier]

Please use Excel Jeanie (see link in my sig) to post 15 or so rows from each of your worksheets with sample data that will allow testing of the criteria you mentioned above. Is sheet 2 sorted by transaction date from oldest to newest? Is data purged from either list? Under what circumstances?