Looping to pull specific cell value

[mani_bbc05]

Hi Friends,

I am stuck with a problem, need your guidance.

In the attachment, Table 1 contains the main data, which will be used to create a table as shown in Table 2.

Conditions are:-
- Data in Table 1 can change, so the VBA code should be dynamic, to look in the full table
- In Table 1 data can change within the range("C3:usedrange") (I mean to say position of Y and country name can change)
- Full Table 2 is required to be extracted by using VBA

Below is the sample file

Thanks you my Friends

 

[SirJB7]

Hi, mani_bbc05!

Place this code in a standard module, adjusting the constants to your actual workboook values.
Input table: ksWSI, worksheet; kiTitleI, title row; kiLabelI, label column
Output table: ksWSO, worksheet; kiTitleO, 1st row; kiLabelO, label column

Option Explicit

Sub WhyDontUseRealTables()
    ' constants
    Const ksWSI = "Sheet1"
    Const kiTitleI = 2
    Const kiLabelI = 2
    Const kiOmitI = 2
    Const ksWSO = "Sheet1"
    Const kiTitleO = 30
    Const kiLabelO = 2
    Const ksY = "Y"
    ' declarations
    Dim I As Long, J As Long, K As Long, L As Integer, A As String
    ' start
    With Worksheets(ksWSO)
        Range(.Cells(kiTitleO, kiLabelO), _
            .Cells(kiTitleO, kiLabelO).End(xlDown)).EntireRow.ClearContents
    End With
    ' process
    With Worksheets(ksWSI)
        I = kiTitleI + 1
        K = 0
        Do Until .Cells(I, kiLabelI).Value = ""
            J = kiLabelI + kiOmitI + 1
            K = K + 1
            L = 0
            Do Until .Cells(I, J).Value = ""
                A = .Cells(I, J).Value
                If L = 0 Then
                    Worksheets(ksWSO).Cells(kiTitleO + K - 1, kiLabelO).Value = .Cells(I, kiLabelI).Value
                End If
                If A <> ksY Then
                    L = L + 1
                    Worksheets(ksWSO).Cells(kiTitleO + K - 1, kiLabelO + L).Value = A
                End If
                J = J + 1
            Loop
            I = I + 1
        Loop
    End With
    ' end
    Beep
End Sub

Just advise if any issue.

Regards!

EDITED

PS: Code updated as per file of 5th post.

 

[mani_bbc05]

Its perfect SirJC7, wonderful lovely...

But SirJC7, can you please tell me what should i change in the code to column C from the result table (i mean to say data should not be pulled from column C)

Thanks a lot bro

 

[SirJB7]

Hi, mani_bbc05!
The code just build your same output table at row 30 (kiTitleO). I don't understand what do you mean with that of changing code, could you please elaborate?
Regards!

 

[mani_bbc05]

Sorry, for my bad English. I have uploaded another sample file, and required output is same as previous. Can you please help me with it.

Sample test file

 

[SirJB7]

Hi, mani_bbc05!
Don't worry about your English, but do worry about changing specs. I added a new constant kiOmitI set to 2, so as to skip 2 columns. Code at previous post updated.
Regards!

 

[mani_bbc05]

Thanks a lot, its working fine.

Thanks & Regards

 

[SirJB7]

Hi, mani_bbc05!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!