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).

Please Login or Register  to view this content.

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.