SEARCH

The How-To Geek Forums Have Migrated to Discourse

How-To Geek Forums / Microsoft Office

IRS Audit and Excel worksheets

(9 posts)
  • Started 1 year ago by JeffreyFrost
  • Latest reply from moreeg
  • Topic Viewed 1160 times

JeffreyFrost
Posts: 6

I am in the middle of an audit by IRS and need a solution to an Excel problem. My corporate QuickBooks program created a 61 page Excel spreadsheet containing thousands of entries. I submitted this spreadsheet to IRS and the agent accepted the document with a random substantiation. I later discovered from my bookkeeper that the entries I used for the submission to IRS was incomplete and therefore the spreadsheet was erroneous. The correct entries were then inserted into QuickBooks and it produced a new corrected spreadsheet (64 pages), which was more favorable for my corporation and the new sheet was submitted to IRS. The IRS agent refuses to even consider the new spreadsheet unless I can specifically delineate each and every change made from the original submission.
Is there a methodology to combine both spreadsheets into one, eliminate duplicate entries and track the changes made by the second spreadsheet. The IRS agent stated that he spent more than enough time reviewing the original submission and is not going to go over a new 64 page submission. Truthfully, I don't blame him.
Jeff Frost

Posted 1 year ago
Top
 
warlock
warlock
Posts: 4100

http://www.howtogeek.com/forum.....?replies=2 You already have a thread started for this problem. Although this one explains it better. You should have just added to your other thread.

Posted 1 year ago
Top
 
moreeg
moreeg
Posts: 842

Hi Jeff

We will need still more information to provide specific help. Can you supply;

the number of columns
the column headers or name (e.g. Acct number, date, invoice # etc.)
what constitutes a unique row i.e. when you compare the 2 sheets which column(s) do you look at that tells you that row x in sheet1 is the same as row y in sheet2 and ignoring for the moment the values that may be different
what changes between the 2 sheets - is it the value in a single column or could it be the value in one of several columns

If you can answer these questions then a new spreadsheet can be constructed where the same records from each spreadsheet are placed side by side and the difference in values of each record is highlighted.

Posted 1 year ago
Top
 
JeffreyFrost
Posts: 6

Hi Moreeg;
Thank you for your quick reply to my dilemma request. I caught a touch of the flu and therefore could not respond to your request for more info immediately.
Both sheets make use of a total of 26 columns however 10 columns (H,I,K,M,O,Q,S,U,W, & Y) along with their respective rows act only as separators from other columns. We therefore have 15 active columns. Columns A through G are names of various Income & Expense items such as sales from cash, credit cards, broad categories of expenses, etc. 'A-G' are the same on both sheets. Column 'J' is titled Type and its rows are called Deposit, General Journal, check, payroll, etc. In Column 'J', we have noticeable differences between the sheets. ,'L' is labeled 'Date' and there are differences between sheets, 'N' is labeled Number however changes are insignificant. Column P is labeled Name, 'R' is labeled Memo, 'T' is labeled Clr,' and 'V' is labeled Split- changes to all these columns are of no significance to IRS. Column 'X' is labeled Amount is a dollar value in the both sheets' formula (either a plus or minus dollar figure). The last column 'Z' is the second part of the formula and changes with every entry in 'X'.
I thank you for any assistance you can give me.
Jeff

Posted 1 year ago
Top
 
moreeg
moreeg
Posts: 842

When there is a difference in Column J - Type how are you comparing the data between the 2 sheets to identify that it is the same account but with the difference? in other words what makes each row unique? is it any one of the cells from A to G or is it a combination of all the cells from A to G?

Posted 1 year ago
Top
 
JeffreyFrost
Posts: 6

I can tell there are changes in J because additional rows with different names appear in the row and new numbers are carried to Column X.

Posted 1 year ago
Top
 
moreeg
moreeg
Posts: 842

Hi Jeff

Sorry, but now I'm really confused. I don't understand what you mean when you say "additional rows with different names appear in the row".

How does a row appear in an existing row?

What I was expecting was that we would do a row by row check for differences. So my question was about identifying how Row X in sheet1 is the same as Row Y in sheet2 and then looking for the other elements in those 2 rows that are different. Here's how I was thinking we would do that;

Create a new Column A in each sheet and populate each row to identify its unique characteristics, For example, if the combination of the first 4 cells of each row is what identifies that row as unique then the cell in Col A would would enter the formula

= B2 & B3 & B4 &B5 ........... and copy this calculation to each row in each sheet

Having done this on each sheet I would then use a vlookup staring in Column AB of sheet2 that would bring the relevant data from sheet1 to its corresponding row in sheet2. It would look something like this

=IFERROR(Vlookup(A2,sheet1!A2:Z5000,11,FALSE),"NEW ROW") ....... this will bring the Type data (you're old column J) from sheet 1 to Sheet 2

Where a row in Sheet2 does not exist in Sheet1 the words "NEW ROW" would appear in cell AB2

You would do this for each data point that you want to compare e.g. to compare Dates in column L (now M because we added that column) the formula in Column AC would be.

=IFERROR(Vlookup(A2,sheet1!A2:Z5000,14,FALSE),"NEW ROW")

Copy these 2 new formulae down to the bottom and then use Conditional formatting to highlight where Sheets1 and Sheets 2 differ.

It might help if you posted a screen shot of a part of your spreadsheet. I understand that this is confidential information so you'd have to put in some dummy data but try to keep as much of the original data as you possibly can.

There are a few other Excel guys on the forum that may have a different approach (probably would) and who may have a better understanding of what your data looks like. Hopefully they will jump in to provide a better solution.

Posted 1 year ago
Top
 
JeffreyFrost
Posts: 6

Hi Moree:
Thanks for you assistance again. I tried to copy & paste a page of one of the worksheets to this reply. It just does not fit or format correctly. I will try your suggestions and again appreciate the time you have put into this.
Jeff

Posted 1 year ago
Top
 
moreeg
moreeg
Posts: 842

Hi Jeff

Here are instructions on how to post screenshots ....

Posted 1 year ago
Top
 



Topic Closed

This topic has been closed to new replies.

Enter Your Email Here to Get Access for Free:

Go check your email!