Company Corporate Audit does analytics for Time and Expense auditing. In the audits, we’d like to use a list of key words to check against Concur transactions to identify line items that may have exceptions that require investigation/follow-up. We’d like to use the keyword list recursively in the “purpose” string field until one of two things happen 1) No exceptions found for each row 2) Exception found and a concatenated string is put into “exception” field added to the end of each row.
Example:
Time Expense Row of data
Transaction ID, User, Type, Purpose, Currency, Amount
1, droe, Taxi, birthday present, USD, 100000000000
2, droe, Misc, bonus, USD, 100000000
3, droe, Hotel Tax, whisky bottle, USD, 10000000
Exception term list
ID, Exception Term
1, Present
2, Whisky
3, Bonus
4, Bottle
The desired outcome: For each row in transaction list loop though exception term list and if there is a match, concatenate the exception term into a field “Corporate Audit Exception”.
Example:
3, droe, hotel tax, whisky bottle, USD, 100000, “Whisky/Bottle”
I’m thinking of doing this in a macro that can batch this against the entire table at once per term but need help with the macro design.
