Changing Data Layout

[Damianjay]

Hello,
I'm trying to transpose some data I receive from a report into a format I require.

The format I receive is as follows:

Column Names as follows:
Inspection No, RoleCode, OIN, InspectorName
340123, S5l1-2, 123123, Mr Smith
340123, QAR, 147147, Mr Jones
340123, SAFE, 159159, Mr Black
347347, S5l1-2, 789789, Mr Sims
347347, S5T2-2, 456456, Mr Hastings
347347, SAFE, 698698, Mr Zach

As you can see I have multiple inspectors against against a single inspection number as out lined above and I need to transpose the data so it sits on a single row with the following Column names:
A=Inspection No, B=RoleCode1, C=OIN, D=InspectorName, E=RoleCode2, F=OIN, G=InspectorName, H=RoleCode2, I=OIN, J=InspectorName, K=RoleCode3, L=OIN, M=InspectorName (so column A is the inspection number followed by repetitions of RoleCode, OIN and Inspector Name on the same row)

I also need help in automating this so that does this for as many inspection numbers that sit in the list.

I hope you can help as this is proving difficult and it is a big sticking point for me.
Thanks in advance
Damian

 

[Comfy]

A Pivot table would work really well.

I won't put them in to the columns that you requested but would group similar data together.

 

[Damianjay]

Thanks for the response Comfy but I really need the data in the layout I specified. Do you thinkit is possible to automate this.
Thanks
Damian

 

[MickG]

Try this:-
The code will alter the data layout !!!

[COLOR="Navy"]Sub[/COLOR] MG03Jun29
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Temp [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Offset(-1) = Dn [COLOR="Navy"]Then[/COLOR]
        Lst = Cells(Temp.row, Columns.Count).End(xlToLeft).Column
        Dn.Offset(, 1).Resize(, 3).Copy Destination:=Temp.Offset(, Lst)
        Dn.Offset(, 1).ClearContents
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] Temp = Dn
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
 Rng.Offset(, 1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Damianjay]

Thats great Mick

Thank you very much for your help

Much Appreciated
Damian

 

[Damianjay]

Hi Mick

Again thanks very much for your help. I've got another problem that I was wonderin gif you could help me with.

I've got two worksheets. The master contains the ID, User Name and the 3 columns which are currently empty Pract, Ethnic, Status.

The second sheet consists of User name, ID and then the 3 columns with the data for Pract, Ethinic, Status.

I want to run a formula on the master that says if ID = ID, paste the data from the 3 columns into the master sheet

Hope that makes sense and really hoppe you can help
Thanks
Damian