Excel Formula or access ?

[taxmom]

Hi, In the past you all have help me tremendiously.

I'm stuck again. I have a very unuserfriendly report that looks like this

State - ype - County/City - Invoice # - Ref # - Amount
UT - Tax Billed - Salt Lake - 073714 - 238025 - $10.82
UT - Taxable Sales - Utah - 073714 - 238025 - $983.75
UT - Tax Billed - Slt Lk Cnty 073714 - 238025 - 4.92
UT- taxable Sales Slt Lk Cnty 073714 - 238025 - 983.75
UT - Tax billed - Utah - 073714 - 238025 - 46.73

For sales tax reporting we need to know the state, city, county district
amounts. This would be simple if we could just sort by county/city, however
we cannot because the state tax piece only only shows the word "UTAH" it does
not give a city or county name. So we must sort by invoice #. This would be
ok if we only had a few invoices in each city such as Salt Lake but we have
have 40 or 50 invoice numbers in salt lake.

I need to find a way to keep all of the tax pieces that belong together, and
subtotal by city/county. I thought if I had a formula that could change the
state name "UTAH" to reflect the city for that invoice. This is the case for
all states. Each state is 10,000 or more lines. I need to somehow grouping
the information I need. I used the pivot table approach. However, since I
must sort by invoice I still have 10,000 lines. I want to sort by invoice to
get all the pieces then sort by county city. Any ideas of how I can do this?
something in Access perhaps? a formual in excel perhaps?

If you could please help with my dilema I would be eternally greatful.

 

[Barb Reinhardt]

Could you give us more information on what you want to see and the results
you expect (with an example). I started looking at it and realized there
were some missing pieces.

 

[taxmom]

I'm sorry it is difficult to explain.

I need to see all of the data that is there. I just need to see grouped
with all sales tax pieces together. The sales tax pieces would include

Salt lake Taxable 500.00
Salt lake Deductible 200
Salt lake city state tax 5.00
Salt lake city city tax 10.00
total tax 15.00

Unfortunately, with this report it will tell you the taxable, deductible tax
billed for each line. A line is state piece, city piece, county piece. If
the invoice is not taxable then you would only see deductible city,
deductible UTAH, deductible county

sample of a 3 consistant taxable invoices

UT - Tax Billed Salt lake city - 073714 - 238025 - 10.00
UT - Tax Billed Salt Lake city - 651439 - 612345 - 10.00
UT - Tax billed Salt Lake City - 432159 - 437854 - 10.00

Total Tax Bille Salt Lake City 30.00

UT - Tax Billed - UTAH - 073714 - 238025 - 5.00 - state tax piece
UT - Tax Billed - UTAH - 651439 - 612345 - 5.00 - state tax piece
UT - Tax billed - UTAH - 432159 - 437854 - 5.00 - state piece

Total Tax billed Salt Lake City - state tax piece 15.00

UT - Taxable sales - salt lake city - 073714 - 238025 - 500.00
UT - Taxable sales - salt lake city - 651439 - 612345 - 500.00
UT - Taxable Sales - salt lake city - 432159 - 437854 - 500.00

Total Taxable Sales Salt Lake City 1500.00

UT - Taxable sales - UTAH salt lake state piece - 073714 - 238025 - 500.00
UT - Taxable sales - UTAH salt lake state piece - 651439 - 612345 - 500.00
UT - Taxable Sales - UTAH salt lake state piece - 432159 - 437854 - 500.00

The problem is the "UTAH" state piece if you sort by city/county it will
group with all other invoices and be unidentifiable to which city or county
the "state" piece belongs to.

Does this help?

Thank you so much for your help.

 

[Barb Reinhardt]

Let's say your data is in the columns shown below:

A B C D E F
State - ype - County/City - Invoice # - Ref # - Amount
UT - Tax Billed - Salt Lake - 073714 - 238025 - $10.82
UT - Taxable Sales - Utah - 073714 - 238025 - $983.75
UT - Tax Billed - Slt Lk Cnty 073714 - 238025 - 4.92
UT- taxable Sales Slt Lk Cnty 073714 - 238025 - 983.75
UT - Tax billed - Utah - 073714 - 238025 - 46.73

I'm assuming your data is in rows 3-7 for this example.

Let's say we have the following values
B20: Tax Billed
C20: Utah
D20: =SUM(IF(B3:B7=B20,IF(C3:C7=C20,F3:F7)))
make sure you commit the array formula with CTRL SHIFT ENTER. It will
have {} around it when you are done.

 

[taxmom]

Interesting formula I have not used before.
It returns the state piece of 46.73 for salt lake city. However, how can I
use this to identify that the Utah is for salt lake or how I can find all of
the salt lake city groups within the 10,000 lines to total them together.
There of course is many different cities in Utah.

This has gotten me thinking though. Is there a formula that will look the
county city "Salt Lake" and pull all of the columns B with the city name of
Salt lake, have it return the invoice # and amount and type.

Then a formula that maybe match the invoice #'s from the Salt Lake list to
find the tax billed "Utah" and Taxalbe Sales "Utah" that corralates.

Or maybe I can use this formula just change the "Utah" to the city name and
could I have it also return column D as well as column F?

Two steps, and I would have to do this with every city/county name.

Thanks this is a good start. Thanks so much

 

[Kevin Vaughn]

It was hard for me to follow this and I wasn't sure if Barb had answered your
question, so I'll throw this out there. I don't know how difficult it would
be, but if you could set up a lookup table with Invoice numbers and their
corresponding city/county, then you could use another column with a formula
to look up the city/county and then sort on that column.

 

[taxmom]

Because the list is so long I think this is what I might need. How would I
set up the lookup table?

I know the Vlookup has to have the lookup data on the first column and in
order.

or Could I use the lookup to lookup the city/county first and get them
sorted together and then back and do a lookup to lookup on the invoice
numbers that match the city and counties I'm looking for?

How would I have to sort the data to use the lookup?

Next month the invoices numbers will be different so I'm looking for a
formula I can make small modified changes to. The cities and counties will
always be typed the same because it s system generated.


Thanks so much for your help. I feel I'm getting so close to resolving this
mess. thank you, thank you.

 

[Kevin Vaughn]

Sorry I don't have time to go into any detail at all right now so I'll just
leave you with a very quick and dirty formula that should get you started in
the right direction with the possibility that others will fill in gaps. Here
is my data

651439 Salt Lake 238025 Salt Lake
432159 Salt Lake
105 Vegas
376 Houston
909 New York
238025 Salt Lake
612345 Salt Lake
437854 Salt Lake

The Formula in E14 (just where I happened to put the data) is:

=INDEX($B$14:$B$21,MATCH(D14,$A$14:$A$21,0))

If I had more time, I would address more of your questions, but gotta go!

 

[taxmom]

Thank you so much both formulas I received here I will be able to use -

You all are the greatest ! Thank you!

 

[Kevin Vaughn]

You are welcome. Glad it helped.