Merge data across multiple sheets in one sheet and calculate the values

Report
Posts
4
Registration date
Tuesday April 20, 2021
Status
Member
Last seen
June 8, 2021
-
Posts
4
Registration date
Tuesday April 20, 2021
Status
Member
Last seen
June 8, 2021
-
hi
I have data in multiple sheets the same structure some sheets have duplicated data
so it should merge and summing the values and show the result in sheet result and sometimes the data in sheet PURCHASE & SRETURNS are new then should highlight by blue color and highlight red color in sheet result an add to the bottom in sheet result and if I change or entering any data for any sheet then should update in sheet the result when I run the macro should brings all of data in sheet result with highlighting in the last row the formula should like this in COL E in sheet result =FIRST+ PURCHASE -SALES+SRETURNS-PRETURNS
I put a little data in first two rows and the three last rows to see how should the formula in last column E with brings all of data
attached file
https://easyupload.io/fst7aj

2 replies

Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462
Hi Kalilme,

I don't understand your logic for highlighting new entries. What is considered new? On sheet "Purchase" you have on entry for 25-2 which is considered new and is blue, but on sheet "Sales" you have on entry for 25-2 which apparently is not new as it is not blue.

Is there a condition for the highlights to be non-highlighted?

I also don't understand the calculation FIRST+ PURCHASE - SALES + SRETURNS - PRETURNS. When I look at the value for Banana SO across the sheet respectively, I get:
100 + 330 - 40 + 10 - 45 = 355
Yet your sheet "Result" shows 255. Did you just forgot the value from sheet "First"?

Best regards,
Trowa
Posts
4
Registration date
Tuesday April 20, 2021
Status
Member
Last seen
June 8, 2021

Hi Trowa,
sorry if I make confusion for you let me start where you finished because this part is the most important , I no know how I forgot it sorry I lost my focus , yes you're right the right value should be 355 , as for the data are highlighted because theses are new the highlighted should only new data
then also you're right on sheet "Sales" entry for 25-2 which apparently should be blue and the same data should not be necessarily to be existed in another sheet
finally I would add another detail my real data are at least 3000 across multiple sheets and it may increasable continuously
I hope this help
Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462 >
Posts
4
Registration date
Tuesday April 20, 2021
Status
Member
Last seen
June 8, 2021

Hi Kalilme,

Ok, that clarifies the calculation part.

I still can't figure out what makes an entry "new" though.
On the "Purchase" sheet you have 2 dates considered new; 24-2 and 25-2, so it's not a single date.
On the "Sreturns" sheet you have a new entry with the date 24-3, which is a whole lot "newer" (later).

How would you define "new"?

Best regards,
Trowa
Posts
4
Registration date
Tuesday April 20, 2021
Status
Member
Last seen
June 8, 2021

Hi Trowa,
of course the date is different because the data occurs daily , the new entry data specifies from COL A: COL E together so when fill data from COL A: COL E then should automatically show in sheet result and calculate , and if the data are existed in COLB,C,D are repeated then should merge , if you see in sheet result you will note merge and calculate duplicated data in COL B,C,D together
note: but sometimes changes data in sheet purchase or sheet sreturns has already existed then should change in sheet result
I hope this help

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!