Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 20th 19, 09:03 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 22
Default Rollup/combine data from several below rows into the TopRow based on keys

Hello excel 'top experts'

Another challange on my desk (and something to learn about combine data).
Hopely somebody can help me out.

I've got a sheet with column A till CH
The sheet has several duplicates in rows because in the columns M - BJ and BP - CH there is data showed in one of the columns but in a separated row.

For example

Column M N O P
Row1 a
Row2 b
Row3 c
Row4 d

That should be

Column M N O P
Row1 a b c d

But,.... there are also rows that don't need to combine.
For make the difference in yes/no to combine and till what row I made some codes in column A. In column B is a key registered which reflects a combined created unique value to show which rows belong to each other.

Column A B
Row1 M Key
Row2 F Key
Row3 F Key
Row4 B Key
Row5 U Key
Row6 B Key
Row7 B Key
Row8 B Key
Row9 M Key
Row10 F Key
Row11 F Key
Row12 F Key
Row13 B Key

A macro should be very helpfull

It should combine in the row with code M all the values registered in the the row with M itself and then together with the values in the below rows where F is the code in column A and in column B the key is the same.

So in row 1 you get the registered data from row1 & row2 & row3
in row9 you get the registered data from row9 & row10 & row11 & row12

This should be done only for columns M till BJ, and BP till CH.

On the end I can delete the rows with F in column A.

regards, Johan




  #2   Report Post  
Old July 20th 19, 03:03 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,695
Default Rollup/combine data from several below rows into the TopRow based on keys

Hi Johan,

Am Sat, 20 Jul 2019 01:03:44 -0700 (PDT) schrieb JS SL:

I've got a sheet with column A till CH
The sheet has several duplicates in rows because in the columns M - BJ and BP - CH there is data showed in one of the columns but in a separated row.

For example

Column M N O P
Row1 a
Row2 b
Row3 c
Row4 d

That should be

Column M N O P
Row1 a b c d

But,.... there are also rows that don't need to combine.
For make the difference in yes/no to combine and till what row I made some codes in column A. In column B is a key registered which reflects a combined created unique value to show which rows belong to each other.

Column A B
Row1 M Key
Row2 F Key
Row3 F Key
Row4 B Key
Row5 U Key
Row6 B Key
Row7 B Key
Row8 B Key
Row9 M Key
Row10 F Key
Row11 F Key
Row12 F Key
Row13 B Key


It should combine in the row with code M all the values registered in the the row with M itself and then together with the values in the below rows where F is the code in column A and in column B the key is the same.

So in row 1 you get the registered data from row1 & row2 & row3
in row9 you get the registered data from row9 & row10 & row11 & row12

This should be done only for columns M till BJ, and BP till CH.


try:

Sub Test()
Dim LRow As Long, i As Long, Last As Long
Dim c As Range, myRng1 As Range, myRng2 As Range
Dim FirstAddr As String

With ActiveSheet
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set c = .Range("A:A").Find("M", LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddr = c.Address
Do
For i = c.Row + 1 To LRow
If .Cells(i, "A") = "F" And .Cells(i, "B") = .Cells(i - 1, "B") Then
Else
Last = i - 1
Exit For
End If
Next
Set myRng1 = .Range(.Cells(c.Row, "M"), .Cells(Last, "BJ"))
Set myRng2 = .Range(.Cells(c.Row, "BP"), .Cells(Last, "CH"))
.Cells(c.Row, "M") = Application.TextJoin("", 1, myRng1)
.Cells(c.Row, "BP") = Application.TextJoin("", 1, myRng2)
Set c = .Range("A:A").FindNext(c)
Loop While Not c Is Nothing And c.Address < FirstAddr
End If
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Old July 21st 19, 06:14 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 22
Default Rollup/combine data from several below rows into the TopRow basedon keys

Hello Claus,

Thanks. I think my explanation was in the good direction but.... not good enough. My misstake (sorry).
It combines now the text from the columns into cell M (M till BJ) or BP (BO till CH) instead of it combines for each column separated the text in the same column but then from row with M+key till F+key.

regards, Johan



  #4   Report Post  
Old July 21st 19, 05:09 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,695
Default Rollup/combine data from several below rows into the TopRow based on keys

Hi Johan,

Am Sat, 20 Jul 2019 22:14:34 -0700 (PDT) schrieb JS SL:

Thanks. I think my explanation was in the good direction but.... not good enough. My misstake (sorry).
It combines now the text from the columns into cell M (M till BJ) or BP (BO till CH) instead of it combines for each column separated the text in the same column but then from row with M+key till F+key.


then try:

Sub ConcatF()
Dim LRow As Long
Dim myRng1 As Range, myRng2 As Range, myCol As Range, c As Range
Dim myCnt As Integer, i As Integer
Dim FirstAddr As String

With ActiveSheet
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set c = .Range("A1:A" & LRow).Find("M", LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddr = c.Address
Do
myCnt = Application.CountIfs(.Range("B1:B" & LRow), c.Offset(, 1), .Range("A1:A" & LRow), "F") + 1
Set myRng1 = .Cells(c.Row, "M").Resize(myCnt, 49)
Set myRng2 = .Cells(c.Row, "BO").Resize(myCnt, 19)
For Each myCol In myRng1.Columns
.Cells(c.Row, myCol.Column).Value = Application.TextJoin("", 1, myCol)
Next
For Each myCol In myRng2.Columns
.Cells(c.Row, myCol.Column).Value = Application.TextJoin("", 1, myCol)
Next
Set c = .Range("A1:A" & LRow).FindNext(c)
Loop While Not c Is Nothing And c.Address < FirstAddr
End If
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016
  #5   Report Post  
Old July 21st 19, 07:09 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 22
Default Rollup/combine data from several below rows into the TopRow basedon keys

Claus,

Thanks!
Perfect.

regards, Johan



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Combine Rows Based on Capitalization dogplayingpoker Excel Worksheet Functions 14 March 25th 12 11:15 PM
How to combine data based on two fields.. gambler1650 Excel Discussion (Misc queries) 1 May 4th 10 08:11 PM
Combine data in rows based on two citeria [email protected] Excel Programming 5 June 21st 07 05:36 PM
Macro to combine data based on IF,THENs? marlea[_3_] Excel Programming 3 September 15th 05 10:32 PM
Rollup of Data in Multiple Sheets (based on user input) Gauthier Excel Programming 1 December 10th 03 12:55 PM


All times are GMT +1. The time now is 12:45 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017