I have a following table:
Name Qtr Rev$ Net$
New York 2007Q1 1,000 800
New York 2007Q2 2,000 500
New Jersey 2007Q1 1,200 600
New Jersey 2007Q2 1,500 800

I want to convert them in the below format based on the Name field.
THe date would be pulled from the top table.
2007Q1 2007Q2
Name Rev$ Net$ Rev$ Net$
New Jersey 1,200 600 1,500 800
GA
CA
New York

Does anyone have any idea? I've tried sumproduct function, but it
didn't work. thanks!

Why not a pivot table???
--
HTH...

Jim Thomlinson


" wrote:

I have a following table:
Name Qtr Rev$ Net$
New York 2007Q1 1,000 800
New York 2007Q2 2,000 500
New Jersey 2007Q1 1,200 600
New Jersey 2007Q2 1,500 800

I want to convert them in the below format based on the Name field.
THe date would be pulled from the top table.
2007Q1 2007Q2
Name Rev$ Net$ Rev$ Net$
New Jersey 1,200 600 1,500 800
GA
CA
New York

Does anyone have any idea? I've tried sumproduct function, but it
didn't work. thanks!

=sumproduct(--(a2:a15=a20)*(b2:b15=b18),(c2:c15))

but as Jim told you PT will be the best option

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



" escreveu:

I have a following table:
Name Qtr Rev$ Net$
New York 2007Q1 1,000 800
New York 2007Q2 2,000 500
New Jersey 2007Q1 1,200 600
New Jersey 2007Q2 1,500 800

I want to convert them in the below format based on the Name field.
THe date would be pulled from the top table.
2007Q1 2007Q2
Name Rev$ Net$ Rev$ Net$
New Jersey 1,200 600 1,500 800
GA
CA
New York

Does anyone have any idea? I've tried sumproduct function, but it
didn't work. thanks!

On Jun 2, 4:03*pm, Marcelo wrote:
=sumproduct(--(a2:a15=a20)*(b2:b15=b18),(c2:c15))

but as Jim told you PT will be the best option

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo

" escreveu:



I have a following table:
Name * * * * *Qtr * * * * * *Rev$ * * *Net$
New York * *2007Q1 * * 1,000 * * 800
New York * *2007Q2 * * 2,000 * * 500
New Jersey *2007Q1 * *1,200 * * 600
New Jersey *2007Q2 * *1,500 * * 800

I want to convert them in the below format based on the Name field.
THe date would be pulled from the top table.
* * * * * * * * * * * * * *2007Q1 * * * * * * * * * * * *2007Q2
Name * * * * * Rev$ * * * * Net$ * * * * * * Rev$ * * * * * Net$
New Jersey *1,200 * *600 * * * *1,500 * * * * * *800
GA
CA
New York

Does anyone have any idea? *I've tried sumproduct function, but it
didn't work. *thanks!- Hide quoted text -

- Show quoted text -

Marcelo, your formual works great but it does not compare the Qtr
field, so how do I insert that in your formula to complete the
formual? Also, the pivot doesn't work since the name would be based
on the Vlookup.

On Jun 2, 4:03*pm, Marcelo wrote:
=sumproduct(--(a2:a15=a20)*(b2:b15=b18),(c2:c15))

but as Jim told you PT will be the best option

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo

" escreveu:



I have a following table:
Name * * * * *Qtr * * * * * *Rev$ * * *Net$
New York * *2007Q1 * * 1,000 * * 800
New York * *2007Q2 * * 2,000 * * 500
New Jersey *2007Q1 * *1,200 * * 600
New Jersey *2007Q2 * *1,500 * * 800

I want to convert them in the below format based on the Name field.
THe date would be pulled from the top table.
* * * * * * * * * * * * * *2007Q1 * * * * * * * * * * * *2007Q2
Name * * * * * Rev$ * * * * Net$ * * * * * * Rev$ * * * * * Net$
New Jersey *1,200 * *600 * * * *1,500 * * * * * *800
GA
CA
New York

Does anyone have any idea? *I've tried sumproduct function, but it
didn't work. *thanks!- Hide quoted text -

- Show quoted text -

Marcelo, your formula works great but it left off comparing Rev$ and
Net$ fields. How do I insert that to complete your formula? Also,
pivot won't work to achieve what I'm trying to do becuase the name
field is based on Vlookup. thanks!