vba - Excel: Create unique rows for data sampled over time -

vba - Excel: Create unique rows for data sampled over time -

i have excel spreadsheet contains info measured on period of 14 years. 14 measurement periods displayed on same row, have individual row each sampling period:

unit 2001 2002 2003 2004 a.....1.0.....2.3....3.4......4.3 b.....1.2.....na.....na.......na c.....0.8.....2.3....na.......na d.....na......na.....1.2......3.4

i have separate row each sampling period, when sampled. thought each year , corresponding measurement it's own info point without including years unit not sampled:

unit year measure a....2001...1.0 a....2002...2.3 a....2003...3.4 a....2004...4.3 b....2001...1.2 c....2001...0.8 c....2002...2.3 d....2003...1.2 d....2004...3.4

is possible vba or formula? i'm dealing big spreadsheet , don't want reenter hand. help appreciated!

you can reverse pivot table on data. need start pivot table wizard that. not in ribbon, if have excel 2007 or later, must start keyboard shortcut alt-d-p.

in step 1 tick alternative multiple consolidation ranges , click next in step 2a tick "i create page fields" select info table in step 2b, including headers , click add, next in step 3 click finish. see pivot table in new worksheet. drag row , column field out of pivot table build tool (or untick them in pivot field list), 1 field remains: total count , label. double-click number a info source created on new sheet.

your original info remain unchanged, can repeat process @ time.

excel vba excel-formula