I have two lists in separate files. They look something like this:

Month Product Qty Price Total
Jan Apples 54 3 162
Jan Oranges 21 3 63
Jan Plums 54 3 162
Jan Peaches 14 3 42
Jan Nectarines 24 3 72
Jan Bananas 64 3 192


Month Product Qty Price Total
Feb Bananas 5 2 10
Feb Oranges 3 2 6
Feb Plums 26 2 52
Feb Honeydew 54 2 108
Feb Watermelon 24 2 48


In my final product, I need to compare the two lists, but the twist is - I
need to add lines that show each product, so even if Jan doesn't have
Honeydew, I need to show a line in Jan that shows Honeydew with a Qty of 0.

So far, I've done a If(countif(range, criteria)1,True,False) to flag the
duplicates, then sort to group them together.

Should I now run a bit of code that inserts blank rows between each row,
then copy the content of the cell above each cell down?

I guess I could then split manually to see the data side-by-side, or use a
pivot table.

Any suggestions would be greatly appreciated!

--
Thanks!

Dee

Hi Dee

Just copy list 2 (minus the header row) and paste directly under List1.
Click a cell in the List, DataPivot TableNextFinish
Drag Month to the Row area
Drag Product to the Row area
Drag Qty, Price and Total to the data area.

Drag Data tot he Total column to show these side by side.

Double click on Product field and select show items with no data.

--
Regards

Roger Govier


"dee" wrote in message
...
I have two lists in separate files. They look something like this:

Month Product Qty Price Total
Jan Apples 54 3 162
Jan Oranges 21 3 63
Jan Plums 54 3 162
Jan Peaches 14 3 42
Jan Nectarines 24 3 72
Jan Bananas 64 3 192


Month Product Qty Price Total
Feb Bananas 5 2 10
Feb Oranges 3 2 6
Feb Plums 26 2 52
Feb Honeydew 54 2 108
Feb Watermelon 24 2 48


In my final product, I need to compare the two lists, but the twist
is - I
need to add lines that show each product, so even if Jan doesn't have
Honeydew, I need to show a line in Jan that shows Honeydew with a Qty
of 0.

So far, I've done a If(countif(range, criteria)1,True,False) to flag
the
duplicates, then sort to group them together.

Should I now run a bit of code that inserts blank rows between each
row,
then copy the content of the cell above each cell down?

I guess I could then split manually to see the data side-by-side, or
use a
pivot table.

Any suggestions would be greatly appreciated!

--
Thanks!

Dee

Thank you so much. Worked like a charm!

You're a genius! :-)
--
Thanks!

Dee


"Roger Govier" wrote:

Hi Dee

Just copy list 2 (minus the header row) and paste directly under List1.
Click a cell in the List, DataPivot TableNextFinish
Drag Month to the Row area
Drag Product to the Row area
Drag Qty, Price and Total to the data area.

Drag Data tot he Total column to show these side by side.

Double click on Product field and select show items with no data.

--
Regards

Roger Govier


"dee" wrote in message
...
I have two lists in separate files. They look something like this:

Month Product Qty Price Total
Jan Apples 54 3 162
Jan Oranges 21 3 63
Jan Plums 54 3 162
Jan Peaches 14 3 42
Jan Nectarines 24 3 72
Jan Bananas 64 3 192


Month Product Qty Price Total
Feb Bananas 5 2 10
Feb Oranges 3 2 6
Feb Plums 26 2 52
Feb Honeydew 54 2 108
Feb Watermelon 24 2 48


In my final product, I need to compare the two lists, but the twist
is - I
need to add lines that show each product, so even if Jan doesn't have
Honeydew, I need to show a line in Jan that shows Honeydew with a Qty
of 0.

So far, I've done a If(countif(range, criteria)1,True,False) to flag
the
duplicates, then sort to group them together.

Should I now run a bit of code that inserts blank rows between each
row,
then copy the content of the cell above each cell down?

I guess I could then split manually to see the data side-by-side, or
use a
pivot table.

Any suggestions would be greatly appreciated!

--
Thanks!

Dee

I hate to be a pest, but just encountered another issue.

I succeeded in having my data nicely laid out, showing the Qty and Value for
each month. Now, what I would like to do is add to extra columns, so show
the difference of Qty from one month to next (current month minus last month)
and also for Value.

Is this possible?

--
Thanks!

Dee


"Roger Govier" wrote:

Hi Dee

Just copy list 2 (minus the header row) and paste directly under List1.
Click a cell in the List, DataPivot TableNextFinish
Drag Month to the Row area
Drag Product to the Row area
Drag Qty, Price and Total to the data area.

Drag Data tot he Total column to show these side by side.

Double click on Product field and select show items with no data.

--
Regards

Roger Govier


"dee" wrote in message
...
I have two lists in separate files. They look something like this:

Month Product Qty Price Total
Jan Apples 54 3 162
Jan Oranges 21 3 63
Jan Plums 54 3 162
Jan Peaches 14 3 42
Jan Nectarines 24 3 72
Jan Bananas 64 3 192


Month Product Qty Price Total
Feb Bananas 5 2 10
Feb Oranges 3 2 6
Feb Plums 26 2 52
Feb Honeydew 54 2 108
Feb Watermelon 24 2 48


In my final product, I need to compare the two lists, but the twist
is - I
need to add lines that show each product, so even if Jan doesn't have
Honeydew, I need to show a line in Jan that shows Honeydew with a Qty
of 0.

So far, I've done a If(countif(range, criteria)1,True,False) to flag
the
duplicates, then sort to group them together.

Should I now run a bit of code that inserts blank rows between each
row,
then copy the content of the cell above each cell down?

I guess I could then split manually to see the data side-by-side, or
use a
pivot table.

Any suggestions would be greatly appreciated!

--
Thanks!

Dee

Hi Dee

Right click on any cell on the PTWizardLayout
Drag a second copy of Qty to the data area, where it will become Qty2.
Double click it and choose OptionsShow data asuse the dropdown to
select
%Difference fromchoose Month as Base field in left pane(Previous) as
Base item in right paneOKOKFinish

You will now have DIV/#0 errors, for Honeydew and Watermelon, as they
didn't exits in the previous Month.
Right click on PTTable Optionstick For error values, showenter
whatever you want in the box e.g. 0 or n/a

--
Regards

Roger Govier


"dee" wrote in message
...
I hate to be a pest, but just encountered another issue.

I succeeded in having my data nicely laid out, showing the Qty and
Value for
each month. Now, what I would like to do is add to extra columns, so
show
the difference of Qty from one month to next (current month minus last
month)
and also for Value.

Is this possible?

--
Thanks!

Dee


"Roger Govier" wrote:

Hi Dee

Just copy list 2 (minus the header row) and paste directly under
List1.
Click a cell in the List, DataPivot TableNextFinish
Drag Month to the Row area
Drag Product to the Row area
Drag Qty, Price and Total to the data area.

Drag Data tot he Total column to show these side by side.

Double click on Product field and select show items with no data.

--
Regards

Roger Govier


"dee" wrote in message
...
I have two lists in separate files. They look something like this:

Month Product Qty Price Total
Jan Apples 54 3 162
Jan Oranges 21 3 63
Jan Plums 54 3 162
Jan Peaches 14 3 42
Jan Nectarines 24 3 72
Jan Bananas 64 3 192


Month Product Qty Price Total
Feb Bananas 5 2 10
Feb Oranges 3 2 6
Feb Plums 26 2 52
Feb Honeydew 54 2 108
Feb Watermelon 24 2 48


In my final product, I need to compare the two lists, but the twist
is - I
need to add lines that show each product, so even if Jan doesn't
have
Honeydew, I need to show a line in Jan that shows Honeydew with a
Qty
of 0.

So far, I've done a If(countif(range, criteria)1,True,False) to
flag
the
duplicates, then sort to group them together.

Should I now run a bit of code that inserts blank rows between each
row,
then copy the content of the cell above each cell down?

I guess I could then split manually to see the data side-by-side,
or
use a
pivot table.

Any suggestions would be greatly appreciated!

--
Thanks!

Dee