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

Please Login or Register  to view this content.

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