checking appearance of a number, and telling the col num

[mordor]

I'm trying to simplify my previous thread, because it was poorly exlained earlier, by me... :-)
first question:
i have a 10 column array, i want it to be sorted automaticly by appearance.
each new number is typed to the "0" column, and the prev numbers move one column to the left...

9-8-7-6-5-4-3-2-1-0 (top of the array, meaning the position of a number)
.............................
_________________6 (first number, goes on column 0)
_______________6_3 (next number is 3, in the"0"column, previous( "6" ) moves left)
_____________6_3_2 (2 is next, moving previous numbers left)
___________6_3_2_1 (1 is next, you get the idea)

the thing is, that the numbers can't repeat, ie when a new digit already appears in one of the prev rows numbers it appears only in the "0" column and not twice, still moving everything else left:

___________6_2_1_3 (3 is next, already there, so 6 stays put, and then everything moves to the left)
_________6_2_1_3_4 (4 is next, do not appear earlyer)
_________6_1_3_4_2 (2 appears, therefore 6 wont move because there isnt any new digits inserted, and everything except 2 moves left, as 2 stays in the 0 col)

second question:

ok, now I have to add another col to the left of these 0-9 cols, the cells in it have to tell me what was the position of the number I just put in the "0" col in each row. ie:

9-8-7-6-5-4-3-2-1-0 (my cols)

_________5-6-4-8-3 (just some row)
_________5-4-8-3-4_2(the new 4 appeared in col 2 in the prev row)
_________4-8-3-4-5_4(5, the new number, appeared in col 4 in the prev row)
how do i do that??

[mordor]

anyone knows vba, I understand that the problem can be solved by programming and not by excell functions...
please help, its important...

[duane]

try this

Sub Macro1()
'allows 100 rows of data
Dim digits(100, 10)
'Assume first row is 2 but work starts on row 3
numberrows = Cells(10000, 10).End(xlUp).Row
'start with 2nd row (row 3)
For i = 3 To numberrows
dupcol = 0
'read in the previous row digits
For j = 1 To 9
'check to see if current row "0" column entry exists in
'previous row
If Cells(i, 10) = Cells(i - 1, 11 - j).Value Then dupcol = 11 - j
Next j
'if no duplicate in the row, then move entire row to the left
If dupcol <> 0 Then GoTo duplicate
Range(Cells(i - 1, 2), Cells(i - 1, 10)).Copy Cells(i, 1)
GoTo nexti
duplicate:
'if duplicate in row move everything up until the duplicate
'to the left
Range(Cells(i - 1, dupcol + 1), Cells(i - 1, 10)).Copy Cells(i, dupcol)
Range(Cells(i - 1, 1), Cells(i - 1, dupcol - 1)).Copy Cells(i, 1)
Cells(i, 11) = 10 - dupcol
nexti:
Next i
End Sub

[mordor]

but I don't quite know how to use VBA in excel, I need step by step instruction about how to write this code, and where exactly, as I said before, I aint a big shot in excel...
thanks for helping

[mordor]

if you have 10 places ( 0 to 9) and the digits cant repeat it means you have each degit once...
you don't have to check whether the digit appears, only where it is, because we know that we have every digit in every row

[duane]

I am not sure what you are saying - the macro first checks to see if a duplicate exists before taking any action.