Column information to cells in Row

[iadukab]

Alright, so this is a confusing problem to explain.

I have column A full of test numbers, and column D is revisions the tests show up in (4 different versions). I would bring this spreadsheet down from 1800 rows to around 500 rows by making the info on D appear on one line in columns E,F,G,H, rather than four rows.

Example:
This
_A__|__B__|__C__|__D__|
1001|_____|_____|v1.0
1002|_____|_____|v1.0
1002|_____|_____|v1.1
1002|_____|_____|v2.0
1003|_____|_____|v1.0
1003|_____|_____|v1.1
1004|_____|_____|v1.1
1005|_____|_____|v1.0
1005|_____|_____|v1.1
1005|_____|_____|v2.0
1005|_____|_____|v2.1

becomes this:


_A__|__B__|__C__|D__| 1.0|1.1 |2.0|2.1 |
1001|_____|_____|v1.0|_y_|_n_|_n_|_n_|
1002|_____|_____|v1.0|_y_|_y_|_y_|_n_|
1003|_____|_____|v1.0|_y_|_y_|_n_|_n_|
1004|_____|_____|v1.1|_n_|_y_|_n_|_n_|
1005|_____|_____|v1.0|_y_|_y_|_y_|_y_|

I am 1000% at a loss on how to do this without spending the next 15 hours doing it by hand.
Any help with this would be amazing.

[JBeaucaire]

That's doable. First, create a helper column down the side of your existing data with this formula in it:

=A1&"-"&RIGHT(D1,3)

In E1, that formula would result in 1001-1.0 and that's a unique and searchable piece of data. Copy that formula down to get the whole E column of unique values.

Now, on Sheet2, setup your new chart layout, then put this formula into E2, the first cell you needed a y/n answer in:

=IF(ISERROR(MATCH($A2&"-"&E$1,Sheet1!$E$1:$E$11,0)),"n","y")

Copy that cell to the rest of your chart and all your y/n answers will appear properly.

Now, highlight the ENTIRE set of new data you just created, then COPY, EDIT>PasteSpecial>Values over the top of it and all the formula will be removed and lock the answers in as real data.

A sample sheet is attached. Note, you must enter those blue cells in as text on Sheet2 for this to work properly.

[StephenR]

Here's one approach, which puts results on sheet2 (and assumes you have nothing in column P of your first sheet).

Sub x()

Dim rng As Range, oDic As Object, n As Long, i As Long

Sheet1.Activate
Sheet2.UsedRange.Clear
Range("D1", Range("D1").End(xlDown)).AdvancedFilter xlFilterCopy, copytorange:=Range("P1"), unique:=True
Range("P2", Range("P2").End(xlDown)).Copy
Sheet2.Range("E1").PasteSpecial Transpose:=True
Set oDic = CreateObject("Scripting.Dictionary")
For Each rng In Range("A2", Range("A2").End(xlDown))
    i = Application.Match(rng.Offset(, 3).Value, Sheet2.Range("E1", Sheet2.Range("E1").End(xlToRight)), 0)
    If Not oDic.Exists(rng.Value) Then
        Sheet2.Cells(Rows.Count, 1).End(xlUp)(2) = rng.Value
        Sheet2.Cells(Rows.Count, 4 + i).End(xlUp)(2) = "y"
        n = n + 1
        oDic.Add rng.Value, n
    Else
        Sheet2.Cells(oDic.Item(rng.Value) + 1, 1) = rng.Value
        Sheet2.Cells(oDic.Item(rng.Value) + 1, 4 + i) = "y"
    End If
Next rng
Sheet2.Range("E1").CurrentRegion.SpecialCells(xlCellTypeBlanks) = "n"
Columns("P").Clear

End Sub

EDIT: ok JBeaucaire has beaten me to it but at least you now have one of each.

[iadukab]

Oh man, thank you guys so much.
I couldn't quite get the first answer to work, but the second one seems to work almost perfectly.
At a glance, I only noticed one that was not correct. It should look like nnyn but it is ynnn. I think it was a one time test though, so it was easily correctable.
Thank you guys though, you just saved me two days of work.

[JBeaucaire]

Mine did require that you put the titles in row in as TEXT. If you type in 1.0, Excel changes that to 1, you would need to type in '1.0 (with the apostrophe). No worries.

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

[StephenR]

That reminds me that I neglected to say that mine requires a header row, i.e. you need to put something (anything) in A1 to D1.