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

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

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.

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.

Option Explicit
Sub ReArrangeData()
Dim ws As Worksheet, dws As Worksheet
Dim Srng As Range, rng As Range, crng As Range, ccell As Range
Dim i As Long, r As Long, lr As Long, lc As Long
Application.ScreenUpdating = False
On Error Resume Next
Set dws = Sheets("Report Analysis")
If Err > 0 Then
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Report Analysis"
Set dws = Sheets("Report Analysis")
End If
i = dws.Cells(Rows.Count, 1).End(xlUp).Row + 1
On Error GoTo 0
For Each ws In Worksheets
    If ws.Name <> "Report Analysis" Then
        ws.Activate
        dws.Range("A" & i) = "ISIN"
        dws.Range("B" & i) = "CUSIP"
        dws.Range("C" & i) = "Sedol"
        dws.Range("D" & i) = "div/coupon rate"
        With dws.Range("A" & i & ":D" & i)
            .Font.Size = 13
            .Font.Bold = True
            .Borders.LineStyle = xlContinuous
            .Borders.Weight = xlThin
            .Interior.ColorIndex = 44
        End With
        lr = Cells(Rows.Count, 1).End(xlUp).Row
        Set Srng = ws.Range("A1:A" & lr)
        With Srng
        Set rng = .Find(what:="ISIN", _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlFormulas, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not rng Is Nothing Then
                r = rng.Row
                Do
                    lc = Cells(rng.Row, Columns.Count).End(xlToLeft).Column
                    Set crng = Range(Cells(rng.Row, 2), Cells(rng.Row, lc))
                    For Each ccell In crng
                        If Len(ccell) = 13 Then
                            dws.Range("A" & Rows.Count).End(3)(2) = ccell
                            dws.Range("B" & Rows.Count).End(3)(2) = ccell.Offset(-1, 0)
                            dws.Range("C" & Rows.Count).End(3)(2) = ccell.Offset(1, 0)
                            dws.Range("D" & Rows.Count).End(3)(2) = ccell.Offset(5, 0)
                            Exit For
                        End If
                    Next ccell
                    Set rng = .FindNext(rng)
                Loop While Not rng Is Nothing And r <> rng.Row
            End If
        End With
    End If
    i = dws.Cells(Rows.Count, 1).End(xlUp).Row + 3
Next ws
dws.Columns.AutoFit
Application.ScreenUpdating = True
dws.Activate
MsgBox "Finished."
End Sub

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

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..........

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

On Error Resume Next
Set dws = Sheets("Report Analysis")

Line of code to be added.

dws.Cells.Clear

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.

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.

Option Explicit
Sub ReArrangeData()
Dim ws As Worksheet, dws As Worksheet
Dim Srng As Range, rng As Range, crng As Range, ccell As Range
Dim i As Long, r As Long, lr As Long, lc As Long
Application.ScreenUpdating = False
On Error Resume Next
Set dws = Sheets("Report Analysis")
dws.Cells.Clear
If Err > 0 Then
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Report Analysis"
Set dws = Sheets("Report Analysis")
End If
i = dws.Cells(Rows.Count, 1).End(xlUp).Row + 1
On Error GoTo 0
For Each ws In Worksheets
    If ws.Name <> "Report Analysis" Then
        ws.Activate
        dws.Range("A" & i) = "ISIN"
        dws.Range("B" & i) = "CUSIP"
        dws.Range("C" & i) = "Sedol"
        dws.Range("D" & i) = "div/coupon rate"
        With dws.Range("A" & i & ":D" & i)
            .Font.Size = 13
            .Font.Bold = True
            .Borders.LineStyle = xlContinuous
            .Borders.Weight = xlThin
            .Interior.ColorIndex = 44
        End With
        lr = Cells(Rows.Count, 1).End(xlUp).Row
        Set Srng = ws.Range("A1:A" & lr)
        With Srng
        Set rng = .Find(what:="ISIN", _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlFormulas, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not rng Is Nothing Then
                r = rng.Row
                Do
                    lc = Cells(rng.Row, Columns.Count).End(xlToLeft).Column
                    Set crng = Range(Cells(rng.Row, 2), Cells(rng.Row, lc))
                    For Each ccell In crng
                        If Len(ccell) >= 10 Then
                            dws.Range("A" & Rows.Count).End(3)(2) = ccell
                            dws.Range("B" & Rows.Count).End(3)(2) = ccell.Offset(-1, 0)
                            dws.Range("C" & Rows.Count).End(3)(2) = ccell.Offset(1, 0)
                            dws.Range("D" & Rows.Count).End(3)(2) = ccell.Offset(5, 0)
                            Exit For
                        End If
                    Next ccell
                    Set rng = .FindNext(rng)
                Loop While Not rng Is Nothing And r <> rng.Row
            End If
        End With
    End If
    i = dws.Cells(Rows.Count, 1).End(xlUp).Row + 3
    r = 0
Next ws
dws.Columns.AutoFit
Application.ScreenUpdating = True
dws.Activate
MsgBox "Finished."
End Sub

You're welcome. Glad I could help.

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.

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.

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