How to compare two columns of a worksheet, and keep information that is the same?

Question

How to compare two columns of a worksheet, and keep information that is the same?

Navigation

#1 by (2 votes)

2

Hello

I'm working with a spreadsheet in Excel that has this structure:

Coluna_A    Coluna_B
A           A
B           B
C           C
C_1         E
D
E
F

What I want is to figure out a way to put together a third column, which has data present in both columns. So:

Coluna_C
A
B
C
E

Note that the sample unit "E" of "Column_B" is in the same line as "C_1", of "Column_A", but it should still be in "Column_C" because it is in both columns. Would anyone know any code to automate this analysis, either in Excel, or in R?

r excel

asked by anonymous 03.06.2018 / 01:46

1 answer

count the same database text in php Using struct in C #

score 2 · Accepted Answer

Maybe the next one is what you want. Note that elements common to both columns are the first elements of the Coluna_C vector, regardless of their position in the original vectors, Coluna_A or Coluna_B .

dados$Coluna_C <- NA
comuns <- intersect(dados$Coluna_A, dados$Coluna_B)
dados$Coluna_C[seq_along(comuns)] <- comuns
dados
#  Coluna_A Coluna_B Coluna_C
#1        A        A        A
#2        B        B        B
#3        C        C        C
#4      C_1        E        E
#5        D              <NA>
#6        E              <NA>
#7        F              <NA>

If you do not want NA values, start with

dados$Coluna_C <- ""

Data.

dados <- read.table(text = "
Coluna_A    Coluna_B
A           A
B           B
C           C
C_1         E
D
E
F
", header = TRUE, fill = TRUE, stringsAsFactors = FALSE)