Concatenate IF

[ritinrulz]

I need to combine different cells if they meet a criteria. I am basically putting up a list of medals and the whole football team gets a medal and it shows up in different lines, so what i want to do is show just one line with the event and all the athletes in one cell rather than different cells.[TABLE="width: 500"]
<tbody>[TR]
[TD]Year
[/TD]
[TD]Event
[/TD]
[TD]Athlete
[/TD]
[TD]Medal
[/TD]
[TD="align: center"]What i want
[/TD]
[/TR]
[TR]
[TD]2000
[/TD]
[TD]Football
[/TD]
[TD]Mark
[/TD]
[TD]Gold
[/TD]
[TD]Mark, Twain, Shakepeare, Elizabeth, Byron, Hemingway
[/TD]
[/TR]
[TR]
[TD]2000
[/TD]
[TD]Football
[/TD]
[TD]Twain
[/TD]
[TD]Gold
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2000
[/TD]
[TD]Football
[/TD]
[TD]Shakespeare
[/TD]
[TD]Gold
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2000
[/TD]
[TD]Football
[/TD]
[TD]Elizabeth
[/TD]
[TD]Gold
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2000
[/TD]
[TD]Football
[/TD]
[TD]Byron
[/TD]
[TD]Gold
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2000
[/TD]
[TD]Football
[/TD]
[TD]Hemingway
[/TD]
[TD]Gold
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2000
[/TD]
[TD]Archery 100ft
[/TD]
[TD]Ian
[/TD]
[TD]Silver
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2000
[/TD]
[TD]Archery 200ft
[/TD]
[TD]Ian
[/TD]
[TD]Gold
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2000
[/TD]
[TD]100m Track
[/TD]
[TD]Bolt
[/TD]
[TD]Gold
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So when the events are different it shoudln't combine them.

Regards,

Ritin

 

[DeusXv]

The image you linked isn't working.

 

[jeffreybrown]

Check out the post below and specifically #7

http://www.mrexcel.com/forum/excel-questions/169996-vlookup-multiple-results.html

 

[ritinrulz]

The image you linked isn't working.

Are you still not able to see the table

 

[ritinrulz]

I am going to try it, looks like it is what i want.
Thanks a ton Mate!!

 

[jamtay317]

Excel 2012

	A	B	C	D	E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Year[/TD]
[TD="bgcolor: #FAFAFA"]Event[/TD]
[TD="bgcolor: #FAFAFA"]Athlete[/TD]
[TD="bgcolor: #FAFAFA"]Medal[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2000[/TD]
[TD="bgcolor: #FAFAFA"]Football[/TD]
[TD="bgcolor: #FAFAFA"]Mark[/TD]
[TD="bgcolor: #FAFAFA"]Gold[/TD]
[TD="bgcolor: #FAFAFA, align: center"]What i want[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2000[/TD]
[TD="bgcolor: #FAFAFA"]Football[/TD]
[TD="bgcolor: #FAFAFA"]Twain[/TD]
[TD="bgcolor: #FAFAFA"]Gold[/TD]
[TD="bgcolor: #FAFAFA"]Mark, Twain, Shakepeare, Elizabeth, Byron, Hemingway[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2000[/TD]
[TD="bgcolor: #FAFAFA"]Football[/TD]
[TD="bgcolor: #FAFAFA"]Shakespeare[/TD]
[TD="bgcolor: #FAFAFA"]Gold[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2000[/TD]
[TD="bgcolor: #FAFAFA"]Football[/TD]
[TD="bgcolor: #FAFAFA"]Elizabeth[/TD]
[TD="bgcolor: #FAFAFA"]Gold[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2000[/TD]
[TD="bgcolor: #FAFAFA"]Football[/TD]
[TD="bgcolor: #FAFAFA"]Byron[/TD]
[TD="bgcolor: #FAFAFA"]Gold[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2000[/TD]
[TD="bgcolor: #FAFAFA"]Football[/TD]
[TD="bgcolor: #FAFAFA"]Hemingway[/TD]
[TD="bgcolor: #FAFAFA"]Gold[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2000[/TD]
[TD="bgcolor: #FAFAFA"]Archery 100ft[/TD]
[TD="bgcolor: #FAFAFA"]Ian[/TD]
[TD="bgcolor: #FAFAFA"]Silver[/TD]
[TD="bgcolor: #FAFAFA"]Mark, Twain, Shakespeare, Elizabeth, Byron, Hemingway[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2000[/TD]
[TD="bgcolor: #FAFAFA"]Archery 200ft[/TD]
[TD="bgcolor: #FAFAFA"]Ian[/TD]
[TD="bgcolor: #FAFAFA"]Gold[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2000[/TD]
[TD="bgcolor: #FAFAFA"]100m Track[/TD]
[TD="bgcolor: #FAFAFA"]Bolt[/TD]
[TD="bgcolor: #FAFAFA"]Gold[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

</tbody>

Sheet3​

Sub jamtayEventCombine()
Dim year As Integer
    lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    game = UCase(InputBox("Please Type What Event It Is!"))
    year = InputBox("Please Type What Year It Is!")
    medal = UCase(InputBox("Please Type What Medal It Is!"))
    
    For x = 2 To lr
        If ActiveSheet.Cells(x, 1).Value = year And UCase(ActiveSheet.Cells(x, 2).Value) = game And UCase(ActiveSheet.Cells(x, 4).Value) = medal Then
            If Name = "" Then
                Name = ActiveSheet.Cells(x, 3)
            Else
                Name = Name & ", " & ActiveSheet.Cells(x, 3)
            End If
        End If
    Next x
    ActiveCell = Name
End Sub

 

[jamtay317]

this will over write anything in the cell that you have active cell in