excel - Transposing and inserting cells at the same time -

i'm having trouble following problem. have dataset looks this:

1  3  4  6  7 1  2 2  4  5  9 5   1  2  3  5 

i want take single numbers common in each, , arrange them in 1 column:

1 2 3 4 5 6 7 9 

the approach i'm taking script recognise there more 1 complete cell in row, execute command transpose adjacent cells beneath current range. have far is:

sub recordarrangetest() dim rng range dim long dim n long dim wholecolumn range dim lastcolumn long lastcolumn = range("a1").currentregion.columns.count = 1  dim lastrow long lastrow = range("a1").end(xldown).row  while <= lastrow set rng = range("a" & i) set wholecolumn = range(cells(i, i), cells(1, lastcolumn)) if isempty(rng.offset(0, 1).value) = false range(rng.offset(1, 0), rng.offset(lastcolumn, 0)).insert shift:=xldown wholecolumn.copy rng.offset(1, 0).pastespecial paste:=xlpasteall, operation:=xlnone, skipblanks:= _         false, transpose:=true wholecolumn.delete shift:=xlup = + 1 else: = + 1  end if wend  end sub 

although during testing, works = 1, increasing after first trigger causes mistmatch somewhere. there i'm missing? or there different approach you'd recommend instead?

thanks

i use dictionary ignores duplicates , iterate through used cells, clear entire range , paste dictionary in place.

sub foo() dim ws worksheet set dict = createobject("scripting.dictionary") dim rng range dim t dim long  set ws = sheets("sheet1")  each rng in ws.usedrange     if rng <> ""         on error resume next             dict.add rng.value, rng.value         on error goto 0     end if next rng  ws.usedrange.clearcontents = 1 each t in dict     ws.cells(i, "a").value = t     = + 1 next t  ws.range("a1:a" & i).sort key1:=ws.range("a1")  end sub