Merging 2 lists with a twist

[dee]

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!

 

[Roger Govier]

Hi Dee

Just copy list 2 (minus the header row) and paste directly under List1.
Click a cell in the List, Data>Pivot Table>Next>Finish
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.

 

[dee]

Thank you so much. Worked like a charm!

You're a genius!
--
Thanks!

Dee

Hi Dee

Just copy list 2 (minus the header row) and paste directly under List1.
Click a cell in the List, Data>Pivot Table>Next>Finish
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.

 

[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

Hi Dee

Just copy list 2 (minus the header row) and paste directly under List1.
Click a cell in the List, Data>Pivot Table>Next>Finish
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.

 

[Roger Govier]

Hi Dee

Right click on any cell on the PT>Wizard>Layout
Drag a second copy of Qty to the data area, where it will become Qty2.
Double click it and choose Options>Show data as>use the dropdown to
select
%Difference from>choose Month as Base field in left pane>(Previous) as
Base item in right pane>OK>OK>Finish

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

--
Regards

Roger Govier

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?