change to one row of data from several

**DonkeyOte** · 11-07-2009, 11:35 AM

I confess I'm not sure I entirely follow - given 1 does not seemingly appear for each record I'm unsure to as to why you're only using D:M to capture 2:11 given C will not always contain 1... should you not therefore use D:N (1:11) ?

Below is a slightly different approach which I believe does as you request but avoids iteration etc...

Public Sub Example()
With Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp)).Offset(, 3).Resize(, 11)
    .FormulaR1C1 = "=IF(R[-1]C1=RC1,0,REPT(""Yes"",SUMPRODUCT(--(R1C1:R500C1=RC1),--(R1C3:R500C3=(COLUMN(C)-3)))))"
    .Value = .Value
    .SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Delete
End With
End Sub

whenever physically altering data be sure to test on a sample sheet first !

edit: and should add the above assumes names are grouped together - if not this can be altered quite easily to account for that.

**gibbo1715** · 11-07-2009, 11:43 AM

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

**DonkeyOte** · 11-07-2009, 11:51 AM

You can certainly iterate if that's your preference - and judging by the nuances you want to add that may well be the most viable approach - I just like to avoid iteration where possible.

In terms of different words for different numbers... that could still be achieved quite easily by storing the appropriate words at the head of each column and thus instead of

REPT(""yes"",...)

one would use

REPT(R1C,...)

Regards the names not being listed in blocks, I would change:

IF(R[-1]C1=RC1,0,...

to

IF(COUNTIF(R1C:RC1,RC1)>1,0,...)

So in summation I would revise the code to:

Public Sub Example()
With Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp)).Offset(, 3).Resize(, 11)
    .FormulaR1C1 = "=IF(COUNTIF(R1C1:RC1,RC1)>1,0,REPT(R1C,SUMPRODUCT(--(R1C1:R500C1=RC1),--(R1C3:R500C3=(COLUMN(C)-3)))))"
    .Value = .Value
    .SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Delete
End With
End Sub

where D1:M1 contain words to be repeated.

The above may not resolve all of your wants - I'm not sure I followed them all in truth - and I appreciate it's quite a removal from your initial approach - but it is no more than a suggestion