insert three columns with merge duplicate items and calculation across sheets

**tubrak** · 06-18-2022, 11:21 PM

hi guys
I need macro to insert three columns in sheet FINISHING after matching column B with the others sheets and merge duplicate items across sheets for columns sales & returns and calculation as the formula in column BALANCE( the formula shouldn't show) with considering two things somtimes there is items are existed in the others sheets but not existed in sheet finishing should be add it into sheet finishing ,will add new sheets every time ( the same structure as in the others) also should show hyphen for empty or zero value for cells with formatting of numbers .
the result should be in columns D,E,F in sheet finshing
Last thing when run the macro repeatedly should not insert columns every times just update data if add new data or change .

**Dante Amor** · 06-22-2022, 11:58 AM

Try this:

Please Login or Register  to view this content.

**tubrak** · 06-23-2022, 04:22 AM

great !
two modifying please
1- sorry I don't mentioned from the beginning .sometimes there is new item into one of sheets but not existed in sheet finshing should be add it and dd hyphen in column C .
2- I want add numberformat foe each value like this "#,##0.00"
thanks

**Dante Amor** · 06-23-2022, 10:29 AM

Try this:

Please Login or Register  to view this content.

**tubrak** · 06-23-2022, 04:26 PM

thanks again
yesterday strange case occurs !!

it gives wrong values it seems arranging the columns are not correct and today occures the same thing
just compare the result in sheet FINISHING when running the code with the right result in last sheet.

**Dante Amor** · 06-23-2022, 05:42 PM

ITEMS sheet
3 TR 1425/148V MN 123H K/L 15

FINISHING sheet (macro):
4 TR 1425/148V MN 123H K/L 1,220.00 15.00 - 1,205.00

right result (Your answer)
4 TR 1425/148V MN 123H K/L 1,220.00 30.00 4.00 1,194.00

why 30?
The ITEM "TR 1425/148V MN 123H K/L" only exists in the "items" sheet. So the macro is fine. Check your data.

**tubrak** · 06-23-2022, 06:54 PM

why 30?
The ITEM "TR 1425/148V MN 123H K/L" only exists in the "items" sheet. So the macro is fine. Check your data.

it's existed into two sheets ITEMS,SHEET2 , if as you said 15 then shouldn't give 34 ( it seems 15+15+4) .the right 15+15
the problem when shows the values are wrong .the returns shows me 34 but the right 4 , and the balance shows me 4 but the right is 224 ,did you see the values after run the macro ?
it seems the return add over sale (30+4=34) and the return replace of the balance .

**Dante Amor** · 06-23-2022, 08:29 PM

In sheet2 it is not the same, since it has 2 spaces, between "V" and "MN" instead of 1, that's why they are not equal. You must review your data. The macro is fine.

ITEMS sheet
TR 1425/148V_MN 123H K/L

Sheet2
TR 1425/148V__MN 123H K/L

**tubrak** · 06-23-2022, 10:42 PM

ok I clean up the spaces to matching but even if this you still don't understand me ( take CTR 12-200M45 MM 123H K/L as in example )
after run the macro sales gives 280 how?

it's just existed in first sheet should be 30 also return gives 34 how? should be 4 also the balance how gives 4 ? should be (250-30+4=224)
I no know why you think your code works ? if I exclude the proplems the spaces otherwise the values are extremely wrong , please take your time and just see the item I gave you as example I'm so confused .
do me favor,please ?
just attach the file and will inform you how works .

**bebo021999** · 06-24-2022, 12:29 AM

HTML Code:

**tubrak** · 06-24-2022, 02:34 AM

@bebo021999 thanks
first
also there is new item into one of sheets but not existed in sheet finshing should be add it and dd hyphen in column C .
add numberformat foe each value like this "#,##0.00" .
should show hyphen for empty or zero value for cells with formatting of numbers .
now it doesn't give right result . see the post#9 and compare sheet finishing with last sheet
your code gives the same result as DanteAmore's code.

may you attach the file maybe the problem from my file ,please?

**bebo021999** · 06-24-2022, 03:06 AM

Here it is. Pls highlight where the issue is.

**tubrak** · 06-24-2022, 04:40 AM

@bebo021999
very strange !!!

it works

do me favor please add this my asking as I has ever said,please?
also there is new item into one of sheets but not existed in sheet finshing should be add it and dd hyphen in column C .
add numberformat foe each value like this "#,##0.00" .
should show hyphen for empty or zero value for cells with formatting of numbers .

**jindon** · 06-24-2022, 05:23 AM

This highlights the difference between my result and yours.

Please Login or Register  to view this content.

**bebo021999** · 06-24-2022, 05:47 AM

Again:

HTML Code:

**tubrak** · 06-24-2022, 06:07 AM

@bebo021999
thanks again ! just last thing seem you forgot showing "-" for the empty or zero cell
and will close this thread

**jindon** · 06-24-2022, 06:11 AM

So, you don't want to even test my code, good.

**tubrak** · 06-24-2022, 06:42 AM

jindon

So, you don't want to even test my code, good.

my apologies !!!
the matter is not as what you think , believe me I don't note it just when enter the thread I see the last post for bebo021999
I don't enter by notification in my email if I did it ,then I saw it.thanks for informe me
about your code extremely perfect except one thing when I add new item and contains values in columns sales & returns it will put value under column QTY shouldn't be , when add new item the column QTY is always "-" should put the values under columns sales & returns
see the wrong in last row when I run the macro and the right in column O,P ,Q range also highlighted .

**jindon** · 06-24-2022, 07:06 AM

Try change to

Please Login or Register  to view this content.

**tubrak** · 06-24-2022, 08:11 AM

thanks jindon but when add new item the empty cell keep empty , doesn't add "-" .

**jindon** · 06-24-2022, 08:45 AM

That's your formatting.
The code copies the cell formats from the last row when the code needs to add new row(s).
The cell format in that column have multiple formatting, so you need to set it as you want for each column before you run the code.

**tubrak** · 06-24-2022, 10:14 AM

@jindon thanks for your solution & time

@Dante Amor you're right , your code works but just on your attaching but not mine( strange case!!!) . even if that many thanks

@bebo021999 thanks very much for time & helping
great works guys

**Dante Amor** · 06-24-2022, 10:53 AM

Originally Posted by tubrak

after run the macro sales gives 280 how?

You must exclude the "right result" sheet.

In this line of the macro you must remove the sheets that should not be considered!

Please Login or Register  to view this content.

You're right, my macro has problems with the "*" considering it as a wildcard.
I changed the focus to search, I append the updated code and in the file you will see the sheet "check" with the verification of the results.

Please Login or Register  to view this content.

**tubrak** · 06-24-2022, 11:50 AM

@Dante Amore
again much appreciated for your effort & assistance
you and the others guys provide me great solution

thanks all

**Dante Amor** · 06-24-2022, 02:35 PM

Glad we could help & thanks for the feedback

**jindon** · 06-24-2022, 10:45 PM

Originally Posted by tubrak

thanks jindon but when add new item the empty cell keep empty , doesn't add "-" .

Originally Posted by jindon

That's your formatting.
......
......

OOps, also need to change one line...

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

**tubrak** · 06-25-2022, 06:34 AM

@jindon
thanks again for correction

insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Re: insert three columns with merge duplicate items and calculation across sheets

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] search & merge duplicate items based on inputbox

[SOLVED] modified code merge duplicate items for each sheet separately based on this thread

[SOLVED] Merge several sheets, with different items

[SOLVED] Merge duplicate rows and merge the columns (approx 10 columns)

Bookmarks

Bookmarks

Posting Permissions