• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Macro

V

Vishal.jagani

New Member
Hello All,

I am new for Macro I really don't know more about macro
Now I have challenge regarding excel sheet

Let me explain

I am getting an excel file everyday

Fields are below:

product-id, product-id-type, price, quantity

Now which file2 came today in that file and yesterdays file1, both has could be same data , Its not compulsory but it could be,

Now My Issue is that I want Keep both sheets data but If
product-id found duplicates then Stock will be done with '0' and all new data should be under of old data

Simple : Copy both file data in one sheet and paste on new sheet and then compare
product-id If found duplicate then yesterdays Quantity data must be "0" and new all data (File2) must be under of old data, Its should be set automatically through Macro
pls find Attached
I hope U guys will understand my question pls reply ASAP

Thnks
Vishal Jagani
 

Attachments

  • File1.xlsx
    8.3 KB · Views: 0
  • File2.xlsx
    8.3 KB · Views: 0
Hi Vishal ,

Firstly , I have not understood anything ; if you can show what the output should be , I may understand.

1. Should the output be in a new workbook ? If not , should it be in yesterday's file or today's file ?

2. Given the data in your uploaded files , there are two duplicates , and the other 3 product IDs are unique. What will be the complete output given these 2 files ?

Thirdly , putting in ASAP is not going to ensure that you get an answer fast. You cannot depend on public help forums to get quick answers ; if you do you can count yourself lucky. If there are people who can spare the time , and have the expertise to resolve your problem , it will happen , otherwise you should be prepared to wait.

Narayan
 
Sorry Narayan I didn't meant It

Out put should be in Today's File
Sorry but I explained some wrong I think
I attached Output file

Sorry narayan again Pls check file

and thank you so much for your humble reply
 

Attachments

  • Output.xlsx
    8.4 KB · Views: 0
Hi Vishal ,

Does this mean that given two files with data , all you want done is :

1. Copy the entire data from yesterday's file to today's file.

2. For all of this copied data , change the quantity values to 0.

Narayan
 
Hi Vishal ,

See if this is OK.

The file which has the macro is named TransferData.xlsm

The process is that you open the 3 files viz. TransferData.xlsm , yesterday's file , which is to be named File1.xlsx , and today's file , which is to be named File2.xlsx

In case these file names will be different , make the necessary changes in the code.

The macro is also called TransferData ; when you run this macro , it will transfer the data from File1.xlsx to File2.xlsx , and zero the quantity values for yesterday's data.

Opening the files and saving / closing them is to be done manually.

Narayan
 

Attachments

  • TransferData.xlsm
    11 KB · Views: 1
  • File1.xlsx
    9 KB · Views: 2
  • File2.xlsx
    11.2 KB · Views: 1
Hi Narayan,
Thnks for reply

I have downloaded macro File but to where I have to Put It

I clicked on but nothing gona happens
 
Hi Narayan,

I downloaded Macro and put it in one folder and there are also both file "File1.xlsx and File2.xlsx"

After I clicked on Macro but I can't get anything

and seriously I really don't know actual process because I am new for Macro
So can you give me some more clearance

Vishal
 
Hi Vishal ,

This has nothing to do with macro ; I have clearly described the process you have to follow :

The process is that you open the 3 files viz. TransferData.xlsm , yesterday's file , which is to be named File1.xlsx , and today's file , which is to be named File2.xlsx

In case these file names will be different , make the necessary changes in the code.

The macro is also called TransferData ; when you run this macro , it will transfer the data from File1.xlsx to File2.xlsx , and zero the quantity values for yesterday's data.

Opening the files and saving / closing them is to be done manually.
Click to expand...

Have you read the above ?

In case you have not understood something in the above , please ask.

Narayan
 
Hi Narayan,

I can't understand

"The process is that you open the 3 files viz. TransferData.xlsm , yesterday's file , which is to be named File1.xlsx , and today's file , which is to be named File2.xlsx"
Sorry


other thing is that File name are not different..
 
Hi ,

There are 3 files involved in this exercise :

1. The file which has the macro ; this is named TransferData.xlsm

2. The file which has yesterday's data ; this should be named File1.xlsx

3. The file which has today's data ; this should be named File2.xlsx

All 3 files should be open before you run the macro.

Activate the workbook named TransferData.xlsm , click on the Developer tab , click on Macros , select the macro named :

ThisWorkbook.TransferData

and click on the Run button.

Can you follow this and give feedback ?

Narayan
 
Hi Narayan,

It's Done

As per before I mentioned that I am less familiar with macro

I opened all 3 files but I didn't run macro

Now It's Done and thank you again
 
Hi Narayan,

Yes, with this steps above - macro works fine
But unfortunately, we have some changes to adopt in this macro

Change is -
We want to make stock 0 for product-id which is missing in today's file but was available in yesterday's file
So by this, we will compare today's file to yesterday's file and take product-id which is missing in today's file.
So we will avoid unnecessary duplication of same product-id again in the final file

Find Output File
 

Attachments

  • Output.xlsx
    8.4 KB · Views: 0
Hello Narayan,

I don't know How to Thnk you, But Hearty Thank you So much

One more thing In future If I need more help regarding same, will contact you
and can you give me some tips that How can be master in Macro ( I don't know anything about Macro)

Step by step How to gain knowledge about macro and more

Please share some tips

Thnk you again Friend
 
Hi Narayan Good Morning..

Thanks for reference, I will try my best

Now I want to know about yesterdays issue , Don't worry there is nothing issue but I want to ask one more question

Macro made for excel files, but can we use this macro for CSV file
I mean If have File1.csv and File2.csv at that time will It Work or not
I have some csv files also (sorry but I forgot to mention you yesterday)

Vishal
 
Hi Vishal ,

The problem is that the .csv file is a text file , and we cannot refer to cells and ranges when working with .csv files.

Secondly , the code has been written assuming that the input files viz. File1.xlsx and File2.xlsx are already open. This will now change , since if the files are .csv files , having them open , presumably in Notepad or some other text editor is irrelevant as far as the code is concerned.

Even if the .csv file is opened in Excel , unless some amount of pre-processing such as a Text to Columns has been done , processing this single column of text is going to take more code.

All in all , the change from having the data in a .xlsx file , and having it in a .csv file is a major change.

Narayan
 
Hi Narayan,

So, We don't have any solution for now..?

or any alternate for same?
If we have any possible solution then, we can consider it.

Vishal
 
Hi Vishal ,

A solution is certainly possible.

I wanted to emphasize that it will require coding , probably lengthier and more complex.

I do not have the time to spend on this.

Please start a new thread , and describe your new requirements in detail ; someone will respond.

Narayan
 
Hi Narayan,

what does means of error code 400

Before some days you have made macro for me and it's working fine
But when I tried to run this same macro for another file Its ganerating error 400

I have gave same name of files , like "File1", "File2",
Sheet name kept "Sheet1"

But one thing is that This two files were processing through PowerQuery

So Is it affected any things to this Macro..?

Any suggetions

Thnks
Vishal
 
Hi Narayan,

Here both file, I forgot to attached

Vishal
 

Attachments

  • TransferData.xlsm
    14 KB · Views: 1
  • File1.xlsx
    494.1 KB · Views: 1
  • File2.xlsx
    487.7 KB · Views: 1
You must log in or register to reply here.
Back
Top