Merging multiple rows based on first column
Tag : vba , By : xie renhui
Date : March 29 2020, 07:55 AM
Hope this helps As you have commented, using a variant array rather than looping the cells directly will speed this up enormously To apply that here you could: Sub Demo()
Dim ws As Worksheet
Dim rng As Range
Dim datSrc As Variant
Dim datDst As Variant
Dim i As Long
Dim j As Long
Dim rwOut As Long
Dim str As String
Set ws = ActiveSheet
With ws
Set rng = Range(.Cells(1, 2), .Cells(.Rows.Count, 3).End(xlUp))
datSrc = rng.Value
ReDim datDst(1 To UBound(datSrc, 1), 1 To UBound(datSrc, 2))
rwOut = 1
For i = 1 To UBound(datSrc, 1)
str = datSrc(i, 1)
If datSrc(i, 2) <> vbNullString Then
For j = i + 1 To UBound(datSrc, 1)
If datSrc(i, 2) = datSrc(j, 2) Then
str = str & "," & datSrc(j, 1)
datSrc(j, 2) = vbNullString
End If
Next
datDst(rwOut, 1) = str
datDst(rwOut, 2) = datSrc(i, 2)
rwOut = rwOut + 1
End If
Next
rng = datDst
End With
End Sub
|
Merging multiple rows and column data into single concatenated row
Date : March 29 2020, 07:55 AM
it helps some times Here's how my table looks , You need a subquery to remove the duplicates, something like; select id, listagg(name, ',') within group (order by name) as names
from (
select id, name1 as name from your_table
union
select id, name2 as name from your_table
union
select id, name3 as name from your_table
)
group by id
with your_table(id, name1, name2, name3) as (
select 1, 'a', 'b', 'c' from dual
union all select 1, 'c', 'd', 'a' from dual
union all select 2, 'd', 'e', 'a' from dual
union all select 2, 'c', 'd', 'b' from dual
)
select id, listagg(name, ',') within group (order by name) as names
from (
select id, name1 as name from your_table
union
select id, name2 as name from your_table
union
select id, name3 as name from your_table
)
group by id;
ID NAMES
-- --------------------
1 a,b,c,d
2 a,b,c,d,e
|
Merging multiple rows by disticnt row value into a single column
Date : March 29 2020, 07:55 AM
Hope that helps Using R trying to merge raw matrix to result a matrix based on the value of the row value. , We can use split split(df1[,2], df1[,1])
#$a1
#[1] 10 20 40
#$a2
#[1] 45 50
#$a3
#[1] 40
#$a4
#[1] 45 60
|
merging one column from multiple rows based on another column
Tag : awk , By : alchemist
Date : March 29 2020, 07:55 AM
This might help you Trying to merge multiple rows into 1 based on first column. , Awk solution: awk -F'\t' '{ a[$1]=($1 in a? a[$1]",":"")$2 }END{ for(i in a) print i,a[i] }' OFS='\t' file
U604 LRRC69
U586 PLEKHA5,C8orf77,ZNF252,LINGO2
U531 5S_rRNA,C3orf33
S784 CHST5,TMEM231,TM4SF20,TMEM184A
|
Merging multiple rows per group into one while merging only some column data in R
Date : March 29 2020, 07:55 AM
it helps some times I believe you can just use a summary statement like post_datapoint_xyz = first(post_datapoint_xyz) for each of the post-columns to address your problem. What also works would be to simply include all the post columns into your group_by list :) all_reddits <- all_posts_and_comments %>%
group_by_at(vars(starts_with('post_'))) %>%
summarise(...)
all_reddits <- all_posts_and_comments %>%
nest(starts_with('comment_'), .key = 'comments')
all_posts_and_comments.2 <- all_reddits %>%
unnest(comments)
|
Boards Message :
Related QUESTIONS :