Rows of data from columns - not simple transpose.

[Jazzledizzle]

Hi, I'm sorry if I don't follow all of the rules as this is my first post, I may have missed some of them,

My current problem is I need to create one line of data from the table below, which contains a lot more data than just shown below, but as an example, I need to transform the data into one row, per unique lead reference. by example i'd need something like the row of data, from the table.

Output required:

LeadReference

ClientiD1

ClientiD2

ClientiD3

ClientiD4

ClientiD5

ClientiD6

ClientiD7

ClientiD8

ClientiD9

ClientiD10

ClientiD11

Title1

Title2

Title3

Title4

Title5

Title6

Title7

Title8

Title9

Title10

Title11

Forename1

Forename2

Forename3

Forename4

Forename5

Forename6

Forename7

Forename8

Forename9

Forename10

90780390​

90780397​

90780390​

90780399​

90780391​

90780400​

90780392​

90780401​

90780394​

90780402​

90780396​

Group

Group

Group

Group

Group

Group

Group

Group

Group

Group

Main table

Lead Reference

Customer Reference

Title

Forename

Surname

Company

Area_desc2 1

Row 1

Start_seat 1

No_seats 1

Cost 1

Ticket Net £

VAT @ 20%

Gross

Dining Net

VAT @ 5%

Gross

Total

Price_Band 1

90780390​

90780397​

Group

BT Group

M07

40​

162​

1​

8503​

1​

1​

2​

1​

1​

2​

0​

Arnold Hills EXEC

90780390​

90780390​

Group

BT Group

M07

39​

161​

1​

8503​

2​

2​

3​

2​

2​

3​

Arnold Hills EXEC

90780390​

90780399​

Group

BT Group

M07

40​

161​

1​

8503​

3​

3​

4​

3​

3​

4​

Arnold Hills EXEC

90780390​

90780391​

Group

BT Group

M07

39​

160​

1​

8503​

4​

4​

4​

4​

4​

5​

Arnold Hills EXEC

90780390​

90780400​

Group

BT Group

M07

40​

160​

1​

8503​

5​

5​

5​

5​

5​

6​

Arnold Hills EXEC

90780390​

90780392​

Group

BT Group

M07

39​

159​

1​

8503​

6​

6​

5.7​

6​

6​

7​

Arnold Hills EXEC

90780390​

90780401​

Group

BT Group

M07

40​

159​

1​

8503​

7​

7​

6.4​

7​

7​

8​

Arnold Hills EXEC

90780390​

90780394​

Group

BT Group

M07

39​

158​

1​

8503​

8​

8​

7.1​

8​

8​

9​

Arnold Hills EXEC

90780390​

90780402​

Group

BT Group

M07

40​

158​

1​

8503​

9​

9​

7.8​

9​

9​

10​

Arnold Hills EXEC

90780390​

90780396​

Group

BT Group

M07

39​

157​

1​

8503​

10​

10​

8.5​

10​

10​

11​

Arnold Hills EXEC

 

[mumps]

Do you want to transpose only the Customer Reference? Do you want the result on a different sheet?

 

[Jazzledizzle]

I would love for every corresponding row that has the same unique Lead Reference to be put into a different sheet with just the unique lead reference.

 

[mumps]

Try:

Sub TransposeData()
    Application.ScreenUpdating = False
    Dim Rng As Range, RngList As Object, srcWS As Worksheet, desWS As Worksheet, key As Variant
    Dim rowCount As Long, LastRow As Long, lCol As Long, x As Long
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each Rng In srcWS.Range("A2", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp))
        If Not RngList.Exists(Rng.Value) Then
            RngList.Add Rng.Value, Nothing
        End If
    Next
    For Each key In RngList
        With srcWS
            .Cells(1, 1).CurrentRegion.AutoFilter 1, key
            rowCount = .[subtotal(103,A:A)] - 1
            desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1) = key
            .Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).Copy
            desWS.Cells(desWS.Rows.Count, "B").End(xlUp).Offset(1).PasteSpecial Transpose:=True
        End With
    Next key
    srcWS.Range("A1").AutoFilter
    With desWS
        lCol = .UsedRange.Columns.Count
        .Range("A1") = "Lead Reference"
        For x = 2 To lCol
            .Cells(1, x) = "ClientID" & x - 1
        Next x
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[Jazzledizzle]

Try:

Sub TransposeData()
    Application.ScreenUpdating = False
    Dim Rng As Range, RngList As Object, srcWS As Worksheet, desWS As Worksheet, key As Variant
    Dim rowCount As Long, LastRow As Long, lCol As Long, x As Long
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each Rng In srcWS.Range("A2", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp))
        If Not RngList.Exists(Rng.Value) Then
            RngList.Add Rng.Value, Nothing
        End If
    Next
    For Each key In RngList
        With srcWS
            .Cells(1, 1).CurrentRegion.AutoFilter 1, key
            rowCount = .[subtotal(103,A:A)] - 1
            desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1) = key
            .Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).Copy
            desWS.Cells(desWS.Rows.Count, "B").End(xlUp).Offset(1).PasteSpecial Transpose:=True
        End With
    Next key
    srcWS.Range("A1").AutoFilter
    With desWS
        lCol = .UsedRange.Columns.Count
        .Range("A1") = "Lead Reference"
        For x = 2 To lCol
            .Cells(1, x) = "ClientID" & x - 1
        Next x
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Thank you, is there a way to get the other columns to also be copied to sheet two in the same format?

 

[mumps]

All in one row? Can you manually re-create Sheet2 to show what the result would look like and post a screen shot

 

[Jazzledizzle]

All in one row? Can you manually re-create Sheet2 to show what the result would look like and post a screen shot

Hey,

I put this in the question above, and in the interim, I'll try and recreate and screenshot.

Output required, but also including all columns into one row

LeadReference

ClientiD1

ClientiD2

ClientiD3

ClientiD4

ClientiD5

ClientiD6

ClientiD7

ClientiD8

ClientiD9

ClientiD10

ClientiD11

Title1

Title2

Title3

Title4

Title5

Title6

Title7

Title8

Title9

Title10

Title11

Forename1

Forename2

Forename3

Forename4

Forename5

Forename6

Forename7

Forename8

Forename9

Forename10

90780390

90780397

90780390

90780399

90780391

90780400

90780392

90780401

90780394

90780402

90780396

Group

Group

Group

Group

Group

Group

Group

Group

Group

Group

 

[Jazzledizzle]

Hey,

I put this in the question above, and in the interim, I'll try and recreate and screenshot.

Output required, but also including all columns into one row

LeadReference

ClientiD1

ClientiD2

ClientiD3

ClientiD4

ClientiD5

ClientiD6

ClientiD7

ClientiD8

ClientiD9

ClientiD10

ClientiD11

Title1

Title2

Title3

Title4

Title5

Title6

Title7

Title8

Title9

Title10

Title11

Forename1

Forename2

Forename3

Forename4

Forename5

Forename6

Forename7

Forename8

Forename9

Forename10

90780390

90780397

90780390

90780399

90780391

90780400

90780392

90780401

90780394

90780402

90780396

Group

Group

Group

Group

Group

Group

Group

Group

Group

Group

Something like the below, but including all the columns

Customer Reference1

Customer Reference2

Customer Reference3

Customer Reference4

Customer Reference5

Customer Reference6

Customer Reference7

Customer Reference8

Customer Reference9

Customer Reference10

Customer Reference11

Title1

Title2

Title3

Title4

Title5

Title6

Title7

Title8

Title9

Title10

Forename1

Forename2

Forename3

Forename4

Forename5

Forename6

Forename7

Forename8

Forename9

Forename10

x

90780397​

90780390​

90780399​

90780391​

90780400​

90780392​

90780401​

90780394​

90780402​

90780396​

 

[mumps]

In the data you posted, the Title and Company columns are blank. Will there ever be any blanks in your actual data in any of the columns?

 

[Jazzledizzle]

In the data you posted, the Title and Company columns are blank. Will there ever be any blanks in your actual data in any of the columns?

Hi, yes there will be, but the empty spaces should be included in the output, as there will be more rows of data with different lead references so the titles will have to match the later rows data.

For example:
So the output will be two rows of data as there are two unique

Lead Reference

Customer Reference

Block

Row

Seat

No_seats 1

Cost 1

Ticket Net £

VAT @ 20%

Gross

Dining Net

VAT @ 5%

Gross

Total

Price_Band 1

Title

Forename

Surname

Company

90801168​

90801167​

M05

35​

191​

1​

5508​

Arnold Hills EXEC

Mr

Ian

Agates

90801168​

90801168​

M05

35​

190​

1​

5508​

Arnold Hills EXEC

Mr

Ian

Agates

90054370​

90054370​

M07

34​

162​

1​

5008​

Arnold Hills EXEC

Mr

durqan

Aneer

90054370​

90054369​

M07

34​

161​

1​

5008​

Arnold Hills EXEC

Mr

Furqan

Anser

90054370​

90054372​

M07

34​

160​

1​

5008​

Arnold Hills EXEC

Mr

Fqrqan

Ander

90054370​

90054371​

M07

34​

159​

1​

5008​

Arnold Hills EXEC

Mr

Furqan

Anwer