Excel move un-duplicated values between two columns to a third

Asked

Viewed 1,433 times

3

I’m having a doubt and I can’t get a plausible answer.
I have a spreadsheet 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 "To" and "B", in these columns there is a sequence of 6-digit numbers, for example:

   A       B

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

I want to compare column "A" using column "B", if the value of column "A" is not found in column "B" that same value should be moved to a new column "C" for example.

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

I am very grateful if someone can help me with a VBA or even Formula.

excel vba

  • something like in C4 put =IF(A4==B4; ; A4)?

  • It’s not clear what you want to do. Do you want to move the value from column A to C only if it exists in B? Should the cell in A be empty or keep the value? What about C cells that do not receive a value? Also, your example is not good, all numbers are different, there is no match. It would be better if you change your example and also present the expected result with the formula or with VBA.

2 answers

1

If I understand your problem, the code below does what you need. Make the necessary adaptations if you use columns other than "Z" or automatically take the number of the last row of the column, for example.

Tip: Put a value in the "A" column that exists in the "B" column 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

1


Does 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 will return the value of A. If the value in A is found in B, the formula will return empty ("").

Browser other questions tagged excel vba

You are not signed in. Login or sign up in order to post.