compare recurring item in monthly basis

[koi]

hi guys,


need a huge favor here, i have some item in each month, the item is just same item with selling quantity different each month.


the different is sometimes we received those item and sometimes we didnt received.


when i put all item in table then do the pivot table chart.. we can easily see the increase or decreasing there but then if someone asking can we compare same item we received last month with this month.. then i have a problem


right now i'm do it manually by add 1 cell then find(vlookup( but all i want is if we can find some automatic way to compare which month vs which month with based only for same item that we received in those both month


attached is the file, you can click slicers recurring and non recurring then you can find comparison same item last on feb & march..i cannot do that manually every time if someone ask : can we compare jan to feb? (then i have to do manual vlookup again )


please find attached file here, and just click big download on the top right to download it. http://www32.zippyshare.com/v/85204641/file.html


thanks all

 

[koi]

i think it cannot be done using formula, you will need to have 2 box then input manually month name into that box.. then press button so macro will do the vlookup against that 2 months then we can just refresh pivot table and using the slicer


is there anyone can do that kind of macro?


thanks for helping or if there is any other idea how to do it? but please still using pivot table since i want to use the slicer

 

[Faseeh]

Dear Koi,


I had been trying to download the attachment but it comes to be a .exe file? Have you uploaded an excel sheet or some thing else? Secondly i recommend you to use Dropbox.com or 2shared.com because it will start download as soon as i click the file.


Kindly go through the first three sticky posts forums home page. They will help you get a quicker reply.


Keep Posting!

Faseeh

 

[sgmpatnaik]

@koi

Hi


i agree with Faseeh, it is better to upload the sample files using with the below link


http://chandoo.org/forums/topic/posting-a-sample-workbook


off course i downloaded your file and i will try to solve your problem


Thanks


SP


@Faseeh


Hi


You can download the file with Koi link using with Download Now Button


Regards


SP

 

[NARAYANK991]

Hi Faseeh ,


You might have clicked the wrong button ! Here it is :


http://speedy.sh/UNbfd/compare-recurring-item.xlsx


Narayan

 

[koi]

Hi All,


thanks for trying, right now i do it manually but it is ok now


i make 1 pivot table only consist of 2 months that i need to compare..then i will make if(and(a1=1,b1=1),"yes","no")


yes meaning : item exist in both month


then in the actual data table i will do index & match then it will give me "yes" or "no"


then i only need to refresh my pivot table then slicer is working


at least only takes me like 5-10 minutes to do index & match to the 2 months you need to compare


if any of you have better idea, i would like to know it, below is the file.


http://speedy.sh/xbyHF/compare-recurring-item.xlsx

 

[Faseeh]

Hi koi,


Please see this: http://dl.dropbox.com/u/60644346/Comp.%20Rec.%20Items.%20Workout.xlsx


Regards,

 

[koi]

thanks faseeh,


i have some idea based on your workout,


1. i will try to use your sumproduct formula in column I

2. the 2 month to compare will be based on pivot table column V and W


then i will put 2 active box + macro to control pivot table column V and W (so we dont need to choose manually the monthly to compare from pivot table)

 

[Faseeh]

Hi Koi,


I have given my suggestion, now you can go your own way.


Regards,