Remove Blank and Copy Paste to other Sheets

[saamrat]

Hi,

Sheet1 has Order and Allocation of different Cafes for various items.
I need to copy only Allocation and paste it to the Sheet2 leaving all unallocated items and Cafe that has no orders
Please help me to do with Macro

Shaj

 

[Marc L]

Hi,​

​

explain why some product is missing within Sheet2,​

so what are exactly the rules to apply in order to get exactly the same result as yours with missing item ?​

​

 

[saamrat]

Hi,

All the cafes will not have order everyday. In fact, it is a long list. We don't need the cafe names and items if there is no order. Remove those items and cafés that have no orderss and copy the allocated column with cafes and items those who have orders for the day. Cafes will give us orders and we allocate manually based on the available stock. Allocated stock sheet (sheet2) will send to dispatch section for loading the items in the truck

 

[Marc L]

So you did not understand my question …​

I send your attachment as a training to a kid VBA beginner but he asked me​

« Why by hell there are 4 allocated items in Sheet1 but only 3 in Sheet2 ?! »​

As guessing can't be coding …​

Please help me to do with Macro

So rather than any macro VBA procedure you should try some Excel feature like a pivot for example …​

​

 

[saamrat]

People who handle the spread sheet has limited knowledge in excel. That's why thought of doing it with click of a button

 

[Marc L]

Without any answer to post #4 kid question so without any crystal clear logic​

I won't waste my time to write any VBA codeline neither the kid obviously …​

​

 

[saamrat]

Thanks

 

[Marc L]

Review your attachment and share the correct expected result according to the source worksheet​

or just answer to post #4 question …​

​

« If you can’t explain it simply, you don’t understand it well enough … »​

Albert Einstein​

​

 

[saamrat]

Sir,

There are 4 allocated items both in Sheet1 and Sheet2. Sheet2 only shows allocated items.

 

[Marc L]

That was confusing 'cause your attachment shows only 3 allocated items among 4 in Sheet2 !​

​

Another point found by the kid : according to the merged cells in Sheet1​

- never come to any Excel forum with merged cells, not such a great idea ! -​

you must choose one of this two options :​

1. if you really need the result in Sheet2 so the cells formatting will be lost.
   
   
2. If you need to keep the cells formatting so the result can be easily achieved directly in Sheet1
   like any Excel beginner operating manually in ten seconds just using a filter, the kid way …

 

[mohamed ilyas]

Hi

if you are using office 365, then the Filter formula will work as your wish, please find the attached.
try to add your in sheet 1, then sheet 2 will showing only total allocated if >0.


regards
ilyas

 

[saamrat]

Thank you so much Ilyas Bhai. That helps a lot. You have showed kindness to help me. Thank you.
If you share your knowledge, that will double many fold. At least you didn't make fun out my lack of expertise in excel.

 

[Marc L]

At least if you just well answer to questions …​

​

So it seems you do not need anymore any VBA procedure !​

​

As a reminder : when the initial post is at the level of what any Excel forum expects for, the solution comes in next post.​

​

 

[saamrat]

Hi

if you are using office 365, then the Filter formula will work as your wish, please find the attached.
try to add your in sheet 1, then sheet 2 will showing only total allocated if >0.


regards
ilyas

Is it possible the same formula can be applied for vertical filter ? How the formula would be ?
If any of those cafe has no order, that has to be removed from the Sheet 2. Is it possible?
Vertical and horizontal filter !

 

[Marc L]

It is possible whatever with or without a formula under VBA …​

​

 

[saamrat]

Can you please help me

 

[mohamed ilyas]

Is it possible the same formula can be applied for vertical filter ? How the formula would be ?
If any of those cafe has no order, that has to be removed from the Sheet 2. Is it possible?
Vertical and horizontal filter !

hello Saamrat,

please give us example how you want in vertical?

regards
ilyas

 

[Marc L]

Can you please help me

So well read at least post #10 and choose an option …​

​

 

[saamrat]

hello Saamrat,

please give us example how you want in vertical?

regards
ilyas

Hi,

Attached excel sheet where I have achieved the result using your filter formula with "match index". The "Final sheet" returns the cafe name and the product only if allocated.

Thank you so much for your help.

Is there any better way to achieve the same ?