Conditionally concatenate string over multiple rows

if then concatenate excel
conditional concatenate in excel
lookup and return multiple values concatenated into one cell
textjoin
concatenateif
how to conditionally concatenate a range
concatenate if cell contains text
textjoin if

I have extracted multiple tables from a PDF which contains strings over multiple lines. I have used the extract_table() function from the tabulizer package, the only problem being that the strings import as separate rows.

e.g.

action <- c(1, NA, NA, 2, NA, 3, NA, NA, NA, 4, NA)

description <- c("a", "b", "c", "a", "b", "a", "b", "c", "d", "a", "b")

data.frame(action, description)

       action description
1       1           a
2      NA           b
3      NA           c
4       2           a
5      NA           b
6       3           a
7      NA           b
8      NA           c
9      NA           d
10      4           a
11     NA           b

I would like to concatenate the strings so that they appear as the same element such as:

  action description
1      1       a b c
2      2         a b
3      3     a b c d
4      4         a b

Hope that makes sense, appreciate any help!

tidyverse way would be to fill the action column with previous non-NA value then group_by Action and paste the description together.

library(tidyverse)

df %>%
 fill(action) %>%
 group_by(action) %>%
 summarise(description = paste(description, collapse = " "))


#  action description
#   <dbl> <chr>      
#1     1. a b c      
#2     2. a b        
#3     3. a b c d    
#4     4. a b        

How To Conditionally Concatenate A Range, For Mac, use ⌘ + Shift + Return. Range – This is range of values which we want to concatenate together. Delimiter – This is the delimiter value  If you have Kutools for Excel, with its Advanced Combine Rows utility, you can quickly and conveniently concatenate the text base on criteria. Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days .

A base R option

dat <- data.frame(action, description)
aggregate(
  description ~ action,
  transform(dat, action = cumsum(!is.na(dat$action))),
  FUN = paste,
  ... = collapse = " "
)
#  action description
#1      1       a b c
#2      2         a b
#3      3     a b c d
#4      4         a b

For aggregate to work we need to change action to what is returned by cumsum(!is.na(dat$action))), i.e.

cumsum(!is.na(dat$action)))
#[1] 1 1 1 2 2 3 3 3 3 4 4

How to easily concatenate text based on criteria in Excel?, To combine text with the unique ID numbers, you can extract the unique values As Variant , ConcatenateRange As Range, Optional Separator As String = "," ) As Variant How to combine multiple rows into one and sum duplicates in Excel? Hold Ctrl + Shift then press Enter while in Edit Mode to create an array formula. For Mac, use ⌘ + Shift + Return. Range – This is range of values which we want to concatenate together. Delimiter – This is the delimiter value which we want to use to separate values by in our concatenation.

Here is one option with data.table

library(data.table)
setDT(df1)[, .(description = paste(description, collapse = ' ')), 
                  .(action = cumsum(!is.na(action)))]
#   action description
#1:      1       a b c
#2:      2         a b
#3:      3     a b c d
#4:      4         a b

Or using na.locf from zoo

library(zoo)
setDT(df1)[, .(description = paste(description, collapse = ' ')),
              .(action = na.locf(action))]
data
df1 <- data.frame(action, description)

Concatenate cell values based on a condition [No VBA], Charts · Conditional formatting · Pivot table · VBA Unique strings [UDF] Concatenate cell values based on a condition [No VBA] here: Lookup and return multiple values concatenated into one cell It is what The final thing to do is to use the concatenate function to add all values into one text string. I have a table like below. I would like to concatenate multiple rows which have the same ID. Please advise what formula could get the results as below? ID TEXT RESULTS APPLE A A, B, F APPLE B A, B, F

You could use the zoo and dplyr packages like so

library(zoo)
library(dplyr)
action <- c(1, NA, NA, 2, NA, 3, NA, NA, NA, 4, NA)
description <- c("a", "b", "c", "a", "b", "a", "b", "c", "d", "a", "b")
df = data.frame(action, description)
df$action = na.locf(df$action)
df = 
    df %>% 
    group_by(action) %>% 
    summarise(description = paste(description, collapse = ' '))

How To Conditionally Concatenate A Range, This video will show you how to use an array formula to conditionally How To Conditionally Duration: 3:10 Posted: Jan 25, 2018 The final thing to do is to use the concatenate function to add all values into one text string. Double click on cell F4; Delete the curly brackets from the formula: {} Add this: =CONCATENATE(and then an ending parentheses ) Press Enter; Add delimiting character

CONCATENATE function (DAX), The CONCATENATE function joins two text strings into one text string. If you need to concatenate multiple columns, you can create a series to conditionally concatenate three string values and add spaces as separators. Add =CONCATENATE ( to the beginning of the text and end it with a round bracket ). Press Enter. Doing this would combine the range of cells into one cell (as shown in the image above). Note that since we use any delimiter (such as comma or space), all the words are joined without any separator.

CONCATENATE Excel Range (with and without separator), In Excel, there are two ways to combine the contents of multiple cells: In its basic form, CONCATENATE function can join 2 or more characters of strings. For​  Concatenate Multiple Rows Using FOR XML PATH The simplest and straight forward way to concatenate rows into a string value is to use FOR XML PATH in a select query. This FOR XML PATH method can be used in SQL Server version 2005 and higher.

Excel CONCATENATE function and "&" to combine strings, cells , As a result, you have one larger cell that is displayed across multiple rows and/or columns in your worksheet. When you concatenate cells in  Excel has some great built in functions for summing and counting conditionally based on given criteria, but to concatenate a range conditionally we will need to create our own user defined function. This ConcatenateIf function will concatenate a range of values based on a given criteria and separate them with a text delimiter of your choice.