I have a row of data as follows:
header1 header2 header3 header4 header5
row key datavalue1 datavalue2 datavalue3 datavalue4 datavalue5....
so basically, I have a denormalized data set where the datavalues may or may not be empty on a row-by-row basis. I need to normalize them.
ie
12345678 NULL 10 3 NULL 14
would become:
12345678 header2 10
12345678 header3 3
12345678 header5 14
I could do this by using a paste special transform, but I have thousands of rows and I'd need to make sure that I get the right row key for each. furthermore, each row has a bunch of descriptives associated with it that I need copied over with each datavalue.
What is the easiest way to convert each row of columns such that I have multiple rows of a single column with all non-empty datavalues plus the associated datavalue reference? I need to be able to pivot the dataset.
If you have five "header" columns, enter these formulas
H1: =OFFSET($A$1,INT((ROW()-1)/5)+1,0)
I1: =OFFSET($A$1,0,IF(MOD(ROW(),5)=0,5,MOD(ROW(),5)))
J1: =INDEX($A$1:$F$9,MATCH(H1,$A$1:$A$9,FALSE),MATCH(I1,$A$1:$F$1,FALSE))
Copy H1:J?? and paste special values over the top. Sort on column J and delete anything that's a zero. If you have legitmate zeros in the data, then you first need to replace blank cells with some unique string that you can then delete later.
If you have more columns, then replace the '5' in all the above formulas with whatever number you have.