Excel move non-duplicate values between two columns to a third

Question

Excel move non-duplicate values between two columns to a third

Navigation

#1 by (1 votes)
#2 by (1 votes)

3

I have a question and I can not get a plausible answer. I have a worksheet in which I would like to make a comparison between two columns and say to it that if there is a value that is not duplicated, that same value is moved to a new column. For example:

I have the column " A " and " B " in these columns there is a sequence of 6-digit numbers,

   A       B

519294  519522
519364  519499
519365  519501
519366  519500
519381  519494
519382  519492
519389  519493
519422  519491
519428  519483

I want to make a comparison of column "A" using column "B", if column value "A" is not found in column "B" this same value should be moved to a new column "C" by example.

If possible I would like to use a formula or a VBA that would make this query and if it was a false value where the value was not found in column "B" it could be moved.

I am very grateful if anyone can help me with a VBA or Formula itself.

excel vba

asked by anonymous 04.03.2016 / 02:23

2 answers

1

If I understood your problem, the code below does what you need. Make the necessary adaptations if you use columns other than the "Z" or automatically pick up the last line of the column, for example.

Tip: Put a value in column "A" that exists in column "B" to make sure the code works.

Dim i, j, LinhaInicial, LinhaFinal, ColunaResultado   As Integer

Dim ColunaBase, ColunaComparacao As String

LinhaInicial = 1
LinhaFinal = 9
ColunaBase = "A"
ColunaComparacao = "B"
ColunaResultado = 3 'Corresponde a coluna "C"


For j = LinhaInicial To LinhaFinal

 For i = LinhaInicial To LinhaFinal

  If Range(ColunaBase & j).Value = Range(ColunaComparacao & i).Value Then

   'Encontrou o mesmo valor, deve comparar o próximo valor
   GoTo AchouMesmoValor

  End If

 Next i

 'Se chegou aqui é por que não encontrou o mesmo valor
 'Coloca o valor em uma nova coluna
 'Calcula o código ASCII para a coluna (67 = "C")
 Range(Chr(64 + ColunaResultado) &     LinhaInicial).Value = Range(ColunaBase & j).Value

 'Prepara para próxima coluna a copiar valor encontrado
 ColunaResultado = ColunaResultado + 1

AchouMesmoValor:

Next j

17.03.2016 / 13:27

Format hours and minutes presentation of variable read from SQL Difference in Socket.io statement

score 1 · Accepted Answer

Will a PROCV combined with SE and SEERRO not solve you?

=SE(SEERRO(PROCV(A1;B:B;1;FALSO);0)=0;A1;"")

Explanation: If PROCV returns an error, indicating that the value in A was not found in B, the formula returns the value of A. If the value in A is found in B, the formula returns empty ("") .