Hi there,
Further testing (admittedly of my slightly tweaked version of the code to cater for the extra column to be returned and condition to be met?) has showed up some odd results. I simply don't know any VBA so probably best leave it to the pros. I've dumped an extract of the real data here in the hope that captures all eventualities and that someone will be able to help. Sorry to keep this query going on - I probably should have done this from the outset!
So the following source data:
User Name
| Entity
| Entity Description
| Account Description
| Level 1
| Level 2
| Level 3
| Level 4
|
David Hoole
| To be confirmed
| All
| Advertising Promotion
| | | Y
| |
Sara Girard
| To be confirmed
| All
| Advertising Promotion
| Y
| Y
| | |
David Hoole
| To be confirmed
| All
| Advertising Promotion
| | | Y
| |
Mike Florek
| To be confirmed
| AM US
| Advertising Promotion
| | Y
| | |
Michael Voss
| To be confirmed
| AM US
| Advertising Promotion
| Y
| | | |
Gerrard Preston
| To be confirmed
| All Titles
| Commercial Reprints
| Y
| | | |
Andy Douglas
| To be confirmed
| All Titles
| Commercial Reprints
| | Y
| | |
Dean Sanderson
| To be confirmed
| All Titles
| Commercial Reprints
| | | Y
| |
Jessica Rutt
| To be confirmed
| All Titles
| Commercial Reprints – Rightslink
| Y
| | | |
David Hoole
| To be confirmed
| All Titles
| Commercial Reprints – Rightslink
| | | Y
| |
Debashish Brahmachari
| To be confirmed
| ABC India
| Commercial Reprints – ABC
| Y
| | | |
Tony Bocquet
| To be confirmed
| ABC Asia
| Commercial Reprints – ABC
| Y
| | | |
Javier Cazana
| To be confirmed
| ABC Brazil & ABC Ibero
| Commercial Reprints – ABC
| Y
| | | |
David Swinbanks
| To be confirmed
| All Titles
| Commercial Reprints – ABC
| | | Y
| |
<tbody>
</tbody>
Should result in this:
Entity
| Entity Description
| Account Description
| Level 1
| Level 2
| Level 3
| Level 4
|
To be confirmed
| All
| Advertising Promotion
| Sara Girard
| Sara Girard
| David Hoole
| |
To be confirmed
| AM US
| Advertising Promotion
| Michael Voss
| Mike Florek
| | |
To be confirmed
| All Titles
| Commercial Reprints
| Gerrard Preston
| Andy Douglas
| Dean Sanderson
| |
To be confirmed
| All Titles
| Commercial Reprints – Rightslink
| Jessica Rutt
| | David Hoole
| |
To be confirmed
| ABC India
| Commercial Reprints – ABC
| Debashish Brahmachari
| | | |
To be confirmed
| ABC Asia
| Commercial Reprints – ABC
| Tony Bocquet
| | | |
To be confirmed
| ABC Brazil & ABC Ibero
| Commercial Reprints – ABC
| Javier Cazana
| | | |
To be confirmed
| All Titles
| Commercial Reprints – ABC
| | | David Swinbanks
| |
<tbody>
</tbody>
However, having tried to 'amend' the code as follows:
Sub brandnewbie()
Dim Rng As Range
Dim Dn As Range
Dim n As Long
Dim Col As Integer
Dim c As Long
Dim Twn As String
Set Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count, 1 To 7)
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
Twn = Dn & Dn.Offset(, 1)
If Not .Exists(Twn) Then
n = n + 1
.Add Twn, n
For Col = 1 To 7
ray(n, Col) = IIf(UCase(Dn(, Col)) = "Y", Dn.Offset(, -1), Dn(, Col))
Next Col
Else
For Col = 4 To 7
If UCase(Dn(, Col)) = "Y" Then ray(.Item(Twn), Col) = Dn.Offset(, -1)
Next Col
End If
Next
c = .Count
End With
With Sheets("macro results")
.Range("A1").Resize(c, 7) = ray
.Columns.AutoFit
End With
End Sub
I am getting this:
Entity
| Entity Description
| Account Description
| Level 1
| Level 2
| Level 3
| Level 4
|
To be confirmed
| All
| Advertising Promotion
| Sara Girard
| Sara Girard
| David Hoole
| |
To be confirmed
| All
| Advertising Promotion
| | | David Hoole
| |
To be confirmed
| AM US
| Advertising Promotion
| Michael Voss
| Mike Florek
| | |
To be confirmed
| All Titles
| Commercial Reprints
| Jessica Rutt
| Andy Douglas
| David Swinbanks
| |
To be confirmed
| ABC India
| Commercial Reprints – ABC
| Debashish Brahmachari
| | | |
To be confirmed
| ABC Asia
| Commercial Reprints – ABC
| Tony Bocquet
| | | |
To be confirmed
| ABC Brazil & ABC Ibero
| Commercial Reprints – ABC
| Javier Cazana
| | | |
<tbody>
</tbody>
Thanks a lot again for any help anyone can give!!!
