Help needed in making Pivot Table

[dvm49]

I made a pivot chart comparing product quantity from two different warehouses. But the problem is some products in WAREHOUSE_2 may not be available in WAREHOUSE_1 or vice versa.. But I need to capture all products, if the product is not available in the other warehouse then in the Pivot Table I would like to mention it as "Not Available". Is this possible? I have attached what I have tried but the qty but the result seems to be incorrect.

 

[vletm]

Something like below; Empty cells as: ...

or
Show - what is Your expected result?

 

[dvm49]

But the pivot seems to be taking exactly the same row result, if you take a look at the attached screenshot..

If you take a look at the value `3463213` it belongs to SILKGROW_HAIR_SERUM however this value has got mapped with AQUAPURE_SHOWER_FILTER which is wrong..

 

[vletm]

dvm49​

If You look - the way have You built Your pivot-table then You'll get as You've written.

As I wrote:
or
Show - what is Your expected result?
Did You show it?

 

[dvm49]

Hello @vletm,

I have manually made the mapping, please find the attached file.. Let me know if that helps?

Thanks,

 

[vletm]

dvm49​

I did something same about 24hrs ago...

 

[dvm49]

Thanks a lot and this helps, I have one last query. I see that you have used the below formula

=SORT(UNIQUE(A3:A59,FALSE))

And it works when all the items that is available in Warehouse1 is also available in Warehouse2.. But as I mentioned in my original post there could be a scenario where some product in Warehouse2 might not be present in Warehouse1 and in such scenarios most likely the formula won't capture the product from Warehouse2..

Do you have any inputs on how to handle this scenario?

 

[vletm]

That formula was one sample how to get all items from both Warehouses.
After that - no matter if there are missing something from any of warehouse ... other formula tries to find qtys from correct warehouse.
Your Do you have any inputs on how to handle this scenario?
Could You show ... what kind of scenario You have in Your mind?

 

[dvm49]

@vletm can you please take a look at the attached excel sheet (i.e C40), so in this case there is a Product that is listed in WAREHOUSE_2 but the same is not available in WAREHOUSE_1. Therefore how can we get this item in column J?

 

[vletm]

dvm49​

As I wrote That formula was one sample how to get all items from both Warehouses.
I noticed that You've added C40 & C41 to Your file ... okay?
You seems to notice that A59 is the last Product Name ... okay?
As in my sample - I've pasted Warehouse 2 manually in the end of Warehouse 1 and marked those with blue text ... okay?
... You've skipped that ... okay?
After that You've still kept J3 formula same ... which means missing Product Name.
... as well as K- and L-columns formulas seems to be same as before You've added that Product Name.
>>> I've given You an idea - how to do something?
... but as many times, ideas need to modify for use too. <<<

 

[dvm49]

Thank you once more for the detailed explanation. I realize I overlooked the text in blue. Apologies for that oversight.

 

[vletm]

dvm49​

Have You found a solution?
This way You could get those all Product Names for use ...
... but there are still to do with those QTYs-formulas.