consolidating

  • Thread starter Thread starter David French
  • Start date Start date
D

David French

I have a situation where a credit card statement comes in and we have to
assign each line item a particular GL code. On one statement this GL code
may be used numerous times. The same GL code may also have credits as well
as debits.
The person writes on the statement what each line item will have for a GL
code.
From here we want to enter line by line each item in the statement on a
sheet in Excel.
When this data entry step is done we would like to have Excel do the
consolidation to another sheet so it then does the calculation which should
match the bottom line of the statement.
(pant, pant, pant...)
Oh yea...one more thing...if there are debits and credits for the same code
they need to appear in 2 separate places on the final sheet.

Please help.

Dave French
 
Easy peasy.
1. If you do a lot of this you might like to consider scanning the
sheets and using OCR software to convert to text in Excel. I do this
with my personal statement(s).

2. To get the plus/minus amount separation insert another column into
your worksheet with a formula something like :-
IF(C2<0,"minus","plus")

3. Then use a pivot table to do the consolidation.

4. Transfer the data from the pivot table to a normal worksheet by
using =VLOOKUP()

Regards
BrianB
=====================================================
 
Back
Top