VBA code with multiple condition

[ryan8200]

There are 5 candidates with 4 favorites sports. WD denotes weekday,while WE represent weekend. If the candidate takes part in a sports, the status will be "Y" and "N" if the participants did not take part in the sports.

The tasks here is to write VBA code to extract data with status "Y" on weekday (WD) and status"N" on weekend (WE).

Kindly refer to the URL below for the sample data and expected outcome.

https://ibb.co/R4YyYgj.

Your kind assistance to the question I posted will be greatly appreciated.


​

 

[steve the fish]

Can you post the table in a format that can be copied. Are those merged cells on row 1?

 

[ryan8200]

Can you post the table in a format that can be copied. Are those merged cells on row 1?[/QUOTE

Row 1 have 5 merge cells. The tables option here have merge functions ?

 

[steve the fish]

Just copy the range in excel and paste it here.

 

[ryan8200]

Just copy the range in excel and paste it here.

Hobbies	A	B	C	D	E
WD	WE	WD	WE	WD	WE	WD	WE	WD	WE
Swimming	Y	Y	Y	N	N	Y	N	N	N	Y
Tennis	N	Y	Y	Y	Y	Y	Y	N	N	Y
Badminton	N	N	N	N	Y	Y	Y	Y	Y	N
Volleyball	Y	N	N	Y	Y	N	Y	Y	N	N
Hobbies	Candidates
Swimming	A
Volleyball	A
Badminton	A
Volleyball	A
Swimming	B
Tennis	B
Swimming	B
Badminton	B
Tennis	C
Badminton	C
Volleyball	C
Volleyball	C
Tennis	D
Badminton	D
Volleyball	D
Swimming	D
Tennis	D
Badminton	E
Badminton	E
Volleyball	E

<colgroup><col><col><col span="9"></colgroup><tbody>
</tbody>

 

[steve the fish]

Is the first table fixed as it wont get any longer or wider?

 

[ryan8200]

Is the first table fixed as it wont get any longer or wider?

You may refer to https://ibb.co/R4YyYgj

. Is there a way to attach Excel files ?

There is alignment problem with row 2. Please indent to the right by 1 cell.
​

 

[steve the fish]

Thats not answered the question. Is table 1 a fixed size? Does it ever grow eg more sports or more than A-E across the top?

 

[ryan8200]

Thats not answered the question. Is table 1 a fixed size? Does it ever grow eg more sports or more than A-E across the top?

It will be a fixed table

 

[steve the fish]

Ok this does it:

With Sheets("Sheet1")
    fr = 8
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    If lr > fr - 2 Then
        .Range("A" & fr & ":B" & lr).ClearContents
        .Range("A" & fr) = "Hobbies"
        .Range("B" & fr) = "Candidates"
    End If
    For j = 2 To 11
        For i = 3 To fr - 2
            If (.Cells(i, j) = "Y" And .Cells(2, j) = "WD") Or (.Cells(i, j) = "N" And .Cells(2, j) = "WE") Then
                .Cells(fr + 1, 1) = .Cells(i, 1)
                .Cells(fr + 1, 2) = .Cells(1, j).MergeArea(1)
                fr = fr + 1
            End If
        Next
    Next
End With

Change sheet name to suit your sheet.