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!!

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?

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).

Originally Posted by NBVC

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!

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?

Originally Posted by NBVC

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.

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...

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....