Rearranging Data in Excel

[astout920]

Hi, all. I have a table where I need to rearrange the data. The below is a small sample size. The real table is about 100 rows and 700 columns. The first table is what it looks like now, and the second table is what I'd like it to look like. Is there an easy way to do this. I tried pivot tables but it wasn't working right (for me). Would MS Access be a better tool? Although I'm not sure how to build the right query.

Any help would be greatly appreciated!!! Thanks!!!

Current Input
[TABLE="width: 356"]
<tbody>[TR]
[TD="align: center"]Qualifier[/TD]
[TD="align: center"]Model 1[/TD]
[TD="align: center"]Model 2[/TD]
[TD="align: center"]Model 3[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]0.1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.2[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.1[/TD]
[/TR]
[TR]
[TD="align: center"]C[/TD]
[TD="align: center"]0.4[/TD]
[TD="align: center"]0.1[/TD]
[TD="align: center"]0.1[/TD]
[/TR]
[TR]
[TD="align: center"]D[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.6[/TD]
[TD="align: center"]0.1[/TD]
[/TR]
[TR]
[TD="align: center"]E[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.3[/TD]
[TD="align: center"]0.5[/TD]
[/TR]
</tbody>[/TABLE]


Desired Output (the headers in the top row can be any text as far as I'm concerned)
[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: center"]Model[/TD]
[TD="class: xl65, width: 64, align: center"]Qualifier[/TD]
[TD="class: xl65, width: 64, align: center"]Value[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 1[/TD]
[TD="class: xl65, align: center"]A[/TD]
[TD="class: xl65, align: center"]0.1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 1[/TD]
[TD="class: xl65, align: center"]B[/TD]
[TD="class: xl65, align: center"]0.5[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 1[/TD]
[TD="class: xl65, align: center"]C[/TD]
[TD="class: xl65, align: center"]0.4[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 1[/TD]
[TD="class: xl65, align: center"]D[/TD]
[TD="class: xl65, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 1[/TD]
[TD="class: xl65, align: center"]E[/TD]
[TD="class: xl65, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 2[/TD]
[TD="class: xl65, align: center"]A[/TD]
[TD="class: xl65, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 2[/TD]
[TD="class: xl65, align: center"]B[/TD]
[TD="class: xl65, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 2[/TD]
[TD="class: xl65, align: center"]C[/TD]
[TD="class: xl65, align: center"]0.1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 2[/TD]
[TD="class: xl65, align: center"]D[/TD]
[TD="class: xl65, align: center"]0.6[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 2[/TD]
[TD="class: xl65, align: center"]E[/TD]
[TD="class: xl65, align: center"]0.3[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 3[/TD]
[TD="class: xl65, align: center"]A[/TD]
[TD="class: xl65, align: center"]0.2[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 3[/TD]
[TD="class: xl65, align: center"]B[/TD]
[TD="class: xl65, align: center"]0.1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 3[/TD]
[TD="class: xl65, align: center"]C[/TD]
[TD="class: xl65, align: center"]0.1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 3[/TD]
[TD="class: xl65, align: center"]D[/TD]
[TD="class: xl65, align: center"]0.1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 3[/TD]
[TD="class: xl65, align: center"]E[/TD]
[TD="class: xl65, align: center"]0.5[/TD]
[/TR]
</tbody>[/TABLE]

​

 

[Marcelo Branco]

btw. what if values contain zeroes?

Well, i looked at the data, looked again, and I saw no zero in data ...
(just kidding, 999 is safer of course)

Thank you for the reply.

M.

 

[sandy666]

Well, i looked at the data, looked again, and I saw no zero in data ...
(just kidding, 999 is safer of course)

I used 999 because it's taken from my nick and turned upside down

Have a nice day