#### chandannasta

##### New member

- Joined
- Dec 4, 2021

- Messages
- 5

- Reaction score
- 0

- Points
- 0

- Excel Version(s)
- 2007

Note: Make sure before doing this this you replace all the # n/a, N/A with all single 0 in entire sheet on new tab when copying and pasting the data from sp1 sheet.

Now here is the 1st part of the sheet which I have marked in red I do from sp1 sheet till current date and copied and pasted on new sheet called as Frnds:

1. First I copy the Code and Company Name in new tab on excel sheet from sp1 sheet

2. Lets say for example I take the data of 25th aug to 31st aug. I copy that

3. So what I do is take the 1st co value from 26th aug and minus that with the same company value on 25th aug. So if you see my 1st co was 20 microns and the value on 26th aug was 52.75 and on 25th aug the value was 53.3 so when I minus that I get -0.55 which is mentioned on the right. So like this I do on daily basis. Minus the value of the current day with the previous day with the data in hand. So the 1st part of the sheet is attached named as frnds which I do it on daily basis for which I need a macro.

Now another part that I do is check how many days/weeks/months how many companies are going up and down in a day, 2 days, 3 days, 4 days, 5 days and so on and in order to do that I do this which I have marked in yellow in frnds 1 sheet

1. Apply filter on entire excel sheet

2. Then sort the numbers where we just collected the entire data the minus thing from largest to smallest

3. As you can see I only have the data from 26th aug onwards so I will take that and sort from largest to smallest.

4. So all companies whose priced increased with positive numbers will come up for 26th aug and all negative numbers and companies with no changes will go down.

5. So the one with positive numbers which means value increased I marked them as 1 and once in which there is no change at all or the value has decreased I mark them as O. So if you see for 26th aug the higest positive value in red shows as 463.45 so I had sorted for 26th aug only from largest to smallest. So higest change was rs 463.45 for company called P&G HYGIENE & HEALTH CARE and the lowest positive change was 0.05 for a co called UNION BANK OF INDIA and after that all negative and no changes in company started which I marked as 0

6. Same procedure I do for 27th, 30th and 31st aug also

So I want to get the above entire thing automated via macro rather then doing it manually. Thank you for all your help in advance.

Now here is the 1st part of the sheet which I have marked in red I do from sp1 sheet till current date and copied and pasted on new sheet called as Frnds:

1. First I copy the Code and Company Name in new tab on excel sheet from sp1 sheet

2. Lets say for example I take the data of 25th aug to 31st aug. I copy that

3. So what I do is take the 1st co value from 26th aug and minus that with the same company value on 25th aug. So if you see my 1st co was 20 microns and the value on 26th aug was 52.75 and on 25th aug the value was 53.3 so when I minus that I get -0.55 which is mentioned on the right. So like this I do on daily basis. Minus the value of the current day with the previous day with the data in hand. So the 1st part of the sheet is attached named as frnds which I do it on daily basis for which I need a macro.

Now another part that I do is check how many days/weeks/months how many companies are going up and down in a day, 2 days, 3 days, 4 days, 5 days and so on and in order to do that I do this which I have marked in yellow in frnds 1 sheet

1. Apply filter on entire excel sheet

2. Then sort the numbers where we just collected the entire data the minus thing from largest to smallest

3. As you can see I only have the data from 26th aug onwards so I will take that and sort from largest to smallest.

4. So all companies whose priced increased with positive numbers will come up for 26th aug and all negative numbers and companies with no changes will go down.

5. So the one with positive numbers which means value increased I marked them as 1 and once in which there is no change at all or the value has decreased I mark them as O. So if you see for 26th aug the higest positive value in red shows as 463.45 so I had sorted for 26th aug only from largest to smallest. So higest change was rs 463.45 for company called P&G HYGIENE & HEALTH CARE and the lowest positive change was 0.05 for a co called UNION BANK OF INDIA and after that all negative and no changes in company started which I marked as 0

6. Same procedure I do for 27th, 30th and 31st aug also

So I want to get the above entire thing automated via macro rather then doing it manually. Thank you for all your help in advance.