Excel 2007 several columns with different values, find where which number is

I Have 6 columns where I have up to 320 values. I say up to because the number of values differ in each column and the values开发者_StackOverflow中文版 also differ in the columns.

Like this:

Column A has the following values, one in each cell: 1,2,3,4,6,8,9

Column B has the following values, one in each cell: 1,3,4,6,7,8,10

etc.

I would like to know what numbers that differ between the columns, so I would like to know that 2 is missing in the B column and that 9 is missing from the A column.

Optimal would be if it were possible to have one line for each number and when there is missing I just get a blank cell on that line in that specific column. Keep in mind that there are 6 columns.

Is this possible? Is there a workaround? I would prefer to do this in Excel but I can use other solutions as well as long as it solves my problem.

If I understand your question correctly the following might help. (I'm assuming no header row here)

In column G put the numbers 1 to 320, e.g. 1,2,3,4,5,6,...320 - so this takes up 320 rows.

In cell H1 put the following:

=VLOOKUP($G1,A:A,1,0)

Drag the above formulae across 6 columns - so H1 to M1 and then select the 6 cells (H1:M1) and drag them down to row 320.

In the missing numbers cells you will get a "#N/A" display so you could replace the above vlookup and use an if condition if you want. Something like:

=IF(ISERROR(VLOOKUP($G1,A:A,1,0)),"",VLOOKUP($G1,A:A,1,0))

Hope this helps.