• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Look for correct value in different reports

Y

Yulechka

New Member
Hello,
I need some help with the right formula that will pull the right project code and task for the related credit card transactions.
For example, in one report, the credit card submitted with the following information in one report:
PROJECT #
TASK #
FY14-8000-OP
OH.MGT
There is another report with all project and tasks:
FY14-8000-OP

OH.LS
OH.MGT
Please advise what the best formula to verify and return the right value for project and tasks for for credit card transactions.
Thank you very much beforehand!
 
I'm not sure what you are asking exactly...
could you provide an example workbook, showing what you have currently, and what you need to see?
 
There is a system report with all data related to projects and there is the credit card transactions report where people booked their charges to the projects and tasks. I need to verify and pull correct project codes and tasks to upload entries into the acctng system. Sometimes, the numbers or characters are missed in the project code or tasks in the cc report and the system gives an error. I am trying to verify the data before upload into the system.
For instance, below is the system report and credit card report. If a project code is correct on the cc report comparing to the system report, i need to return as ie "FY14-8000-OP" and if there is an error, flag it somehow ie "error". The same is with the tasks, if there is a task under the project code. For instance, "OH.MGT" is not under "FY14-8000-OP", then flag it with ie "error", if there is the correct task such as "OH.HS" under "FY14-2000-OP", then return as "OH.HS".
System Report
Project Number Task Number
FY14-8000-OP OH
FY14-8000-OP OH.103
FY14-8000-OP OH.CLD
FY14-8000-OP OH.DA
FY14-8000-OP OH.DC
FY14-8000-OP OH.ERPSYS
FY14-8000-OP OH.HELP
FY14-8000-OP OH.INV
FY14-8000-OP OH.OR
FY14-8000-OP OH.PEX
FY14-8000-OP OH.PIX
FY14-8000-OP OH.RESDISC
FY14-8000-OP OH.SC
FY14-8000-OP OH.SEC
FY14-8000-OP OH.TRNG
FY14-8000-OP OH.UNGER
FY14-2000-OP OH.HAZSPILL
FY14-2000-OP OH.HS
FY14-2000-OP OH.OC
FY14-2000-OP OH.SFTYCOMM
FY14-2000-OP OH.WHS

Credit Card Report
PROJECT # TASK #
FY14-8000-OP OH.MGT
FY14-2000-OP OH.SFTYCOMM
FY14-2000-OP
OH.HS


Sorry for the confusing explanation, i am the beginner with excel and forum. ;)

Thanks again.
 
Wow, amazing! Thank you!
I have added two tabs, this is how my file looks like, system report on one tab and cc report on another. Now, how to add these formulas so the correct value for project code and task will be added to Check cols in CC report? I have used VLOOKUP before, but struggled with matching the tasks.
Thank you again.
 

Attachments

You must log in or register to reply here.
Back
Top