help me to sort the data with v.b.a code

**boddulus** · 10-03-2014, 11:12 PM

Hi all a very good morning to you all,

I have an excel sheet where there is tons of data which is dispersed in uneven way. can some one please help me with the code to get the desired result.

Please see the sample sheet attached.

things needed from code:

i want a new sheet with name as "Report analysis" to be created with headers in horizontal as ISIN, Cusip,Sedol,Div/Coupon rate
i want macro should copy the ISIN, cusip,sedol ,Div/Coupon rate and paste it in new sheet
the sheet format will be same as sample security reports sheet i provided
ISIN will be in same row and it always be in 13 digits
Cusip will be in 9 digits
sedol will be in 7 digits
after completing sheet 1 it should go to sheet 2 and leave 2 rows gap in report analysis sheet and again do the same process in sheet2 as sheet 1.
please find the desired sheet as sample of what i want.
thanks for your help in advance..... have a good day..........

**boddulus** · 10-04-2014, 06:31 AM

can someone please look in to this..... thanks in advance for your help......

**HaHoBe** · 10-04-2014, 06:45 AM

Hi, boddulus,

according to the forum rules you should wait for a day before you push your own thread (and you didn´t consider that this a weekkend):

If your question has not been answered within a day, consider adding another post with any additional information you believe is relevant. If you think your post is good as is, just reply to your own thread with the words "Bump no response", which will bring it to the top of the forum.

Ciao,
Holger

**sktneer** · 10-04-2014, 06:51 AM

Give some logic that what data is to be picked from unknown column like CUSIP can be found in col. A in any row but once a CUSIP is found in a row, how would you decide that which column has the desired CUSIP as your CUSIPs do not follow any pattern so how would it be different from other columns values?
What is exactly in other columns? Is it the actual data or you have put some random characters in other columns?
Code will need a logic to pick the correct data. It is just like visualizing the correct data manually and that even is based on some logic so that we know that this is the desired data to be picked and how it differs from other data in that row.
Same way you need to give some logic to pick the data for ISIN, Sedol and div/coupon rate also.

**boddulus** · 10-04-2014, 08:45 AM

Hi Hahobe,

i am sorry , i will follow the rules.......

Hi Sktneer,

i had given the logic's like headers will be always in column A and desired value will also be in same row other column.

For example if first ISIN is in COLUMN A you will find the desired value of isin in same row

coming on differentiating point ISIN is always 13 digits and if you observe no other data in same row is 13 digits.
in same ways Cusip as 9 digits always in same row and if you observe no other data in same row is 9 digits
in same way sedol as 7 digits always in same row and if you observe no other data in same row is 7 digits
and for div/coupon rate once it find isin,cusip,sedol then you can find the desired value in same column.

the code should first detect the word ISIN in column A and it should search for 13 digit word in same row and it should pick up the value in another sheet as i mentioned in original sheet.
then cusip,sedol,div/coupon

this is not original data .it is sample data but original looks same as this ........ please let me know if you need any more details... thanks.....

**sktneer** · 10-04-2014, 09:48 AM

Please try this code to see if you get the desired output. As you provided only one sheet so I copied the same data on two other sheets. Therefore this data will also reflect on the Report Analysis Sheet i.e. you will get three reports on the Report Analysis Sheet.

Please Login or Register  to view this content.

Please find the attached sheet and click on the Green Button on Sheet1 to get the desired output on Report Analysis Sheet.

**sktneer** · 10-04-2014, 10:00 AM

Please add one more line of code just below these two lines of code

Please Login or Register  to view this content.

Line of code to be added.

Please Login or Register  to view this content.

This new line of code will clear the existing data from the Report Analysis Sheet if it already exists in the workbook otherwise the code will add a new Report Analysis Sheet in the workbook.

**boddulus** · 10-04-2014, 10:02 AM

that's too good ............ thank you very much, that helped me a lot ........ i want to give you many ****** ......... and lots of smileys........

........ thank you very much.............

**boddulus** · 10-04-2014, 10:10 AM

But one thing in sheet 2 there are 4 securities but when i run a macro, in desired report it has taken only two can you please look in to it and adjust that.... please find the attached sheet for reference.......... and there are lot of securities in original data in both the sheets, so macro should go to end row to pick up all securities can you please make that adjustments to the code............ thanks in advance..........

**sktneer** · 10-04-2014, 11:18 AM

You're welcome. Thanks for the feedback.

On Sheet2 there are 5 securities not 4 and two ISIN are of 13 characters, two ISIN are 11 characters and one is of 12 characters. As you said your ISIN will always be 13 characters long and the code was looking for a 13 characters string in ISIN's row so it found only two. That's the case.
I have made changes in the code to pick the string which is more than or equal to 10 characters.
The changed part is in red color in the code, so you can change it as per your need.

Please Login or Register  to view this content.

**boddulus** · 10-04-2014, 01:52 PM

oh i am sorry it was my fault..... i didn't notice it....yes the isin will always be of 13 digits but since i manually enter the data there was a mistakes in sheet 2................... anyway thanks for your help..... i will mark this thread as solved.... thank you very much once again for your help ...............

**sktneer** · 10-04-2014, 02:41 PM

You're welcome. Glad I could help.

**boddulus** · 10-06-2014, 11:25 AM

Hi Sktneer,

Can you please help me out,

can you please modify the above code in such a way that macro should find cusip ,sedol and div/coupon rate same like ISIN... since i found in the original sheet like some doesn't have cusip,some doesn't have sedol and some doesn't have Div/coupon rate... please find the attached sheet for reference...

and also i want to know from which sheet the isin or cusip or sedol came . i mean macro should automatically give name of sheet in that 2 blank in report analysis sheet... please find the sheet for reference....
Thanks for your help in advance ........

**sktneer** · 10-06-2014, 11:50 AM

please upload the sample workbook with the desired output shown on another sheet and highlight the cells in question on raw data sheets where you don't find these values. Let me know what output you are expecting if any value is missing for a sub-set. Show the desired output which is completely meets all your requirements.

**boddulus** · 10-06-2014, 12:10 PM

please help me out ........ i have attached the sheet with explanation in the sheet.... please let me know if you need any thing else...

thanks for your help and patience...............

Please see the comments in raw sheet and Report analysis sheet.....

**sktneer** · 10-06-2014, 01:25 PM

Please find the attached sheet to see if this works as per your requirement. I haven't matched the Report with raw data. Please match the Report Analysis Sheet with the Raw Data Sheets to see if you get the desired output.
Notice some of your ISINs are less than 13 characters, so I have modified the code to pick any ISIN which is >=10 characters.

**boddulus** · 10-06-2014, 08:21 PM

Hi Sktneer,

yes this will work..... i have compared it and it is working fine... thanks for your help again...... you have been a great help to me thanks for that..............

**boddulus** · 10-06-2014, 08:32 PM

Hi sktneer,

One more question.... i have been trying to learn V.B.A coding from past 20 days .. i have brought books like Excel 2013 power programming with VBA and started reading them. i work cognizant (investment banking) in Hyderabad and my lot of work is involved around excel . i know that there is no easy way to learn these things except doing hard work.... so i when ever i get time i will sit with that book or watch online solutions or watch you tube videos about excel programming , so can you please suggest me some tips how to learn and master these stuff..... i know excel formulas and other basic stuff but i want to master these VBA coding like you people..........

**boddulus** · 10-06-2014, 08:40 PM

i really want to give a star and add reputation for your help but i don't know it is not allowing me to do. its popping up as "You must spread some Reputation around before giving it to sktneer again". any way it was great help and lot of patience from you .... i really appreciate and thank you for that........

**sktneer** · 10-07-2014, 01:30 AM

Never mind. Its good to know that your issue has been resolved.

help me to sort the data with v.b.a code

LinkBack

Thread Tools

Rate This Thread

Display

help me to sort the data with v.b.a code

Re: help me to sort the data with v.b.a code

Re: help me to sort the data with v.b.a code

Re: help me to sort the data with v.b.a code

Re: help me to sort the data with v.b.a code

Re: help me to sort the data with v.b.a code

Re: help me to sort the data with v.b.a code

Re: help me to sort the data with v.b.a code

Re: help me to sort the data with v.b.a code

Re: help me to sort the data with v.b.a code

Re: help me to sort the data with v.b.a code

Re: help me to sort the data with v.b.a code

Re: help me to sort the data with v.b.a code

Re: help me to sort the data with v.b.a code

Re: help me to sort the data with v.b.a code

Re: help me to sort the data with v.b.a code

Re: help me to sort the data with v.b.a code

Re: help me to sort the data with v.b.a code

Re: help me to sort the data with v.b.a code

Re: help me to sort the data with v.b.a code

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to sort the data by item code first, then sort them by date?

Using VBA Code to sort my data automatically

Data Sort Code Issue

Code Help Needed to Sort Data

Sort Data without using code

Bookmarks

Bookmarks

Posting Permissions