change to one row of data from several

Hi All

New here so thanks in advance for any help you can offer me

I have a list ( no more than 500 entries that contains duplicates, one of the columns (c) contains an action number ( 1 to 11)

I download this data from a database (Which I cant change) so i will have one persons name (Column A) which will be repeated several times with a cation number in column c as one person can have several actions

e.g.

Name called action
Fred 10.00 1
Fred 10.01 2
Fred 10.01 3
Fred 10.01 4
Fred 10.01 7
John 10.00 5
John 10.01 4
John 10.01 11
Mike 11.00 8
Adam 12.02 2
Adam 12.02 1
Adam 12.02 3
Adam 12.02 4
Adam 12.04 5

What i need to do is delete the duplicates but put a yes in columns d through to M on a single row where they have had an action matching 2 - 11

I have got how to remove duplicates, and i ve managed to do a case statement to put a yes in the row in the right place prior to deleting it

Are you able to help me to get all the data i want onto one line

I ve pasted my existing code below

I really am greatfull for any help you can offer in this regard

Gibbo

Dim R As Long
    Dim N As Long
    Dim V As Variant
    Dim A As Long
    Dim rng As Range
     
    'On Error GoTo EndMacro
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
     
     
    Set rng = Application.Intersect(ActiveSheet.UsedRange, _
    ActiveSheet.Columns(ActiveCell.Column))
     
    Application.StatusBar = "Processing Row: " & Format(rng.Row, "#,##0")
     
    N = 0
    'A = Rng.Rows(1).Row
    For R = rng.Rows.Count To 2 Step -1
        If R Mod 500 = 0 Then
            Application.StatusBar = "Processing Row: " & Format(R, "#,##0")
        End If

        V = rng.Cells(R, 1).Value
        
        
        'This Bit*************************
        'A = Rng(R).Rows.Row(2)
        'MsgBox A
        'This Bit*************************
        
        
         '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
         ' Note that COUNTIF works oddly with a Variant that is equal to vbNullString.
         ' Rather than pass in the variant, you need to pass in vbNullString explicitly.
         '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        If V = vbNullString Then
            If Application.WorksheetFunction.CountIf(rng.Columns(1), vbNullString) > 1 Then

                rng.Rows(R).EntireRow.Delete

                N = N + 1
            End If
        Else
        
        
            If Application.WorksheetFunction.CountIf(rng.Columns(1), V) > 1 Then
            
            
            'Case Statement
            
            Select Case rng.Cells(R, 3).Value
            
            Case 2
            rng(R).Rows(1).Cells(, 4).Value = "Yes"
            Case 3
            rng(R).Rows(1).Cells(, 5).Value = "Yes"
            Case 4
            rng(R).Rows(1).Cells(, 6).Value = "Yes"
            Case 5
            rng(R).Rows(1).Cells(, 7).Value = "Yes"
            Case 6
            rng(R).Rows(1).Cells(, 8).Value = "Yes"
            Case 7
            rng(R).Rows(1).Cells(, 9).Value = "Yes"
            Case 8
            rng(R).Rows(1).Cells(, 10).Value = "Yes"
            Case 9
            rng(R).Rows(1).Cells(, 11).Value = "Yes"
            Case 10
            rng(R).Rows(1).Cells(, 12).Value = "Yes"
            Case 11
            rng(R).Rows(1).Cells(, 13).Value = "Yes"
            
            End Select
            'rng.Offset(, 1) =
            
                N = N + 1
                
            End If
            
        End If
    Next R
     
EndMacro:
     
    Application.StatusBar = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    MsgBox "Duplicate Rows Deleted: " & CStr(N)

Thankyou, that did what I asked but i want to try and take it one step further so i was hoping there was an approach that would be more editable

I should add names are not always grouped together either

For example

I have stated 1 - 11 and it now puts them across in one row which is fantastic, but i may want a different word used so for action 2 i may want yes but for action three i might want completed.

The other think i was looking at was that i might want the title to say level trained and if 2 is completed for it to say Level 2 or if three is complete (In which case 2 would not be there) for it to say fully trained

Not sure if that explains where im heading any better, I am very greatful for your assistance which has already made my life far easier but i was also hoping for an example i could manipulate a bit more and learn from

Thanks again and if anyone can offer a further example based on my above comments that would be great

Thanks

Gibbo

Can I also ask how this code would look if i wanted to use it for data in column f and put the results from J onwards for example (To help me understand it)

Thanks

Gibbo

Thanks

Appologies for not being clearer, soulds clear in my head

Yes the first columns will stay the same, F becomes C and replaces the actions column and I would like to know how to start the data that currently starts in D in J for example (Its so i can better understand how this works)

Thanks Again

Gibbo

P.S. the code above works great now and i did have numbers in the header as I was lazy when adding headers for testing

Thanksyou, one last question and then i leave you in peace and do a bit more reading

I have doen action numbers 1 to 11 on my test and in some cases this will really help me but some of hat i am looking at has 100's of codes but i will only be interested in displaying about 20 of them, will this method work where i want the titles along the top to represent the action numbers in the format 1,3,7,120, 122, 145 instead of 1 2 3 4 5 or will that require a totally different approach

Thanks

gibbo

Wow, That really is superb, i tried for ages to get to this stage and have learnt so much so cant thank you enough

I did say no more questions but as everything i ve asked youhave solved in a few seconds flat is it possible then to set an arguement

For example say if the code is 2 then the text is yes, if the code is 110 then the text in the same column becomes no,

so could I have a title for example Level of training, if action = 2 No then the cell would say no, if the action is 110 the cell content becomes Yes or someother text?

Also im assuming if i want 20 columns instead of 11 i just change the Resize(, 11) to Resize(, 20)

Thanks once again

gibbo

Thanks for your help

Gibbo

Sorry forgot to ask, if my actions data is in column f what would i need to change

thanks

gibbo

Sorry io got the first bit

Cells(Rows.Count, "A").End(xlUp)).Offset(, 9).Resize(, 11)
.FormulaR1C1 = "=IF(COUNTIF(R1C1:RC1,RC1)>1,NA(),REPT(R1C&"""",SUMPRODUCT(--(R1C1:R500C1=RC1),--(R1C6:R500C6=(COLUMN(C)-9)))))"
.Value = .Value
.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete

But the second bit of code changed and im not sure how to move from the column Being C (The source) I tried a couple of options but none worked for me

I assumed the following would work but doesnt

.FormulaR1C1 = "=IF(COUNTIF(R3C1:RC1,RC1)>1,NA(),REPT(R2C&"""",SUMPRODUCT(--(R3C1:R502C1=RC1),--(R3C6:R502C6=R1C))))"
.Value = .Value
.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete

Thanks

Gibbo

Ignore my last, think i figured it out, the below has set it up with the actions column to be split in column d and the data split starting from column E

Public Sub Remove_Duplicates_and_Sort_Data()
With Range(Cells(3, "A"), Cells(Rows.Count, "A").End(xlUp)).Offset(, 4).Resize(, Application.Count(Rows(1)))
.FormulaR1C1 = "=IF(COUNTIF(R3C1:RC1,RC1)>1,NA(),REPT(R2C&"""",SUMPRODUCT(--(R3C1:R502C1=RC1),--(R3C4:R502C4=R1C))))"
.Value = .Value
.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
End With
End Sub

Thanks

Gibbo