Transpose of Data

Hi All,

I was hoping somebody would be able to help with an issue i am having at the moment.I am trying to transpose data from one spreadsheet to another using an index formula however, i am having issues.

(Please see attached image of example spreadsheet)

What i am hoping to do is transpose the information on Figure 1 to Figure 2.

When i try to get (Figure 1) No.1 Ref A for example to Ref A No.1 on Figure 2 it works no problem. The problem that does occur however, is for No.2 - No7 as it doesnt recognise the A from figure 1 it sees it as a blank cell. I was originally going to copy them over indiviually however, there is a vast amount of information and i want to use this exact sheet for other information of a similar standard.

I would be extrmeley grateful for your help as i have tried everything i know (which is limited). If no one knows a way of solving this i have another possible solution that i also would need your help with but its very complex and a bit of a pain.

Where is 255.9 stored? It looks like C3 but is it?
This is where posting the file, or a small sample of it, instead of an image helps more.

As you posted no sample workbook, nor described you method to transpose data, try the following (rather universal) procedure:
Select your merged cells with A...D. Unmerge (format cells: Ctrl+1 and unmark merge cells).
Having these cells selected use Goto (Ctrl+G) and then select Special - Empty cells
Note that several ranges are now selected between cells with A and B, B and C etc. Assumming your A is in cell B5 (first selected cell = the active one is B6)
in formula bar write =B5 and press Ctrl+Enter

now in first colun you will have
A
A
A... 7 times
B
B... etc


so your transposition method shall work for all data.

This works on your sample spreadsheet, adjust to fit if necessary

Sub transpdata()
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row
j = ""
For i = 5 To lastrow
If Worksheets("Sheet1").Cells(i, 2) <> "" Then
    k = Cells(i, 2)
    j = k
Else
    k = j
End If
l = Worksheets("Sheet1").Cells(i, 3)
m = Worksheets("Sheet1").Cells(i, 4)
shrow = WorksheetFunction.Match(k, Worksheets("Sheet1").Range("H7:H11"), False)
shcol = WorksheetFunction.Match(l, Worksheets("Sheet1").Range("I6:O6"), False)
Worksheets("Sheet1").Cells(shrow + 6, shcol + 8) = m
Next i
End Sub

So your data on the ACTUAL spreadsheet starts in B5 and contains the letter A ?
And the value in column B never exceeds 7 (since your example output doesnt allow for that) ?

Somehow I suspect this is not the case and this sample data you've provided is a merely an aesthetically pleasing output.
This means whatever solution is provided you will have to modify it (by yourself) to fit your actual data.

Ignore this, I unsubscribed to this thread by mistake, just replying so it resubscribes (unless there is another way to do this?)

This shall do (tried to write it rather simple, using built-in worksheet functions from excel 2016):

Sub foo()
Dim i As Long, j As Long, lr As Long, k As Long, inp_make, foundvalue
Application.ScreenUpdating = False
With Sheets("Car Output")
  Sheets("Car Input").Activate
  lr = Cells(Rows.Count, "A").End(xlUp).Row
  inp_make = Range("A8:A" & lr).Value
  k = 8
  For i = 1 To lr - 7
    If inp_make(i, 1) <> "" Then
      j = Cells(i + 7, "A").MergeArea.Count
      .Cells(k, "C") = inp_make(i, 1)
      foundvalue = Application.VLookup(2008, Cells(i + 7, "E").Resize(j, 2), 2, 0)
      If Not IsError(foundvalue) Then .Cells(k, "E") = foundvalue
      foundvalue = Application.VLookup(2009, Cells(i + 7, "E").Resize(j, 2), 2, 0)
      If Not IsError(foundvalue) Then .Cells(k, "F") = foundvalue
      foundvalue = Application.VLookup(2010, Cells(i + 7, "E").Resize(j, 2), 2, 0)
      If Not IsError(foundvalue) Then .Cells(k, "J") = foundvalue
      If Application.CountIfs(Cells(i + 7, "E").Resize(j, 1), ">=2012", Cells(i + 7, "E").Resize(j, 1), "<=2015") > 0 Then
        .Cells(k, "M") = Application.MaxIfs(Cells(i + 7, "F").Resize(j, 1), Cells(i + 7, "E").Resize(j, 1), ">=2012", Cells(i + 7, "E").Resize(j, 1), "<=2015")
        .Cells(k, "N") = Application.MinIfs(Cells(i + 7, "F").Resize(j, 1), Cells(i + 7, "E").Resize(j, 1), ">=2012", Cells(i + 7, "E").Resize(j, 1), "<=2015")
      End If
      k = k + 1
    End If
  Next i
  .Activate
End With
Application.ScreenUpdating = True
End Sub

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

PS. Note rule 12 of our https://www.excelforum.com/forum-rul...rum-rules.html