+ Reply to Thread
Results 1 to 11 of 11

Completing Tax tables

  1. #1
    Registered User
    Join Date
    02-16-2011
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2007
    Posts
    13

    Question Completing Tax tables

    Good Morning all, For the last 2 days I have tried to figure this out. At this point I realize I could have copy and pasted what I needed and just been done with it. But, I refuse. I really want to figure it out. Now, of course my boss is starting to look at me kinda funny. It's simple really. We have 233 Stores that use our Software. We identify them by a 6 digit number called an EFIN. When I run reports from Drake I run "All EFINS" and as of last night had 14,128 "IRS Accepted Returns" So, lets say EFIN 382630 may have 50 returns listed. The problem I have is that the 233 Stores identify themselves with "Store Numbers", "Store Number 16, Store Number 68, etc" I have been running the report, adding a column and manually entering in the Store Numbers. Is there a better way to do this? Is there a formula that will look at all 14,128 Returns and match the EFIN with the Store Number? I hope I am making sense - I stayed up very late trying to figure it out. I think I have become obsessed. Anyone awake this morning who would like to point me in the right direction I would very grateful.

    I have attached the file for yall to see my latest attempt. Thanks again for any advice.

    Note: I use Microsoft Excel at work but at home have Mac. The attachment is my work last night on Numbers converted to Excel. When I get to work this morning I can post the Spreadsheets created in Excel. I'm sorry I did not mention that earlier. *hand to forehead* thanks yall!!
    Attached Files Attached Files
    Last edited by NBVC; 02-17-2011 at 04:13 PM. Reason: Clarification

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Tax Season Help (tried v-lookup, if, match)

    Difficult to follow which sheet you are trying to populate and from what...

    guessing you are populating column B of Sheet2 and looking at first 2 columns in Sheet1, then perhaps:

    =INDEX('Sheet1 - Table 3 - Table 1'!A:A,MATCH(A2,'Sheet1 - Table 3 - Table 1'!B:B,0))

    copied down?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    02-16-2011
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2007
    Posts
    13

    Question Re: Completing Tax tables

    Thank you for such a quick response I want to have two sheets. One Master, generally it would include fees, IRS acceptance date, Date submitted, total returns per EFIN Number. I want to use the EFIN numbers (which is an IRS identifying number) and match that number to our Store Numbers (233 in total and not 1 through 233) When I run the report in Drake (tax software) it looks like this:

    EFIN
    323689
    323689
    323689
    323689
    678234
    678234
    209672

    All the data is listed to the right of each EFIN. It was easy in the beginning to go in and add "Store 235" next to EFIN 323689 and drag down until the next EFIN and do the same with the corresponding Store Number. But, now at over 14,000 Returns I run the report and wanna burst into tears.

    Thank you again for such a fast response. I will try your formula when I get to work and report back.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Completing Tax tables

    If the formula is not what you were looking for, then I suggest you post back a sample of what your result should look like and explain what are the givens and what are the expected results (and how you get them)... this will help us to help you (especially given we are not all financial gurus.. but there are some luckily that can possibly understand better your dilemma).

  5. #5
    Registered User
    Join Date
    02-16-2011
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2007
    Posts
    13

    Unhappy Re: Completing Tax tables

    Quote Originally Posted by NBVC View Post
    If the formula is not what you were looking for, then I suggest you post back a sample of what your result should look like and explain what are the givens and what are the expected results (and how you get them)... this will help us to help you (especially given we are not all financial gurus.. but there are some luckily that can possibly understand better your dilemma).
    I think part of the problem, well, actually, the problem is that I don't know exactly what formula I need. The math formulas are a bit easier but these has me stuck. I would like a formula that says, "if" this number (EFIN number) is on the spreadsheet it should correspond in the column next to it with its corresponding Store Number. When listing our EFIN and Store Numbers alone it is very easy. 233 Stores with 233 Matching EFIN numbers. The problem arises because our tax software does not have our Store numbers uploaded, only EFINS. So, when I run a report for IRS Accepted Returns one EFIN is listed numerous times but no Store number. I have been manually going through the report and adding the Store number next to the EFIN number. But, now there are over 13,000 IRS Accepted returns. *sigh*

    The good news is that I never realized how much fun these formulas can be and all the things they can do. I love it. I just finally get it! When a formula comes out right its the BEST feeling!

    Any assistance is much appreciated and I will help yall out too in anyway that I can. Thank yall so much!
    Attached Files Attached Files
    Last edited by Paperdoll; 02-17-2011 at 01:45 PM. Reason: To add an Attachment

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Completing Tax tables

    For A2:

    =VLOOKUP(B2,'Store Number EFIN'!A:B,2,0)

    copied down gets the store Numbers... but I don't understand column C.. is it the same number as column A?

  7. #7
    Registered User
    Join Date
    02-16-2011
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2007
    Posts
    13

    Smile Re: Completing Tax tables

    Quote Originally Posted by NBVC View Post
    For A2:

    =VLOOKUP(B2,'Store Number EFIN'!A:B,2,0)

    copied down gets the store Numbers... but I don't understand column C.. is it the same number as column A?
    In Column C I will add the taxpayers name. For A2 should I paste it on the first worksheet or the second worksheet? Is there a way to add to the VLOOKUP that you provided that will also count how many times that EFIN is in the report to find "Total Bank Products" per EFIN for the second worksheet Column C?

    Thank you again for your help :-) I appreciate it so much.
    Last edited by NBVC; 02-17-2011 at 02:10 PM.

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Completing Tax tables

    That formula would go in A2 of "IRS Accepted" sheet.

    In the "Store Number EFIN" sheet, in C2 enter: =COUNTIF('IRS Accepted'!B:B,A2)

    and copy down the column

    Then in D2 enter: =SUMIF('IRS Accepted'!$B:$B,$A2,'IRS Accepted'!F:F)

    copy across the columns and then copy all down the rows...

  9. #9
    Registered User
    Join Date
    02-16-2011
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Completing Tax tables

    I will enter the formulas and report back with results. Thank you again, you are very helpful!

  10. #10
    Registered User
    Join Date
    02-16-2011
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Completing Tax tables

    Quote Originally Posted by NBVC View Post
    That formula would go in A2 of "IRS Accepted" sheet.

    In the "Store Number EFIN" sheet, in C2 enter: =COUNTIF('IRS Accepted'!B:B,A2)

    and copy down the column

    Then in D2 enter: =SUMIF('IRS Accepted'!$B:$B,$A2,'IRS Accepted'!F:F)

    copy across the columns and then copy all down the rows...
    You are insanely brilliant!! Thank you SO much! How on Gods green did you do that? If you would and if you have time, could you tell me how you came to the conclusion? I started out trying the VLOOKUP, but spent most of the last 48 hours on "MATCH" "IF" "AND" -could any of those worked?

    eeeeeee! You totally made my day!
    Attached Files Attached Files

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Completing Tax tables

    Thank you for your compliments, but really those are just standard Excel formulas. You can look up VLOOKUP, COUNTIF and SUMIF functions in Excel's help files and understand how they each work along with examples that would hopefully allow you to understand how I came up with those solutions.

    All 3 are lookup functions of sorts.

    Vlookup looks for the first match to a lookup value and finds it in a defined range and pulls data in corresponding row from specified column.

    Countif counts based on the condition that a range has the values you are looking for

    and Sumif is very much like Countif, but allows you to sum corresponding values in where the matches are found....

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1